October 18, 2005 at 9:06 am
I have a situation where I need to take a number of excel files and worksheets and combine them into 1 CSV file. The worksheets have varying column headings such as...
Worksheet1 - AcctNumber, FirstName, LastName, Amount, Flag1, Flag2, Address, City, State
Worksheet2 - AcccountNo, Name, Amount, Address, City, State
The CSV file will have a predetermined file layout such as...
AccountNumber, Name, Amount
In my situation, there are 50+ excel worksheets all with varying information that needs to fall into the file layout for the CSV file that needs to be produced from all the worksheets. When all is said and done, the CSV file will contain a million plus rows.
I am looking for a solution in how to produce the desired results without having to create 50+ data pumps from the Excel worksheet to a temp table and then after all data pumps complete - export to the CSV file. There has to be a better way, but, then again, maybe there is not.
How would you go about producing the desired results? Maybe DTS is not the way to go.
Thanks in advance for your suggestions,
Jody Wood
October 18, 2005 at 4:48 pm
create a table with pre-determined columns and then do the transformation... still you have go through every connection and do the transformation according to the files....
I don't see the way to do it like you said if your fields are not the same. You can get the files with same columns together and then do it with fewer steps but I think this still not the efficient way...
go through all the files and create blank columns wherever you have to and format every file like the predetermined file and then use DTS...
October 19, 2005 at 8:25 am
To Ghanta Bro - Yeah, I have done it that way in the past with a smaller set of data, but it was very time consuming. I was hoping to avoid that method, which is why I am looking for other ideas. Thanks for your feedback!
At this point, the only thing that I am coming up with is to write a C# application rather than handle this through DTS.
If there are other ideas, I am interested to hear them.
Thanks,
Jody Wood
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply