CSV Import, different no of columns?
-
Hey guys,
I am trying to get a DTS job to loop round a folder importing files as it goes. HOWEVER:
Some csv files may have 4 columns while others may have 5. At the moment, my code simply throws up an error of "Column name 'Col005' was not found.".
Anyone any idea how I can force the transformation to accept my 5th column?
Cheers,
Stuart
-
Will you please provide more information as to how your DTS package is designed? How are you looping through the files? Are you changing the data source for the transform on each loop?
-
Hi,
I am using the looping example here http://www.sqldts.com/default.aspx?246
My csv files are in the following two formats.
"TEXT","OTHERTEXT",12/12/2005,56.4
&
"TEXT","OTHERTEXT",12/12/2005,56.4,"UK"
In effect, I think I need to count the columns in the csv file somehow? Something like DTSSource.Columns.Count??
Hope this helps
-
Very interesting scenario. The way I would go about the issue is by creating a new ActiveX script task and first check each csv for column counts. If the csv is missing the 5th column, simply add a new comma delimiter to each row.
Otherwise, you'll be left to figure out how to get the column count before engaging the transform, then you'll need to remove and re-add the source columns to the transform on each loop. Doing that would prove very interesting... but could be done.
Good luck
-
another thought would be to have two data transformation tasks. One would only start if the column count was 4, the other would only start if the column count was 5.
Is there any difference in the naming convention or the location of the files that you could use? This would avoid you having to figure out column count.
-
why not use the bulk insert within tsql to perform this, then it wouldnt matter if there were 4 or 5 columns as long as the table you read into has 5 columns
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply