none
Sql 2008 SSIS, Importing Excel 2003 Spreadsheets, silent errors

    Question

  • Using SSIS I imported an Excel 2003 spreadsheet (don't know what Excel SP was installed) into a sql table. Most of the data imported well but ZIPCodes were another matter entirely. Sometimes they would import correctly, other times they were replaced by NULLS in the table. That is they did not import at all. So I converted the Excel ZIPCode columns to Excel "text" but that didn't solve the problem either. Then I modified the SSIS spreadsheet import default from "Excel 97-2003" to "Excel 2007" and that worked!  That's a fine howy-do! Soon we are expecting an onslaught of daily spreadsheet files that need to be imported into sql.

    Question 1: I don't trust SSIS anymore to accurately import all spreadsheet data. What can I do to minimize this issue.

    Question 2: "Excel 2007"??? Are their SSIS drivers for Excel 2010, 2012, 2013?

    TIA,

    edm2

    Wednesday, September 11, 2013 12:03 AM

Answers

All replies

  • Excel is not a data exchange solution in the 1st place, but a desktop application.

    The Excel driver typically must be set to data exchange mode - the IMEX=1 option

    Then to eliminate the NULLs you can put a Conditional Split immediately after the Excel data source and then test a field for ISNULL and replace the NULLs with say an empty string.


    Arthur My Blog

    Wednesday, September 11, 2013 2:20 PM
    Moderator
  • Hi - in the past I use and OLEDB connection manager with the 'Microsoft Office 12.0 Access Database Engine OLEDB Provider'. Set your path to the data file the then use the 'All' tab - set the Extended properties to: Excel 12.0;HDR=YES( or NO);IMEX=1

    HDR is to control column headings as first row (yes or no) IMEX = 1 (Import Mixed Types) - allows mixture of data types - converted to text for the column data...

    see: http://support.microsoft.com/kb/194124/en-us for full details here.

    OLEDB Excel 12.0


    MCSA: (SQL 2012, SQL 2008, Windows Server 2008) MCITP: (SQL BI & SQL 2008) MCT: 2000 - 2013 SQL Training London

    Wednesday, September 11, 2013 2:44 PM
  • Arthur,

    You wrote  >>> Excel is not a data exchange solution in the 1st place

    You are technically right but in practice many people use Excel xls files as data exchange solutions!

    edm2

    Thursday, September 12, 2013 3:16 AM
  • Awesome Lawrence. I was totally unaware of that solution!

    edm2

    Thursday, September 12, 2013 3:17 AM
  • Lawrence,

    What versions of Excel does the  'Microsoft Office 12.0 Access Database Engine OLEDB Provider' support?

    Up to Office 2013?

    TIA,

    edm2

    Thursday, September 12, 2013 3:15 PM
  • Hi edm2,

    Yes, the Microsoft Access Database Engine 2010 Redistributable supports the Excel spreadsheets created by Excel 97-2003 or Excel 2013. When typing the Extended Properties for the OLEDB Connection Manager, for Xlsx files, it should be "Excel 12.0 Xml;HDR=YES;IMEX=1".

    When we download the Microsoft Access Database Engine 2010 Redistributable from http://www.microsoft.com/en-in/download/details.aspx?id=13255, it is the latest version (SP2) released on July 22, 2013.

    Reference:
    http://www.connectionstrings.com/excel/

    Regards,


    Mike Yin
    TechNet Community Support

    Monday, September 16, 2013 7:50 AM
    Moderator
  • Hi Mike, I need a little more guiding. I downloaded the 'Microsoft Access Database Engine 2010 Redistributable' but how do I add the OLE DB Provider to Visual Studio 2010, so I can chose it as a Connection Manager? Regards Elisberg

    Answer: Download and install the 32 bit version - even though you have a 64 bit system. The Microsoft Access Database OLE DB Provider should now be one of the OLE DB Providers in SSIS.
    • Edited by Elisberg Wednesday, October 09, 2013 9:22 AM
    Wednesday, October 09, 2013 7:59 AM