Feb 17, 2012 6:58 AM
Issue with Reading from a flat file
-
Like (0)
Hi,
I tried reading from a flat file which will usually have 3 columns. It was properly working.
But sometimes we received the files with one more column, or a delimiter at the end. And this happens for only a handful of of records in a file which has thousands of records.
We can use fillrecord if we are gettig lesser than expeceted number of columns.
But in this case how can we handle the extra delimiter?
You can probably use sed and regular expressions to clean-up your file prior to import.
This is being done from a stored procedure and we have access only to aginity workbench... Not the NZ box...
Hi,
How about creating a table with VARCHAR columns that is wide enough to always load the records and then use SQL to transform to your target?
Shaun
There is a -TruncString option, but I doubt that would fix the problem.
If that doesn't help, define an extra column in the landing table and use the -fillrecord option, Ignore the extra column. This assumes you are using NZLoad and bringing data into staging and can change the staging table definition.
If you are using an external table definition, just add the extra column, add -fillrecord, and ignore the extra column in your SELECT query.
TruncString truncates the Column values if they exceed the given width... This one will be helpful in some other cases...
We should give more number of columns than the actual...
Thanks Nick, Shaun and Jordan... ![]()

