July 3, 2014 at 11:12 pm
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.
July 4, 2014 at 12:30 am
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
July 6, 2014 at 10:29 pm
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.
July 8, 2014 at 1:17 am
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.
July 8, 2014 at 1:29 am
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
July 8, 2014 at 1:46 am
this is the message i got in catch block...
July 8, 2014 at 1:50 am
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
July 8, 2014 at 2:01 am
error message attachment
July 8, 2014 at 2:11 am
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
July 8, 2014 at 2:18 am
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?
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
July 8, 2014 at 2:33 am
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...
July 8, 2014 at 2:38 am
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.
July 8, 2014 at 2:39 am
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.
July 8, 2014 at 2:43 am
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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply