Reading Excel Sheets - Column DataTypes - Change default

Hot Topic

Example of merge ,hash and nested join
SQL Server 2005 with 2000
HTTP access for standard Analysis Services Edition of SQL Server 2000 issue
Re-Create All Indexes in database
Export problem
How To View Time Series Chart In C#
How can I get the actual line number from inside flatfile sources for each record?
Full Text "And Not" Fails
DTC error with concurrent data flows
Execute package via a web application failing

SQL Server

Extracting Cube detailed info
Failed to decrypt XML node "DTS:Password"
Database Engine from One Computer to Another
How to handle printing big batches of reports
InvalidProgramException on Adapter.Fill
Smo Column Object
Development Procedures
How to insert data with single qoute?
Datafields in report header
XML DOM Document

Reading Excel Sheets - Column DataTypes - Change default

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.


Answer this question

Reading Excel Sheets - Column DataTypes - Change default

  • thepaul

    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

  • jh2005

    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

  • onur6n

    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.

  • reachrishikh

    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

  • williamwon

    Thanks Doug.

  • jay2068

    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

  • Reading Excel Sheets - Column DataTypes - Change default
Answer this question