Importing Data 1

[xloop]

Having a slight problem importing a list of sites from excel into an existing sql server table, basically i'm using access to import the list of sites (siteno,sitename,address1..etc) from excel to a new table, then use the link table from sql server to create a append query.

This used to work fine, however it now looks like it's going take about 3 hours, it gets to about 2 little progress bars after about 1 1/2 mins.

Is there an easier way to do this? I need two expressions to, that is one field named clientid needs to be fixed as 20 and brandid needs to be 0 (both fields not in excel)

Any help appreciated.

 

[JohnDTampaBay]

What version of SQL are you running? Have you tried using a DTS package to import directly from Excel to SQL?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[xloop]

Running 2000

DTS sounds good but i have never used it before, i've created a package, created both connections, do i now need to use 'Transform Data Task' ?
If so, i write click on the arrow, choose properties then it brings up the properties, click on the transform tab and it looks like i can map my columns, so far so good, however when i go to map them it brings up a 'Create New Transformation' box, then i have no idea which item to choose (ActiveX Script, Copy Column, Trim string..etc)

 

[JohnDTampaBay]

You're on the right track. If your columns names are the same in the spreadsheet and table, then they should map automatically. There are several ways to handle the expressions you need. Here's the way I handle expressions.

Change the source of the Transform Data Task to a query instead of a Table/View. In the query box, write a SQL SELECT statement against the Excel table to pull the columns you need. Include the two expressions as columns. Use the Preview button to verify that you are selecting the data you want. Switch to the Transformation tab and remap the columns from the SELECT to the destination table.

http://www.SQLDTS.com

has some great articles that will help you out. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[xloop]

Angel

Worked a treat, thank you!

Rob

 

[JohnDTampaBay]

Glad to be of service. Thanks for the star.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.