none
Excel to Table simple load - But has issues with 'data' - Please help!

    Question

  • Hi,
        I have a Excel Source. It has few columns. One of the column is  'EmployeeCode', which has values 6,7,w,1,a,c,5,y,6,6,4... and so on
    The destination is a table, column name and data type is 'EmpCode , varchar(20) for this column.

    Its a simple package. steps are ,  Excel Source--> Derived Column - (DT_STR,20,1252)LTRIM(RTRIM([EmployeeCode])) --> Destination table column 'EmpCode'.

    The problem is, if i execute the package,the values w,a,c and y are getting inserted as NULL. 
    I did a analysis, what i found is when coming from Excel , that column is coming as DT_R8 and NOT DT_WSTR. In the Advanced Editor -External Column , this column's datatype is DT_R8.

    In the Excel source 'Preview' itself, those values are coming as NULL not as w,a,c and y.

    If you guys help me to solve this problem - It would be really great!

    thanks in advance!


    • Edited by vskindia Thursday, November 14, 2013 6:00 AM edit
    Thursday, November 14, 2013 12:28 AM

Answers

  • This happens because excel drivers use first 8 rows to determine the data type.. 

    There are basically two ways to overcome this

    1: Make registry change

     You can use any good registry editor to increase the default 8 rows to 

    HKEY_LOCAL_MACHINE -> SOFTWARE -> Wow6432Node -> Microsoft -> Jet -> 4.0 ->Engines -> Excel -> change TypeGuessRows to 0 instead of 8.
     

    After making above changes now excel drivers will check 16384 rows to identify the correct data type.

    2: Change connection string for your excel connection manager

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Folder1\Book1.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1″;

    Hope this will help

    Thursday, November 14, 2013 12:11 PM

All replies

  • This happens because excel drivers use first 8 rows to determine the data type.. 

    There are basically two ways to overcome this

    1: Make registry change

     You can use any good registry editor to increase the default 8 rows to 

    HKEY_LOCAL_MACHINE -> SOFTWARE -> Wow6432Node -> Microsoft -> Jet -> 4.0 ->Engines -> Excel -> change TypeGuessRows to 0 instead of 8.
     

    After making above changes now excel drivers will check 16384 rows to identify the correct data type.

    2: Change connection string for your excel connection manager

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Folder1\Book1.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1″;

    Hope this will help

    Thursday, November 14, 2013 12:11 PM
  • Awesome Anuj! thanks a lot!
    Monday, November 18, 2013 4:40 AM