Load CSV using ASCII when there is missing data

Ben Campbell

Hi,

I receive a daily CSV from a third party. The first row is the column header, the first 5 columns always have data and the next two columns only occasionally have data. When I load using ASCII data reader, I can't load columns 6 and 7 because the first row of actual data is almost always blank for these columns. If I re-order the CSV by column 6 or 7, it will load as required. But because it comes from a third party, I can't automatically have it ordered this way.

I have tried to load with 'first row contains headers' not ticked - knowing that it will reject the first row as "Day" is not a valid day field. Whilst this option gives me the chance to specify Field#6 and Field#7, for the next row it reverts to there being only 5 possible fields.

Is there a way around this problem?

 

Thanks.

Unknown

If the row length is consistent, you could try parsing by character ranges, rather than columns.

 

If that's not possible, you may need to drop the data into an intermediate table somewhere. Ragged right files can be tricky if there isn't a consistent format.

Alessio Biondi

You could try some tricks over the txt file.

The final purpose is to add a proper heading to the file. You could achieve this by:

1. preparing a proper heading file, which will have always the correct headers
2. setting a batch file that executes a merge of your heading file and the data file you receive
   see xcopy dos syntax for details Xcopy - Copy files and folders - Windows CMD - SS64.com 
3. linking Board to read the new file generated in this way
4. adding the batch file to the procedure before the ascii datareader

 

I don't know an easy way to delete the first row of your actual data using dos commands: you can always rely on the fact that this row will be discarded by Board, as you said.

Ben Campbell

Thank you for your suggestions.

Unfortunately the row length is variable so using character ranges isn't applicable.

Previous Member

Not sure you if you tried ETL to find a workaround. 

 

There are a couple of things you can try in ETL (a) Use Validation Condition to skip the row if you want to ignore a row (b) Use a formula to set a default value for the column where data is missing. 

Björn Reuber

Hi,

 

which BOARD version did you use (and did your DataReader contain an ETL)? Cause in 10.1.3 there were some optimization regarding  NULL Values and ETL

 

Regards

Björn

Ben Campbell

Thanks Bjorn. Upgrading to v10.x soon so will test it out.