Skip to main content
Business Central forum
Suggested answer

convert csv/txt file into excel

editSubscribe (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

Attachments
  • Suggested answer
    JAngle Profile Picture
    JAngle 33,131 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

Helpful resources

Quick Links

New Blog Features Released!

Check out the new community blog features for viewers and authors…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,080 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,601 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Featured topics

Product updates

Dynamics 365 release plans