December 12, 2007 at 4:09 am
Hi,
I'm having an excel file with around 50 columns and I want to write a DTS package which should create a table in the database and import the data from excel file to that table for the first time and after that it should append the records whenever it is run by the user.
FYI: The number of columns and their order will be same everytime and I'll be run on an weekly basis to import the data in the database.
Please help me in doing this...!
Thanks in Advance.
Rohit
December 12, 2007 at 6:42 am
Rohit,
This should be a fairly simple process, if you have any experience of DTS.
Open DTS and add a lonk to tour spreadsheet and also a link to your database. Then connect the 2 with a Transform Data Task. Double click thender sourse. Click on the Destination tab and you will have the opportunity to create a table in your database. The transformations Tab allows you to match the spreadsheet columns with the table columns.
You want to insert your data to a Temp table and then add a SQL statement to your DTS package to insert the data to the "live" table. This will enable you to only insert new data or update existing data - by using the primary key of the table.
Beware of importing from spreadsheets. If a column has its first few(10?) rows empty it will import NULL for ALL rows, even when data exists in the spreadsheet!! My advise is to add a starting line to the spreadsheet (first import only) with dummy data and then delete this once all data has been imported.
Also, take care with how the spreadsheet columns are formatted, the data does not always convert as one expects.
Good luck
Colin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply