0

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?

Nathen Silver
  • 439
  • 4
  • 7

3 Answers3

1

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
  • 160,644
  • 26
  • 247
  • 397
  • I believe that 2.1 format is from the original version of Excel released for Windows back in 1987. – Nathen Silver Mar 11 '09 at 14:38
  • 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 Mar 11 '09 at 14:43
  • Excel 2003 opens it without any problem, and will still save in the format as well. – Nathen Silver Mar 11 '09 at 15:03
  • 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 johnwsaundersiii@live.com? I'm curious for some reason. I was actually young back in '87... – John Saunders Mar 11 '09 at 15:06
  • I don't have access to Excel 2007. I've set you a sample file for you to satisfy your curiosity. – Nathen Silver Mar 11 '09 at 15:31
  • 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 Mar 11 '09 at 15:48
  • 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 Mar 11 '09 at 16:55
0

Are you using SSIS just for importing the file or is this a part of some bigger solution and one of the step is importing the excel file. If not then you should go down the route of using the a dll and call it from somewhere. This example may help you http://www.codeproject.com/Articles/115578/Excel-to-SQL-without-JET-or-OLE-Version-2

if the import is part of the bigger solution then you can still have this as a external dll and call it withing SSIS.

bhupendra patel
  • 3,139
  • 1
  • 25
  • 29
0

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
  • 16,870
  • 3
  • 25
  • 32
  • Would that require installing Office onto the database server machine then (this is where the SSIS would be executing)? – Nathen Silver Mar 11 '09 at 15:33