How to import data from .CSV file in to Navision using a Report

Business Central/NAV Developers Forum
1

Hi All,

I’m using Navision 3.70 and I have a requirement to import data from a .csv file in to General Journal. The.CSV file includes the fields Posting Date, Doc. No., G/L Acc type, Acc No. and Amount.

I created a table to define the format of the .CSV file because the order of fields in .CSV file are different from one file to other.

By using a Report,now I need to import data from .csv file in to General Journal.

Can any of you please help me, how can I create a Report in order to import the data.

Thanks in advance.

With Regards,
Dilla

2

why use report? why don’t try to make it on dataport? in dataport, you can design the field list like:

Journal Template, Journal Batch, Posting Date, Doc. Type, Doc. No., Account Type, Account No., Description, Amount (,Dimensions, Gen. Bus. Posting Group … )

you can call validate() on each field as necessary.

3

Hi Dilla,

You don’t need to create a table for this, I suggest make consistency in .CSV files.

Instead of using Report, better you use Dataport. Create a dataport and use table 81 (General Journal Line) as its dataitem and define your fields (here, Posting Date, Doc. No., G/L Acc type, Acc No. and Amount.) in dataport fields.

You need to do some coding also, need to take care of Primary Key (Journal Template Name,Journal Batch Name,Line No.) and Line No. etc…

4

Hi Joe and Dhan Raj,

Thank you both of you for your quick reply.

Actually right now I’m using Navision 3.70 but I’m going to upgrade to NAV 2009 where I can not use Dataports in future and I can not use XML ports also because .CSV file I’m receiving from a third party vendor and they can not chage the format.

Kindly revert for any further information.

5

you can use Dataports even i NAV 2009

6

Hi,

NAV2009 still supports dataports, so this should not be an issue as of now.

7

Although I don’t believe the RoleTailored client supports dataport objects.

For testing, it’s a good thing to remember that if the dataport is run directly from the Dataport Designer and the dataport is used for import process, no records are actually saved in the database table.

8

Hi All,

Thanks to all of you for your valuable inputs.

As per the requirement I should use only Report, I created a new Report and by using Excel Buffer Table some how I mangaged to import the data from .CSV file in to General Journal. .CSV file contains data for 21 fields for each record in a single shell seperated by “;”.

Two problems I have now are:

  1. If I have 2 records in .CSV file, first record is getting imported first and then it is over written by the second record.

  2. If .CSV file does not contain data for all 21 fileds, emty fields are seperated by a “;” and I’m receiving an error message. How can I handle the empty fields.

Kindly revert for further information.

9

  1. The keys for Gen. Journal Line are: Journal Template Name,Journal Batch Name,Line No. you need to make sure each line (record) in your csv file maintains incremental or unique line number.

  2. it depends on the codes you write in the report section, but to represent empty field you can put double apostrophe (’’) between semi-colon sign. example:
    field1;field2;’’;’’;’’ …

10

You can use a LineNo integer variable assigning it to the Line No. field of each record. Everytime you insert a record into the Gen. Journal Line table, increment this variable by 10000. As Joe said, all three primary key fields should be filled in and incrementing the Line No. field by 10000 is the standard practice.

GenJnlLine.“Line No.” := LineNo;

LineNo += 10000;

As for the empty field error. What code are you using to check each line read from the file? Maybe another set of eyes on it will help.