Automated Data Import from Excel

[thesti]

hello,

i know that we can import data to SQL server from excel files, i've tried this in SQL server 2000 enterprise manager.

but i'm stucked at how to configure which table the data will be inserted to and which column is bound to which field of the table,

can someone please kindly show me how to do this?

Thank you.

[Delerna]

You can do that through DTS. Too big a subject to cover the details here.
I suggest you Google "SQLServer DTS tutorial" and ask specific questions for any difficulties you have here.

However the basic process is this.
You create a DTS package that imports the excel data into a table.
You right click the DTS package and schedule it.
You modify the schedule so that it executes the DTS as desired.


The basics of the DTS are
Create an EXCEL connection and connect it to your file
Create an SQLServer connection and connect it to your SQL Server
Run a "Transform Data" task fom the EXCEL to the SQL Server connection
Edit the "Transform Data" task and in the
----Destination tab, select the table.
----Transformation tab, connect the source and destination fields

HINT:-the source and destination fields will be automatically connected if the field names are the same.

TEST the DTS
right click the "Transform Data" task and execute it.
Check the table that the data came in OK.

ADDENDUM
You will probably need to add an "Execute SQL" task to the DTS that deletes any current data in the table before the "transform data" task executes.