Excel to SQL

[Shin25]

Hi All

I have just been handed a task to export Excel workbooks with multiple sheets into sql. The excel sheet/workbook are heavily filled with data so there will be alot of data transfering.

This will happen on a regular basis...twice a month.

At the moment Iam gathering ideas how best to do this....I look forward to your ideas.....

Many Thanks

 

[fredericofonseca]

fastest way is probably to export each spreadsheet into text files, tab delimited, and then bulk insert into the database for further processing.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Shin25]

Thanks for the above but how do I export each sheet/workbook to text file(tab delimited)?

 

[philhege]

If your Excel file formats are static, you should do this all in the DTS package. Create a transformation task for each worksheet/tab in the spreadsheet.

 

[Shin25]

Hi All

I have the above describled.....ie. created a transformation task for a sheet, but on a sheet there is much information how select a range of information from a sheet in the DTS set up for eg.

only want k14 to k25 data from sheet 1?

Many Thanks

 

[philhege]

SELECT TOP 25 F11 FROM [MySheet$]

and then run a SQL task to lop off the first thirteen.

You can also look into named ranges and Excel ActiveX, though this is frowned on for use on a server.

I sure hope you're not planning to change this often.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.