Importing Excel files to SQL Server 7.0

[princessinthetower]

I know 2 ways to import Excel files to SQL Server

1) Use DTS to import
2) Convert the source table to a SQL table and insert rows via an INSERT script.

I would like to write an application to make the insert more automatic a process that needs to happen once or twice a month. Where should I look for the tools to do this? In Excel? In SQL? I want to do as little handling, mapping, and conversion as possible. Thanks for any advice.

 

[vechalapu]

Hi,

Use VB like application to convert Excel application to comma delimited format and using bcp utility with format file, you can automate the process.

 

[tlbroadbent]

You can certainly automate DTS by creating and scheduling the DTS package. Is there a reason this is not acceptable?

If the schedule varies, such that setting up a job is difficult or impossible, you could set up a job to start by an alert or execute the DTS import package from a stored procedure.

You can also create a linked server to the Excel spreadsheet and access the spreadwheet directly from SQL, using SQL queries. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[princessinthetower]

DTS may be acceptable but I can’t figure out how to do other than an exact column for column import. It is easy to do this using the ‘copy table from source’ option, but I want to ‘use a query to specify the data to transfer’ because I want to apply some conditions.

The source Excel file will be address updates. They need to be inserted into an existing table but not updating or deleting existing records. Below is an example of a SQL query (simplified) that would work successfully if the data were exchanged between 2 SQL Server tables instead of Excel to SQL Server.

INSERT INTO address
SELECT addrid = impaddrid,
addrline1 = impaddrline1,
addrline2 = impaddrline2,
addrtype = impaddrtype,
addrdate = getdate()

FROM import JOIN address
ON addrid = impaddrid WHERE impaddrtype = 'h'

Within the DTS query statement window this doesn’t work. For one thing, it doesn’t understand getdate(). There is an Error: ‘No Destination field name in Insert Into Statement.’

I know this must work. I just need to figure out what’s wrong with my query syntax.

 

[MinAmiChief]

The structure of the table I am importing from Access and Excel should always be the same yet the name changes for each month. So...

1. Use xp_cmdshell to change the name of the file I am importing. So that the DTS package recognises it. eg from JuneData.xls to Data.xls
2. execute the DTS package via the DOS dtsrun command. DTS package imports Data.xls
3. Change the file name back to what it was.
Rename Data.xls JuneData.xls

Hence all I need from the user is the file name which is collected from an MS Access program. You could also do these three steps from VBA. (I am going to try this soon)
1. use write line to write the name of the file into the .bat file
2. Use Shell("Net Send NTUserName Message 'Hello'", 0) to execute your .bat file.

This way the user can import your file into SQL server for you via your Excel/Access application.

DTS can do a lot more however.




Be the change that you want to see in the world - Mahatma Gandhi