By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

Automated Data Import from Excel

100+
P: 144
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.
Oct 24 '09 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
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.
Oct 25 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.