How to skip CSV Header Line?

Hi Expert,

I want to skip description/header/title when i import the data.

  1. I close Auto Save - No.

Item-Test OnBeforeImportRecord()

IF CurrFile.POS = 0 THEN
CurrDataport.SKIP;


I get this error.

Item Category Code is too long. The text in code fields can have a maximum length of 10 char:

I skip 1st line but it show that error. Why? How to skip header/title/caption?

'My Data

Item No,Alternative Item No,Description,descr,Description 2 (Text 30),Item Category Code,Product Group Code,Base Unit of Measure,Purch Unit of Measure,Sales Unit of Measure,Gen Prod Posting Group1,GST Prod Posting Group,Inventory Posting Group,Shelf No,Item Disc Group,Unit Price,Price/Profit Calculation,Profit %,Costing Method1,Unit Cost,Standard Cost,Lead Time Calculation,Reorder Point,Maximum Inventory,Reorder Quantity,Reserve,Scrap %,Minimum Order Quantity,Maximum Order Quantity,Safety Stock Quantity,Safety Lead Time,Replenishment System,Reordering Policy,Reorder Cycle,Include Inventory,Rounding Precision,Item Tracking Code,Serial/Lot Nos
CL-7190-12 R.02,SR20 FXT (7.093.66) - 12521,"002PP-2""EBPH",UNIT,UNIT,UNIT,FG,GST7,FG,FG,Profit=Price-Cost,Average,Never,Produce,Fixed Reorder Qty.,

Regards

The best trick I’ve seen to skip the header row is to add a dataitem based on integer, filtered where number = 1, and importing everything into dummy variables. Set up the dataitem separator with just one line feed, and you should be set.

Due to the discussion in here I tried to prove that it works, but this only works when exporting information. For importing, you’ll need to use Harry’s suggestion below

The problem with this solution is that you will only be able to import ONE line in the real dataitem as the system expects no more data after the single line feed of the first data line (second line in the csv file).

Rather consider one of the following options:

Declare a set of variables you import your csv-columns. Define these as dataport fields for an “Integer” dataitem but do not define an additional dataitem.
Then check that you set the AutoSave property to false in the dataitem properties. Define an integer variable as a line counter which you increment in the “OnAfterImportRecord” trigger. Then test in the same trigger if the counter is bigger than 1 and if so, assign (evaluate for non-text fields) the content of the variables to a record variable and insert/modify then from within the code.

Second Option would be that you open the file upfront and define a new file you use for the import itself. Define Instream for reading from CSV file and Outstream for the copy of the CSV file without the header line. Read through the Instream until you find the linefeed character. Then call the COPYSTREAM method to copy the remaining bytes to the new file.Close the file and assign the filename of this new file to the CurrDataport.FILENAME property instead of the original filename.

Maybe easier just to delete the first line of the file.

Could be done rather easy by C/SIDE also!

Isn’t that what I’m suggesting in the second option? Maybe I did not explain detailed enough?

A great post by Kriki - and we use this - works like a charm

Put some code in trigger “OnPreDataItem()”:
(variable “cha” is CHAR)

IF CurrDataport.IMPORT THEN BEGIN
REPEAT
CurrFile.READ(cha);
UNTIL cha = 10; // of course in case the RecordSeparator is "<<NewLine>>"
END;

And for more than 1 line:

IF CurrDataport.IMPORT THEN BEGIN
LintLinesToRead := 2; // lines that must be read (this is an integer)
REPEAT
CurrFile.READ(cha);
IF cha = 10 THEN
LintLinesToRead -= 1;
UNTIL LintLinesToRead = 0;
END;

The whole purpose of setting the filter on number=1 is to read one header line, and after that it moves on to the ‘real’ dataitem to import the data. For someone who doesn’t know how to program, this is a perfectly fine solution. There is no ‘problem’ at all, it is just one way to do this, one that works like a charm.

What happens if you set the record seperator to instead of ?
The system skips to the next dataitem after the line break has been detected.
This behavior is fine for the “header to first line” - line break, but can cause a problem for the line break between second and third line.

Navision should (per definition) just ignore the lines after line 2 as there is no new data item to take the data.

If this still works, it is a bug (undocumented feature) which should not be used.

I don’t know what you’re talking about Thomas, I’ve used this many times years ago, and it always worked like a charm. Just because you like another solution better doesn’t mean others are not good solutions either. Not everyone knows how to write code, and this is an alternative that works without having to write any code.

I don’t have a problem with your solution - the only thing is - the dataport is just not supposed to work this way. If it works, it is great.

This was bugging the crap out of me so I was going to create a little dataport and post the object here, but I can’t get it to work this way. I was 100% positively sure that I used this trick before, years and years ago. I don’t know if this used to work and they “fixed” it, but I can’t get it to work anymore, the single line feed is causing the dataitem to break after the first line.

My recommendation is to use Harry’s suggestion, and copy the little bit of code in the OnPreDataItem trigger.

You have no idea how much I hate dataports, they never work how you think they work… field start and end delimiters, separators, line feeds, it never works properly…

I know what you are talking about. The dataports do not even work in the NAS - there is absolutely no reason ffor the dataport to not work in NAS - but that is how it is.

Hi Daniel,

Harry suggested two pieces of code.

  1. For one line which is header line.

  2. for multiple lines which follow another dataitem underneath integer.

These codes should be written in which dataitem section? 1st or 2nd?

Or to skip the REPEAT usage, and also have the function that writes the header:


Item - OnPreDataItem()

IF NOT CurrDataport.IMPORT THEN BEGIN
// Write Header
CurrFile.TEXTMODE(TRUE);
CurrFile.WRITE('Item No (20),Description (30),Label Description 1 (250),Label Description 2 (250),Additional Info (35)');
CurrFile.TEXTMODE(FALSE);
END ELSE BEGIN
// Skip header
CurrFile.TEXTMODE(TRUE);
CurrFile.READ(Dummy);
CurrFile.TEXTMODE(FALSE);
END;

For import files I use SEEK function of CurrFile on PreDataItem():

Integer - OnPreDataItem()
CurrFile.SEEK(349);

where 349 is the end column of the first row (header of csv).

You can use notepad to view the column.

Regards.