Skip to main content

Notifications

Announcements

No record found.

Small and medium business | Business Central, N...
Suggested answer

convert csv/txt file into excel

(0) ShareShare
ReportReport
Posted on by 127

i have one csv/txt file i want that file to convert into excel using csv buffer and excel buffer.Firstly i want that file to import into csv buffer after that i want that file to be converted into excel using excel buffer i am able to import file into csv buffer but not able to export it into excel. Export should be according to condition for multiple sheet and also want some hard code for first three cell. The sheet examples and code is given below that i have used for import.I tried export using report but not able to do that if you can help ,e it will be greatfull.thanks in advance.

page 51111 "import data"
{
    ApplicationArea = All;
    Caption = 'Import data';
    PageType = Card;
    SourceTable = "CSV Buffer";
    SourceTableTemporary = true;
    UsageCategory = Documents;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field("Line No."; Rec."Line No.")
                {
                    ApplicationArea = All;
                }
                field("Field No."; Rec."Field No.")
                {
                    ApplicationArea = All;
                }
                field(Value; Rec.Value)
                {
                    ApplicationArea = All;
                }
            }
        }
    }
    actions
    {
        area(Processing)
        {
            action(Import)
            {
                Caption = 'Import';
                Image = Import;
                ToolTip = 'Import';
                ApplicationArea = all;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                PromotedOnly = true;
                trigger OnAction()
                var
                
                begin
                    Rec.Init();
                    SelectFileFromFileShare(Rec);
                    Message('check import %1 ', Rec.GetValue(3, 5));
                 end;
            }
           
           
            action(Excel)
            {

                Image = Excel;
                ToolTip = 'excel file';
                ApplicationArea = all;

                trigger OnAction()
                var
                    excel: Report "Export sheet";
                begin
                    excel.Run();



                end;
            }
        }
    }
    procedure SelectFileFromFileShare(Var TempCSVBUffer: record "CSV Buffer" temporary)
    var
        FileMgt: Codeunit "File Management";
        WindowTitle: text;
        FileName: text;
        InS: InStream;

    begin

        WindowTitle := 'Select file salba';
        Filename := FileMgt.OpenFileDialog(WindowTitle, '', '');
        Rec.Init();
        Rec.LoadData(FileName, ',');
    end;

   

    trigger OnClosePage()
    begin
        Rec.Reset();
        Rec.DeleteAll();
    end;

    


}

report 51103 "Export sheet"
{
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    ProcessingOnly = true;
    Caption = 'Export  To Excel';

    dataset
    {
        dataitem("CSV Buffer"; "CSV Buffer")
        {
            DataItemTableView = SORTING("Field No.") ORDER(Ascending);
            trigger OnPreDataItem()
            begin
                ;
                CreateExcelHeader();
            end;

            trigger OnAfterGetRecord()
            begin
                CreateExcelBody();
            end;
        }
    }

    trigger OnInitReport()
    begin
        TempExcelBuffer.DeleteAll();
    end;

    trigger OnPostReport()
    begin
        CreateExcelBook();
    end;

    local procedure CreateExcelHeader()
    begin
        TempExcelBuffer.AddColumn("CSV Buffer".FieldCaption("Line No."), False, '', True, false, false, '', TempExcelBuffer."Cell Type"::Number);
        TempExcelBuffer.AddColumn("CSV Buffer".FieldCaption("Field No."), False, '', True, false, false, '', TempExcelBuffer."Cell Type"::Number);
        TempExcelBuffer.AddColumn("CSV Buffer".FieldCaption(Value), False, '', True, false, false, '', TempExcelBuffer."Cell Type"::Text);
        // TempExcelBuffer.AddColumn(csvbuffer.FieldCaption(Inventory), False, '', True, false, false, '', TempExcelBuffer."Cell Type"::Text);
    end;

    local procedure CreateExcelBody()
    begin
        TempExcelBuffer.NewRow();
        TempExcelBuffer.AddColumn("CSV Buffer"."Line No.", False, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
        TempExcelBuffer.AddColumn("CSV Buffer"."Field No.", False, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
        TempExcelBuffer.AddColumn("CSV Buffer".Value, False, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
        // TempExcelBuffer.AddColumn(csvbuffer.GetValue(1, 5), False, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
        // TempExcelBuffer.EnterCell();
    end;

    local procedure CreateExcelBook()
    begin
        TempExcelBuffer.CreateNewBook('List');
        TempExcelBuffer.WriteSheet('List', CompanyName, UserId);
        TempExcelBuffer.CloseBook();
        TempExcelBuffer.SetFriendlyFilename('Export');
        TempExcelBuffer.OpenExcel();
    end;

    var
        TempExcelBuffer: Record "Excel Buffer" temporary;
}

export.png

and output excel file will be like that below:

import.png

there are 13 column and 763 rows in txt file.

thanks in advance

  • Suggested answer
    JAngle Profile Picture
    JAngle 48 on at
    RE: convert csv/txt file into excel

    Why not move the data after import to the csv buffer to a regular table. Then use a configuration package to export - because your image is of the format that produces. Which is done with xml actually. If you want the new table to be blank most of the time have a processing only report clear it down and run it from the job queue

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

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,234 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,994 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans