Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

getting null values while imporing from excel Expand / Collapse
Author
Message
Posted Thursday, July 3, 2014 11:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:26 PM
Points: 8, Visits: 116
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.
Post #1589195
Posted Friday, July 4, 2014 12:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1589200
Posted Sunday, July 6, 2014 10:29 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:40 PM
Points: 3,062, Visits: 2,673
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.



Post #1589726
Posted Tuesday, July 8, 2014 1:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:26 PM
Points: 8, Visits: 116
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.
Post #1590203
Posted Tuesday, July 8, 2014 1:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1590207
Posted Tuesday, July 8, 2014 1:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:26 PM
Points: 8, Visits: 116
this is the message i got in catch block...
Post #1590213
Posted Tuesday, July 8, 2014 1:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1590217
Posted Tuesday, July 8, 2014 2:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:26 PM
Points: 8, Visits: 116
error message attachment

  Post Attachments 
err.jpg (14 views, 9.32 KB)
Post #1590220
Posted Tuesday, July 8, 2014 2:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1590223
Posted Tuesday, July 8, 2014 2:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 6,864, Visits: 14,165
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
Post #1590228
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse