none
Setting TypeGuessRows for excel ACE Driver RRS feed

  • Question

  • I set the TypeGuessRows registry value to 0 in this location:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

    Is that the correct location where it would be set when creating excel files through SSIS 2012 using the ACE driver like this one:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\myfile.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1";

    I'm still having problems with fields over 255 characters and thought maybe that I had set TypeGuessRows in the wrong location.

    Thanks


    • Edited by R_i-c_h Wednesday, April 22, 2015 9:32 PM
    Wednesday, April 22, 2015 9:32 PM

All replies

  • Hi R_i-c_h,

    The root cause and the solution of this issue have been documented in the Excel Source article, and we can see that:

    When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key.

    One more thing we need to keep in mind is that the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows only applies to Excel 97- 2003. For Excel 2007 and higher version, Excel Open XML (.XLSX extension) actually uses ACE OLE DB provider rather JET provider. If you want to keep the file extension as .XLSX, you need to modify the following registry key according to your Excel version:

    • Excel 2007: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
    • Excel 2010: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
    • Excel 2013: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows


    Thanks,
    Katherine Xiong

    If you have any feedback on our support, please click here.


    Katherine Xiong
    TechNet Community Support

    • Proposed as answer by Visakh16MVP Thursday, April 23, 2015 7:07 AM
    Thursday, April 23, 2015 6:58 AM
    Moderator
  • And notice that changing it to zero will only scan the first 16384 rows instead of the first 8


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, April 23, 2015 8:47 AM
    Moderator
  • Hi R_i-c_h,

    The root cause and the solution of this issue have been documented in the Excel Source article, and we can see that:

    When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key.

    One more thing we need to keep in mind is that the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows only applies to Excel 97- 2003. For Excel 2007 and higher version, Excel Open XML (.XLSX extension) actually uses ACE OLE DB provider rather JET provider. If you want to keep the file extension as .XLSX, you need to modify the following registry key according to your Excel version:

    • Excel 2007: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
    • Excel 2010: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
    • Excel 2013: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows


    Thanks,
    Katherine Xiong

    If you have any feedback on our support, please click here.


    Katherine Xiong
    TechNet Community Support

    I'm have Excel 2010 and have set the registry key TypeGuessRows to 0 at

    HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\

    and in SSIS 2012 My Excel connection string is:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\myFile.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";

    If I set the connectionstring to 14.0 I receive a design time error. If I set it to 12.0, it runs but an error occurs because of the field being over 255 characters. Does my connection string look correct?


    Thursday, April 23, 2015 1:59 PM
  • And notice that changing it to zero will only scan the first 16384 rows instead of the first 8


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    If I could get it to work, 16384 would be enough. This report at least will never contain more than 1000 rows.
    Thursday, April 23, 2015 2:00 PM
  • I'm have Excel 2010 and have set the registry key TypeGuessRows to 0 at

    HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\

    and in SSIS 2012 My Excel connection string is:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\myFile.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";

    If I set the connectionstring to 14.0 I receive a design time error. If I set it to 12.0, it runs but an error occurs because of the field being over 255 characters. Does my connection string look correct?


    Connection string looks correct to me. You have TypeGuessRows property at correct location.

    Maybe try to Restart SSDT/Visual Studio or even the system.

    Another workaround would be to to put long dummy string having lenth > 255 and later skip this row.

    Please refer: http://vaibhav-c.blogspot.in/2015/04/ssis-fix-excel-truncation-error-without.html


    Cheers,
    Vaibhav Chaudhari
    MCP, MCTS, MCSA (SQL Server 2012)


    Friday, April 24, 2015 7:16 AM
  • what can we for Csv
    Monday, November 26, 2018 9:01 AM
  • what can we for Csv

    Same method you can use for CSV also

    Do you have predefined template created for CSV export? Or does the file gets generated dynamically without any idea on the structure?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, November 26, 2018 9:08 AM
  • Those registry keys didn't worked for me; instead I change this:

    Equipo\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0

    and worked for me:

    I have Windows 10, SQL Server 2017 and tried to import data from xls (1997-2003) file


    Omar Diaz

    Wednesday, October 16, 2019 10:00 PM