web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Generate .csv file and...
Small and medium business | Business Central, N...
Answered

Generate .csv file and upload to SFTP

(0) ShareShare
ReportReport
Posted on by Danyal 259

Hi,

I want to export data in .csv and upload that file to a SFTP location. I am able to generate .csv file using xmlPort. I am also able to upload the file to SFTP using Azure functions.

The problem here is that I have to manually select the file by using UploadIntoStream('Select a file to upload', '', 'All files (*.*)|*.*', fileName, InStr) because xmlPort downloads the file in downloads folder. Is there a way to do this on a single click or with a job where xmlPort directly downloads the file to SFTP or Azure Blob instead of downloading in Downloads folder.

I am using Business central online v16.5.

Here is my xmlPort code

xmlport 50128 exportItems
{
    Direction = Export;
    Format = VariableText;
    FormatEvaluate = Legacy;
    FileName = 'MyFile.csv';
    UseRequestPage = false;

    schema
    {
        textelement(Root)
        {
            tableelement(Customer; Customer)
            {
                XmlName = 'Customer';
                fieldelement(T1; Customer.Name)
                {

                }
                fieldelement(T2; Customer."No.")
                {

                }
                fieldelement(T3; Customer."Country/Region Code")
                {

                }
                fieldelement(T4; Customer."City")
                {

                }
                fieldelement(T5; Customer."Post Code")
                {

                }
                fieldelement(T6; Customer."Address")
                {

                }
                fieldelement(T7; Customer."Address 2")
                {

                }         
            }
        }
    }
}
here is the codeunit which asks user to upload a file and then uploads that to Azure blob and then to SFTP
var
        fileMgt: Codeunit "File Management";
        httpClient: HttpClient;
        httpContent: HttpContent;
        jsonBody: text;
        httpResponse: HttpResponseMessage;
        httpHeader: HttpHeaders;
        fileName: Text;
        fileExt: Text;
        InStr: InStream;
        OutStr: OutStream;
        base64Convert: Codeunit "Base64 Convert";
    begin

        Xmlport.Run(50128, false, false);
        UploadIntoStream('Select a file to upload', '', 'All files (*.*)|*.*', fileName, InStr);
        fileExt := fileMgt.GetExtension(fileName);

        jsonBody := ' {"base64":"' + base64Convert.ToBase64(InStr) +
        '","fileName":"' + fileName + '.' + fileExt +
        '","fileType":"' + fileMgt.GetFileNameMimeType(fileName) + '", "fileExt":"' + fileMgt.GetExtension(fileName) +
            '"}';

        httpContent.WriteFrom(jsonBody);
        httpContent.GetHeaders(httpHeader);
        httpHeader.Remove('Content-Type');
        httpHeader.Add('Content-Type', 'application/json');
        httpClient.Post('MyAzureFunctionUrl', httpContent, httpResponse);
        //Here we should read the response to retrieve the URI
        message('File uploaded.');
    end;
Categories:
Dynamics 365 Business Central
I have the same question (0)
  • Suggested answer
    JAngle Profile Picture
    JAngle 119 on at
    RE: Generate .csv file and upload to SFTP

    I would bypass AL completely and use Power Automate or Logic Apps instead. This post shows one way of converting JSON to CSV. This could be done by having a query or page producing the data you need and call a web service. After that the relevant connectors are they to produce an excel file and then send it to a SFTP: powerusers.microsoft.com/.../443890

  • beenmeckel Profile Picture
    beenmeckel 77 on at
    RE: Generate .csv file and upload to SFTP

    I have a script doing this at work. I am using PowerShell to pull source and destination locations from a csv some user update and have it output to a .bat file that calls WinSCP to do the moving.

    Took a lot of trial and error to get it to generate the output correctly with the right set of quotes around the right sections. And it is ugly as can be.

    Kodi nox

  • Verified answer
    divyeshchitte Profile Picture
    divyeshchitte 950 on at
    RE: Generate .csv file and upload to SFTP

    Kindly Convert and store CSV in Temp Blob and then pass to Instream.

  • Peace_112 Profile Picture
    Peace_112 5 on at
    RE: Generate .csv file and upload to SFTP

    Hi Divyesh, could you please provide the source code along with dll if any....It would be great help for me. Thank you.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
Rishabh Kanaskar Profile Picture

Rishabh Kanaskar 4,285

#2
Sumit Singh Profile Picture

Sumit Singh 2,725

#3
Nimsara Jayathilaka. Profile Picture

Nimsara Jayathilaka. 2,526

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans