This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Hi
I use SSIS to load excel files in SQLServer
I identified that SSIS use diferents datatypes when the excel columns are numbers or letters.
A friend told me that excel identified the first 8 rows to set the datatype.
Is it correct?
Yes
By default it uses first 8 rows to determine the datatype
The registry setting TypeGuessRows is what determines number of rows to be scanned
If its set to 0, it will scan as much as 16234 rows to determine the types
If you want to scan entire data for determining datatypes, you need to set TypeGuessRows value to 1,048,576
Its all explained here
https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/initializing-the-microsoft-excel-driver?tabs=office-2016&redirectSourcePath=%252fen-us%252farticle%252fusing-the-typeguessrows-setting-for-excel-driver-6aa3e101-2a90-47ac-bf0f-7d4109a5708b
Inside SSIS you can also specify IMEX=1 in Excel connectionstring if you want driver to handle intermixed datatypes
See
https://visakhm.blogspot.com/2013/01/excel-issues-in-handling-intermixed-data.html
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