June 22, 2005 at 7:10 pm
I have a table with 10 columns and data from text file (csv) is transferred into this table.
The text file may have rows with less than 10 columns or more than 10 columns.
if it has 10 columns it is copying into the table. if it has more than 10 columns it is truncating other columns and just copying only those 10 columns.
is there any script that automatically adds new columns into the table if it finds a row in the text file with more than 10 columns. Right now we are manually adding the column if we find any extra column.
will be very thankful if anyone can help me out.
ex: a,b,c,d,e,f,g,h,i,j
k,l,m,n
o,p,q,r,s,t,u,v,w,x,y,z
if the file is like this. the third row is copied till 'x' into the table as it is the 10th column, truncating the last columns 'y' and 'z'.
June 22, 2005 at 9:28 pm
DTS does not do this automatically. You will need to write some code to handle this. If the datapump is expecting a comma separated format file, then you probably need to rethink this because there is no simple way to change what the datapump is doing on the fly. It can be done but I don't think it is a good idea.
What you might want to consider is importing each record as a single large field (each line = one column). Once you have the data in SQL, work out how many columns you actually have and adjust the table definition accordingly. You can then split up each record and insert it as you want.
Having said this, if your source data is so variable that you cannot even determine what it looks like before you are using it, I think you have a much bigger problem. Why is it that you either have no control over the file format or that you have insufficient information to determine what the data actually looks like ? And once you have extra columns, exactly how can you work out how to use them ?
June 24, 2005 at 9:02 am
Hi,
I think someone asked almost this same exact question on Wednesday of this week. Someone posted a possible active-x solution. If you are getting the daily emails for this group, check out the one from Wednesday.
Teague
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply