getting null values while imporing from excel

  • vissu

    SSC Veteran

    Points: 258

    hi,

    I am using following query...

    SELECT *

    FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0;DATABASE=E:\EXCELFILES\EXCEL_2014.xls',

    'Select * from [sheet1$]')

    in my sheet one column contains numeric values, but some of the rows are string. when importing this data numeric values coming properly but string values imported as nulls.

    ex: value

    --------

    10

    15

    20

    str

    25

    imported as

    value

    --------

    10

    15

    20

    NULL

    25

    can any one have idea on this....

    thankyou.

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    This is one of the most common issues with Excel.

    Did you try google because there are literally hundreds of blog posts, articles and forum threads that describe this issue.

    Here is one of them:

    What’s the deal with Excel & SSIS?[/url]

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

  • happycat59

    One Orange Chip

    Points: 29186

    visu.viswanath (7/3/2014)


    hi,

    I am using following query...

    SELECT *

    FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0;DATABASE=E:\EXCELFILES\EXCEL_2014.xls',

    'Select * from [sheet1$]')

    in my sheet one column contains numeric values, but some of the rows are string. when importing this data numeric values coming properly but string values imported as nulls.

    ex: value

    --------

    10

    15

    20

    str

    25

    imported as

    value

    --------

    10

    15

    20

    NULL

    25

    can any one have idea on this....

    thankyou.

    This sort of thing happens because data inside Excel is untyped. When you try to read a spreadsheet using SSIS, the OLEDB provider attempts to turned untyped data into typed data. To do this it reads the first "Few" rows (configurable by updated the registry on each machine that will execute the SSIS package) and based on what is in each column, it will make a decision about the datatype that is applied to the entire column. So, if the first few columns contain numbers, it may determine that the appropriate data type is a 4 byte integer. This metadata is then given to SSIS. If SSIS is happy with it (i.e. the meta data is the same as when the package was designed), then you can proceed to the next step and read all of the data in the spreadsheet. When it encounters the value "str", instead of throwing an exception, the OLE DB provider simply returns NULL.

    To change the number of rows used, search the registry for "TypeGuessRows". From memory the default value is 8. AND, there may be several entries - and not all apply to excel (the name of the registry key is pretty obvious, though)

    You can change the connection string so that the OLE DB provider treats everything as text. Have a look at https://www.connectionstrings.com/excel/ - this gives more info on this.

  • vissu

    SSC Veteran

    Points: 258

    Thanks for replay, i have one more doubt ...

    Is there any limitation for file size through query not from SSIS package.

    it gives error message as

    "server is not responding to dump the excel file" for 2.5 MB file and it works for 1.5 Mb file.

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    visu.viswanath (7/8/2014)


    Thanks for replay, i have one more doubt ...

    Is there any limitation for file size through query not from SSIS package.

    it gives error message as

    "server is not responding to dump the excel file" for 2.5 MB file and it works for 1.5 Mb file.

    Is that the actual error message?

    The only google result for the error message is this exact post.

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

  • vissu

    SSC Veteran

    Points: 258

    this is the message i got in catch block...

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    visu.viswanath (7/8/2014)


    this is the message i got in catch block...

    Can you post a screenshot?

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

  • vissu

    SSC Veteran

    Points: 258

    error message attachment

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Where do you get this error? In Excel itself?

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

  • ChrisM@Work

    SSC Guru

    Points: 186054

    visu.viswanath (7/8/2014)


    Thanks for replay, i have one more doubt ...

    Is there any limitation for file size through query not from SSIS package.

    it gives error message as

    "server is not responding to dump the excel file" for 2.5 MB file and it works for 1.5 Mb file.

    It doesn't look like an MS error message. What tool are you using to run your TSQL OPENROWSET script?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • vissu

    SSC Veteran

    Points: 258

    actually one procedure is there for validating(checking for sheet names and for columns in sheet) excel and import data by using OPENROWSET('Microsoft.Ace.OLEDB.12.0').

    and it split the data into multiple tables.

    this procedure is invoked by .net application, in application i got this error message.

    and now i tried from sql server management studio, it runs properly

    but from application it throughs error messge...

  • vissu

    SSC Veteran

    Points: 258

    one procedure is there for validating ,dumping and to split the data into multiple tables using OPENROWSET('Microsoft.Ace.OLEDB.12.0').

    here validating means checks for sheet names, columns in sheet or correct or not.

    when this procedure invoked from .net application it gives error

    now i tried from sql server management studio it works properly, but from application it gives error.

  • vissu

    SSC Veteran

    Points: 258

    one procedure is there for validating ,dumping and to split the data into multiple tables using OPENROWSET('Microsoft.Ace.OLEDB.12.0').

    here validating means checks for sheet names, columns in sheet or correct or not.

    when this procedure invoked from .net application it gives error

    now i tried from sql server management studio it works properly, but from application it gives error.

  • vissu

    SSC Veteran

    Points: 258

    one procedure is there for validating ,dumping and to split the data into multiple tables using OPENROWSET('Microsoft.Ace.OLEDB.12.0').

    here validating means checks for sheet names, columns in sheet or correct or not.

    when this procedure invoked from .net application it gives error

    now i tried from sql server management studio it works properly, but from application it gives error.

Viewing 14 posts - 1 through 14 (of 14 total)

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