0

I have an Excel '97 spreadsheet that is generated by an external automated process that I would like to import into a SQL [2008 R2] table via SSIS. The column I am importing contains text values in some cells and date values in other cells. To connect to the Excel data, I have an "Excel Source" Data Flow Source, where the OpenRowset property has been set to a specific column range: Sheet1$A1:A100. I have added a grid Data Viewer directly after the Excel Source so that I can view the "raw" results.

When the package is executed, I review the Data Viewer results and all of the cells that contained a date come through as NULLs. Oddly, the text fields and number fields come through fine and show up correctly in the Data Viewer. If I put a single quotation mark (apostrophe) in front of the date value so that it treats the date as text, it imports properly. If I right-click on one of the date fields in Excel and go to "Format Cells...", they are all displayed as "Date".

These Excel files are being generated automatically by a process that I can't control, and I can't manually edit each file to get them to import properly. Within the External Columns portion of the Excel Source, the column is shown with a DataType of "Unicode string [DT_WSTR]", so I would think it would just import it as text.

I would GREATLY appreciate any suggestions on how I might get these date values to import properly. Thank you in advance!

Loki70
  • 613
  • 4
  • 15
  • 36
  • You say your column has a mixture of text and date values, and the column is being imported as "string". Since dates are stored in Excel as numeric values (and only displayed as text), maybe that's why your dates are coming through as null. A good start would be to at least get your column datatype consistent. – Tim Williams Oct 14 '11 at 18:47
  • Sounds like the problem resides with whatever process creates the excel documents. What format are these values in? Copy and paste an example of a value the process kicks out for you so we can see how its writing them into the excel file. – KreepN Oct 14 '11 at 19:50

1 Answers1

1

I don't know if there is a "good" way to handle this situation in SSIS, but I can think of a few more-or-less ugly ideas. In order of increasing hackiness:

  1. Ask whoever provides these files to you to use a more database-friendly format (instead of one where the column's data type changes from row to row).
  2. If you know ahead of time which rows will contain text and which will contain dates, you could try opening the spreadsheet multiple times, changing the ranges each time so that the data type is consistent.
  3. You could manually open the spreadsheet in Excel and save it as a text file; then feed the text file to your SSIS package. All the dates will be converted to text (based on your locale).
  4. You could write a Windows script to open the spreadsheet in Excel and save it as a text file, then feed the text file to your SSIS package.
  5. You could write an SSIS Script Task that used the Excel automation model to open the spreadsheet and handle each cell in whatever the appropriate manner might be.
  6. You could roll your own Excel connection manager that could be configured to treat all cells in a particular range as text. (Hmmm. This may be getting past hacking and into yak shaving.)

I think #1 is the best long-term solution, but it doesn't sound like it'll happen quickly. That said, saving the spreadsheet as text will at least make it something SSIS can handle easily, and that might be enough to get over the immediate hurdle.

Edmund Schweppe
  • 4,992
  • 1
  • 20
  • 26
  • First off, thanks for all of the great comments & suggestions. In the end, I found an article detailing how it was looking at the first 8 characters to determine the data type . To resolve this, I added the "IMEX=1" text to the end of my connection string - see http://support.microsoft.com/kb/194124 . Once I did this, it correctly imported the date fields. Again, I CANNOT change the format of this Excel document from my customer, so I had to handle it this way. Thanks again! – Loki70 Oct 17 '11 at 00:36
  • You're welcome - and thank **you** for the pointer to the IMEX=1 tip! – Edmund Schweppe Oct 17 '11 at 11:51