Is there a way to import an Excel worksheed file (ver 2.1) into SQL 2005 using SSIS?

views:

172

answers:

Is there a way to import an Excel worksheed file (ver 2.1) into SQL 2005 using SSIS?

I am receiving an Excel Worksheet file (ver 2.1) from one of our clients as part of a weekly date update. I am trying incorporate it into our automated update process, but I can't import the file in SSIS (or Sql Management Studio) as the oldest format I can process is Excel 3.0.

Is there an update that will add support for this older format? Or possibly a utility that I can have SSIS execute to convert the Excel worksheet into another format that SSIS can process?

+1 A:

What version is 2.1? Is that Excel 2000?

In any case, if the customer are using a version of Excel so old that there's no ODBC provider for it, then they should send you CSV, with headings.

John Saunders 2009-03-11 14:27:52

I believe that 2.1 format is from the original version of Excel released for Windows back in 1987.

Nathen Silver 2009-03-11 14:38:30

If 2.1 is from Excel 1.0, then the best thing you could do for your customer is stop enabling them - tell them to upgrade. 12 year old software is just plain foolish. Can Excel itself still import that version?

John Saunders 2009-03-11 14:43:25

Excel 2003 opens it without any problem, and will still save in the format as well.

Nathen Silver 2009-03-11 15:03:14

Interesting. Do you know if 2007 does it? If you want to find out, could you send me a trivial spreadsheet in that format to [email protected]? I'm curious for some reason. I was actually young back in '87...

John Saunders 2009-03-11 15:06:29

I don't have access to Excel 2007. I've set you a sample file for you to satisfy your curiosity.

Nathen Silver 2009-03-11 15:31:59

Thanks. Excel 2007 can read it. For the record, SQL Server 2008 Import wizard can read Excel 3.0 and above. Excel 2007 can save as version 5.0 and above. Q: Are the customer actually _running_ 2.1, or maybe run 3.0 and save as 2.1?

John Saunders 2009-03-11 15:48:42

I don't any knowledge on what the customer is running. Hopefully they will be able to send me the data in a different format.

Nathen Silver 2009-03-11 16:55:40

The only way I can think of, at least for now, that might work is use an Execute Process task and have the task use Office bits to convert the Excel file. I am not sure exactly what the code would look like, but after you are done, you would have a file in the newer format, which could be consumed by SSIS.

Gregory A Beamer 2009-03-11 14:28:11

Would that require installing Office onto the database server machine then (this is where the SSIS would be executing)?

Nathen Silver 2009-03-11 15:33:49

This entry may help..

http://stackoverflow.com/questions/338776/creating-a-sql-table-from-a-xls-excel-file

madcolor 2009-03-11 14:28:24

ansaurus

tags:

views:

answers:

Is there a way to import an Excel worksheed file (ver 2.1) into SQL 2005 using SSIS?

related questions