Read data from Excel and while inserting getting data type error

  • Hi,

    I am reading data from excel sheet and inserting in to temp table something like this

    SELECT * into Temp_table

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=C:\PB.xlsx',

    'SELECT * FROM [Sheet1$]')

    In this,we have particular column "Obejct ID".

    In Object ID column starting few rows will have integer data(example-100,200 and 300) and few rows will have alphanumeric data (5000-55526-6563).

    when temp table is created this column Object ID would have created with data type INT.

    so when it tries to insert alphanumeric data for the upcoming rows it will throw error data type mismatch.

    How can i overcome this problem?

    Please Note:Since we want to make this extraction and loading dynamic,data type might change from excel to excel and table should be created based on the data type we have in excel everytime.

  • Usually the first 10 rows of the file are used to decide which type the column is. So if you always use the first row as a dummy with an alphanumeric value, you never go wrong here.

    Let me know if that will work for you.

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • Yep,you are right.

    But in that case, if first 10 rows are integer then we might have to hardcode some sample values in the first row so that table will be created with the desired data types.

    any other alternative way to overcome this hardcode....??

  • Yes there is. You can set the TypeGuessRows registry property to change the amount of sample rows.

    (the default is 8 by the way, not 10).

    More information:

    What’s the deal with Excel & SSIS?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • There is also another way to change the way the Excel drivers 'determine' the datatype of a column and that is to use the IMEX=1 switch on the connections string.

    I dont know if you can use it in an OPENROWSET command or the impact it will have in regards to all other columns on the temp table, as I've only used it in DTS/SSIS in the past.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Hi,

    There is also another way to change the way the Excel drivers 'determine' the datatype of a column and that is to use the IMEX=1 switch on the connections string.

    Could you please brief this??

  • subramani.rudrappa 78855 (11/30/2012)


    Hi,

    There is also another way to change the way the Excel drivers 'determine' the datatype of a column and that is to use the IMEX=1 switch on the connections string.

    Could you please brief this??

    Did you read the article I linked to?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hi,

    sorry i did not go through the link....

    i just checked now,it is providing good info.I will try that and let you know the results...

  • Hi Koen,

    as per your suggestion,setting the property of excel while reading the data worked fine

    Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

    thank you!!!!

  • Great, glad you got it solved.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply