SSIS preview Excel Data Display null solution

Recently, I am working on Excel parsing. Because Excel is not standard, there are many problems in parsing. One problem is that the Excel data has a value, but the Preview Data is null.

 

The reason is that my Excel data appears in 9th columns, while Excel obtains the Data TypeThe first eight rows are scanned by default.. This can be done by adding an optionalMaxscanrows =Set, or in
Change in the DSN configuration dialog boxNumber of rows to be scannedComplete the settings. However, because an error exists in the ODBC driver, the current setting of "number of rows to be scanned" (maxscanrows) does not work. In other words, Excel
ODBC drivers (MDAC 2.1 and later) Always scan the first eight rows of the specified data source to determine the Data Type of each column. Http://support.microsoft.com/kb/257819/cn)

 

You can solve this problem by modifying the registry:

 

Version 32:

Change the value of typeguessrows from 8 to 1.
Excel 97
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ jet \ 3.5 \ engines \ Excel
Excel 2000 and later versions
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ jet \ 4.0 \ engines \ Excel

 

64-bit version:

Modify:
HKEY_LOCAL_MACHINE \ SOFTWARE \ wow6432node \ Microsoft \ jet \ 4.0 \ engines \ Excel

Change the value of typeguessrows from 8 to 0.

 

Note:

1. typeguessrows is a global setting option that takes effect for all Excel files. It does not only affect SSIs, so you need to test the impact of modifications.

2. Modifying typeguessrows will enable EXCEL to scan all rows to determine the data type. If the Excel data volume is large, the performance may be affected.

 

For more information, see:

PRB: excelvalues returned as null using Dao openrecordset: http://support.microsoft.com/kb/194124

BUG: Excel ODBC driver will ignore firstrowhasnames or header settings: http://support.microsoft.com/kb/288343/cn