none
SSIS Loading Data From Excel With Multiple Data Types in Column is not working properly RRS feed

  • Question

  • Hello Everyone,

    We are trying to Load data from several Excel Spreadsheets into SQL Server Tables.

    For this purpose, we are using SSIS to read and load the data. However we have found that the Excel data is not getting loaded properly into the SQL Server Table. Basically we noticed that if there is an Excel Column with All Integer Values in the first 8 rows and then it holds Character or Alpha Numeric values in the rows below, the system by default identifies that column as an Integer column and sets all the cells with Character or Alpha Numeric values as NULL.

    The SSIS Excel connection manager nor the Excel Data source gives me an option to manually alter the expected data types.

    This issue has been very heavily discussed in several forums but Microsoft has not given any solution for this till date.

    Here is a sample excel file:

    When i try to import this in SQL Server Table using SSIS, this is how it shows the data preview:

    Many people have suggested Windows Registry Edit to force excel to read all records before deciding on the data type. This is not possible in my case as the clients will not allow us to play with the registry settings of the system.

    Some have suggested to add "IMEX=1" Extended Property in the Excel Connection String. This is not working for my case.

    Some have suggested to add "TypeGuessRows=99999" in the Extended Properties of the Excel Connection String. This also does not work in my case.

    I have no control in the way the excel is being generated or formatted, hence having additional hidden text rows before the actual data starts, or having the cell format as TEXT for all the columns of excel is also not an option for me.

    Below is the standard connection string from the Excel Connection Manager that i currently have:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\suvrat\Desktop\RawExcel.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";

    Any way we can force excel connection manager to use Data Types as per our choice?

    This is such a simple requirement, but unfortunately i could not find any useful solutions to this problem.

    Any thoughts on how this type of data can be loaded into the system?

    Thanks in advance!

    Suvrat



    • Edited by Suvrat Rai Wednesday, October 31, 2018 1:19 PM Added more details.
    Wednesday, October 31, 2018 9:45 AM

All replies

  • Hi Suvrat Rai, 

    Usually, this problem can be solved by changing the Connection String to Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test\FileName.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1"; 

    Please try to use this Connection String to extract mixed data from other newly created Excel Files. Is that the same result? Sometimes, this problem can be caused by source file...

    Please check this thread. 

    If this is caused by source file, you will need to convert them to CSV file, to load into SSIS. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 1, 2018 2:15 AM
  • Thank you for your response, but i have already tried IMEX=1 Extended Property and it is not working for me.

    Any other suggestions on how this can be solved?

    Regards,

    Suvrat

    Thursday, November 1, 2018 4:46 AM
  • Hi Suvrat, 

    I'm using Microsoft Access database engine 2016

    You can try with this(32 bit). 

    If it doesn't work, you may need to convert the Excel files to CSV. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 1, 2018 6:23 AM
  • Thank you for your response Yang.

    I am also using the same driver in my case and it is still giving the issue.

    Quoting my connection string again here for your reference:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=;Extended Properties="Excel 12.0 XML;HDR=YES";

    Thursday, November 1, 2018 7:36 AM
  • Thank you for your response Yang.

    I am also using the same driver in my case and it is still giving the issue.

    Quoting my connection string again here for your reference:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=;Extended Properties="Excel 12.0 XML;HDR=YES";

    Hi, 

    This is a standard Connection String. It can't extract text from number columns, Don't use this one. 

    The one with IMEX=1, will treat all data as text, so it can deal with mixed columns. 

    But, from your description, the second one isn't working too. So, I suspect there would be something wrong with your source file, someone else have come across this kind of issue. 

    The easiest workaround is to convert Excel files to flat files. 




    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 1, 2018 7:51 AM
  • Sure Yang,

    Converting the file format to CSV seems to be the last resort, but i still want to explore if we can avoid doing that and ingest directly from Excel. I could reproduce this error with a simple excel where i had placed some data, so this does not seem to be an issue with the source file.

    Regards,

    Suvrat


    • Edited by Suvrat Rai Thursday, November 1, 2018 9:08 AM
    Thursday, November 1, 2018 9:07 AM
  • Hi,

    Try changing the registry setting HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Nod\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows to 0, by default it is 8.


    Cheers,

    Thursday, November 1, 2018 9:14 AM
  • Hi Amit,

    Thank you for your response.

    I am aware of this workaround, but altering the system registry is not something that our clients will allow us to do as this can lead to system instability.

    Hence we are trying to find a solution that lies withing the realm of SSIS.

    Regards,

    Suvrat

    Thursday, November 1, 2018 9:28 AM
  • Hi Suvrat,

    I would recommend you check the commercial COZYROC Excel adapters. They will read your data properly. Guaranteed.


    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    Monday, November 5, 2018 2:10 AM
  • Hi Suvrat Rai, 

    Any update on this problem?

    Have you found a better solution to load these data than converting them to CSV? 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, November 6, 2018 5:28 AM