SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


getting null values while imporing from excel


getting null values while imporing from excel

Author
Message
vissu
vissu
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 120
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
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27365 Visits: 13268
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?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4740 Visits: 3218
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
vissu
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 120
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
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27365 Visits: 13268
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
vissu
vissu
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 120
this is the message i got in catch block...
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27365 Visits: 13268
visu.viswanath (7/8/2014)
this is the message i got in catch block...


Can you post a screenshot?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
vissu
vissu
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 120
error message attachment
Attachments
err.jpg (27 views, 9.00 KB)
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27365 Visits: 13268
Where do you get this error? In Excel itself?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16236 Visits: 19551
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search