getting null values while imporing from excel

Question

getting null values while imporing from excel

vissu

SSC Veteran

Points: 258
More actions
July 3, 2014 at 11:12 pm

#290686

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.

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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: 29389 More actions · Answer 2

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 More actions · Answer 3

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: 259115 More actions · Answer 4

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 More actions · Answer 5

vissu

SSC Veteran

Points: 258

July 8, 2014 at 1:46 am

#1727656

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

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 More actions · Answer 7

vissu

SSC Veteran

Points: 258

July 8, 2014 at 2:01 am

#1727663

error message attachment

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

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: 186127 More actions · Answer 9

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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

vissu SSC Veteran Points: 258 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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.