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.
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…
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.
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.
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:
If I have 2 records in .CSV file, first record is getting imported first and then it is over written by the second record.
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.
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.
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;’’;’’;’’ …
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.