Load CSV using ASCII when there is missing data
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.
Answers
-
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.
12 -
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:
- preparing a proper heading file, which will have always the correct headers
- 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 - linking Board to read the new file generated in this way
- 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.
4 -
Thank you for your suggestions.
Unfortunately the row length is variable so using character ranges isn't applicable.
1 -
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.
0 -
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
1 -
Thanks Bjorn. Upgrading to v10.x soon so will test it out.
0
Categories
- All Categories
- 2K Forums
- 1.8K Platform
- 159 Academy
- 325 Resources
- 1 Board Knowledge Base
- 50 Best Practices
- 49 How-To Guides
- 19 Board Advocacy Program
- 192 Blog
- 4 Groups Hub
- 4 About Groups
- New Community Members
- DACH
- Japan
- 4 Community Captains
- 1 About Community Captains
- 2 Meet the Community Captains
- 1 Topics & Thought Starters
- Learn from the Board Captains
- Release Notes
- Academy
- 2 Board Academy
- 8 ILT/VILT Course Catalogue
- 13 e-Learning Course Catalogue
- 4 Academy Forum
- 1.2K Idea Exchange
- 337 Partner Hub
- 94 Support
- 14 FAQ's
- Customer Support Portal
- 54 Support Articles
- BEAP