none
Reading Excel Sheets - Column DataTypes - Change default RRS feed

  • Question

  • Hi,

    I am reading Excel sheets using the Excel Source in a Data Flow. The driver decides the data type of the column based on the first 8 (?) values which it reads. How can this default be changed to, say, 1000, please?

    This is because I am reading columns in which the first, say, 20 cells have no values and this is causing the driver to make ALL the values in the column to be null.

    Thanks.
    Thursday, December 1, 2005 3:57 PM

Answers

  • That's correct, this is not a connection string option for the Jet provider.

    Once upon a time, it was theoretically a connection string option named MaxScanRows for the Excel ODBC driver, but oops! someone forgot to actually implement it in the code and it never worked.

    Tweaking the registry value is the only option. I suppose that this could be done in a Script task...in fact, you could set it back to its previous setting at the end of the package.

    The default setting always used to be 8, but I've found machines where it's set to 25 -- not sure whether Office does that? I believe I've also read in the past that 0 (zero) = "all rows," but I don't have that in writing, and analyzing 16,000+ rows to guess at a column's datatype would be a bit excessive.

    -Doug
    Tuesday, December 6, 2005 5:57 PM
    Moderator
  • Thanks Doug.
    Tuesday, December 6, 2005 7:17 PM

All replies

  • In Connection Manger Editor Click on Advanced  and then click on Suggested types  and there you can change number of values to 1000 (i think that is the max)

    Hope this works

    -Raj
    Thursday, December 1, 2005 4:05 PM
  • Sadly, there is no Advanced Option for the Excel Connection Manager.

    And, it is not possible to read an Excel file using a Flat File Connection Manager.

    Does anybody else have any other suggestions?

    Thanks.
    Friday, December 2, 2005 11:59 AM
  • You simply need to change a registry value. This is discussed in the BOL topic for the Excel Source, though in the context of a slightly different known issue.

    For information about increasing 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, see PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error.

    -Doug
    Friday, December 2, 2005 4:16 PM
    Moderator
  • Thanks Doug ... So, just to confirm, does it have to be a change to a registry entry? i.e. it cannot be changed in the Excel connection string?
    Tuesday, December 6, 2005 5:33 PM
  • That's correct, this is not a connection string option for the Jet provider.

    Once upon a time, it was theoretically a connection string option named MaxScanRows for the Excel ODBC driver, but oops! someone forgot to actually implement it in the code and it never worked.

    Tweaking the registry value is the only option. I suppose that this could be done in a Script task...in fact, you could set it back to its previous setting at the end of the package.

    The default setting always used to be 8, but I've found machines where it's set to 25 -- not sure whether Office does that? I believe I've also read in the past that 0 (zero) = "all rows," but I don't have that in writing, and analyzing 16,000+ rows to guess at a column's datatype would be a bit excessive.

    -Doug
    Tuesday, December 6, 2005 5:57 PM
    Moderator
  • Thanks Doug.
    Tuesday, December 6, 2005 7:17 PM