Skip to main content

Import CSV file using CSV Buffer in Business Central

Hi Everyone, 

In this article we will see how we can Import Data (CSV/Excel format) in Microsoft Dynamics 365 Business Central. Here i am using CSV Buffer as a record variable to read data from csv file for temporary basis and then will store it to Master table.

Let us take a scenario to import CSV file to Employee Table.

Step-(1) First analyze the format/ structure of the csv data file as shown below:

Step-(2) Secondly based on the data format and columns, create a new Procedure/function on Codeunit to choose and import file using the dialog box to read the data.

codeunit 50001 "CSV Import"
{
    trigger OnRun()
    begin
    end;

    procedure ImportEmployeeDetailViaCSVBuffer()
    var
        csv_InStream: InStream;
        uploadResult: Boolean;
        csvFileName: Text;
        csvBuffer: Record "CSV Buffer";
        RecEmployee: Record Employee;
    begin
        if UploadIntoStream('Import Employee CSV', '', '', csvFileName, csv_InStream) then begin
            csvBuffer.DeleteAll();
            csvBuffer.LoadDataFromStream(csv_InStream, ',');
            if csvBuffer.FindSet() then begin
                repeat
                    if csvBuffer."Line No." > 1 then begin // To Ignore 1st Line (Header Part)
                        if (csvBuffer."Field No." = 1) then
                            RecEmployee.Init();
                        case csvBuffer."Field No." of
                            1:
                                begin
                                    RecEmployee.Validate("No.", csvBuffer.Value);
                                end;
                            2:
                                begin
                                    RecEmployee."First Name" := csvBuffer.Value;
                                end;
                            3:
                                begin
                                    RecEmployee."Middle Name" := csvBuffer.Value;
                                end;
                            4:
                                begin
                                    RecEmployee."Last Name" := csvBuffer.Value;
                                end;
                            6:
                                begin
                                    RecEmployee."Job Title" := csvBuffer.Value;
                                end;
                            7:
                                begin
                                    RecEmployee."Phone No." := csvBuffer.Value;
                                end;
                            8:
                                begin
                                    RecEmployee."Search Name" := csvBuffer.Value;
                                end;
                            9:
                                begin
                                    RecEmployee.Address := csvBuffer.Value;
                                    RecEmployee.Insert();
                                end;
                        end;
                    end;
                until csvBuffer.Next() = 0;
                Message('Imported Successfully.');
            end;
        end;
    end;
}

Step-(3) Now create new Employee List Page Extension, and add new action button and call the required procedure:

pageextension 50001 "Employee List Ext" extends "Employee List"
{
    actions
    {
        // Add changes to page actions here
        addlast(Processing)
        {
            action("Import Employee Via CSV")
            {
                ApplicationArea = All;
                PromotedCategory = Process;
                Promoted = true;
                PromotedIsBig = true;
                Image = ImportExcel;

                trigger OnAction()
                Var
                    CSVImport_CU: Codeunit "CSV Import";
                begin
                    CSVImport_CU.ImportEmployeeDetailViaCSVBuffer();
                end;
            }
        }
    }
}

Output- Open Employee List Page and click on action button "Import Employee Via CSV"  and Choose the file and click on OK button:

Thanks for Reading!!
Happy to Help :)

Labels:

Comments

  1. Anonymous25 September 2023 at 20:12

    I don't know why, but I can not skip the header

    ReplyDelete
  2. Kunal Huria9 October 2023 at 20:51

    You need to add condition to skip Header Line, see below Code : if csvBuffer."Line No." > 1 then begin // To Ignore 1st Line (Header Part)

    ReplyDelete

Post a Comment

Popular posts from this blog

Amount In Words in Navision / Business Central

Hi Guys, In this article, we will see how to display amount in words in Microsoft Dynamics Business Central. Here, I am taking an example of Report object  which is widely used in Navision for converting decimal amount field into words. Procedure :-  Step-1 Declare the following Global Variables :         RepCheck:  Report  "Check";         NoText:  array [ 2 ]  of   Text ;         AmountInWords:  Text ; where, (1) RepCheck is a variable of  Report DataType Named as " Check " which is 1401. (2) NoText is a variable of DataType " Text " with array Dimension value as 2 which is defined as shown above. (3) AmountInWords is a variable of DataType " Text " which will store final result of amount in words. Step-2 Now, add the following code in OnAfterGetRecord() trigger of DataItem containing that decimal field value. So in my custom Purchase Order Report, i am adding code in the Purchase Header (DataItem) - OnAfterGetRecord() trigger.   trigger  OnA
7 comments
Read more

Flow Custom field to G/L Entry and other Posted tables : From Purchase Order to Posted Purchase invoice

Hi Readers, In this blog we will see how we can transfer data from " Purchase Header" to "Purchase Invoice Header" & "G/L Entry" table in Business Central. In this example, will create "Remarks" field on Purchase Header table and after posting, flow it to Purchase Invoice Header & G/L Entry tables: Firsly, create required fields ("Remarks") in below mentioned tables by creating table extensions: Purchase Header (38) Purch. Inv. Header (122) Gen. Journal Line (81)  G/L Entry (17) For the Purch. Inv. Header table, you can just create copy the field from purchase header and paste it in  Purch. Inv. Header  table. If the field ID No. is same for that field for both the tables then you don't have to code i.e, it automatically transfer that field data from Purch. Header to Purch. Inv. Header . Step-(1)   tableextension   50104  "Purchase Header Ext"  extends  "Purchase Header" {      fields     {         
1 comment
Read more

Calculate Date : Using Date Formula and CalcDate Function in Navision/BC

 Hi Everyone, In this article we will see how we can Calculate Date by adding and subtracting No. of Days / Months based on CalcDate and DateFormula datatype. Here, I define three variables required for: (1) " No of Days " -  To define No. of Days / Months as required. You can also use these units denoted as: <Unit> = D | WD | W | M | Q | Y (D=day, WD=weekday, W=week, M=month, Q=quarter, Y=year). (2) " From Date " and " To Date "  Date type Variables to store calculated date in it. page   50103  "Date from Date Formula" {     PageType = Card;     ApplicationArea = All;     UsageCategory = Administration;     SourceTable =  Integer ;      layout     {          area( Content )         {              group( General )             {                  field( "No. of Days"; "No. of Days" )                 {                     ApplicationArea = All;                 }             }         }     }      actions     {          area
Post a Comment
Read more