November 29, 2012 at 5:00 am
Hi,
I am reading data from excel sheet and inserting in to temp table something like this
SELECT * into Temp_table
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\PB.xlsx',
'SELECT * FROM [Sheet1$]')
In this,we have particular column "Obejct ID".
In Object ID column starting few rows will have integer data(example-100,200 and 300) and few rows will have alphanumeric data (5000-55526-6563).
when temp table is created this column Object ID would have created with data type INT.
so when it tries to insert alphanumeric data for the upcoming rows it will throw error data type mismatch.
How can i overcome this problem?
Please Note:Since we want to make this extraction and loading dynamic,data type might change from excel to excel and table should be created based on the data type we have in excel everytime.
November 29, 2012 at 5:08 am
Usually the first 10 rows of the file are used to decide which type the column is. So if you always use the first row as a dummy with an alphanumeric value, you never go wrong here.
Let me know if that will work for you.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
November 29, 2012 at 8:23 pm
Yep,you are right.
But in that case, if first 10 rows are integer then we might have to hardcode some sample values in the first row so that table will be created with the desired data types.
any other alternative way to overcome this hardcode....??
November 30, 2012 at 12:27 am
Yes there is. You can set the TypeGuessRows registry property to change the amount of sample rows.
(the default is 8 by the way, not 10).
More information:
What’s the deal with Excel & SSIS?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 30, 2012 at 12:45 am
There is also another way to change the way the Excel drivers 'determine' the datatype of a column and that is to use the IMEX=1 switch on the connections string.
I dont know if you can use it in an OPENROWSET command or the impact it will have in regards to all other columns on the temp table, as I've only used it in DTS/SSIS in the past.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 30, 2012 at 3:57 am
Hi,
There is also another way to change the way the Excel drivers 'determine' the datatype of a column and that is to use the IMEX=1 switch on the connections string.
Could you please brief this??
November 30, 2012 at 3:57 am
subramani.rudrappa 78855 (11/30/2012)
Hi,There is also another way to change the way the Excel drivers 'determine' the datatype of a column and that is to use the IMEX=1 switch on the connections string.
Could you please brief this??
Did you read the article I linked to?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 30, 2012 at 4:34 am
Hi,
sorry i did not go through the link....
i just checked now,it is providing good info.I will try that and let you know the results...
December 2, 2012 at 11:49 pm
Hi Koen,
as per your suggestion,setting the property of excel while reading the data worked fine
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
thank you!!!!
December 3, 2012 at 1:13 am
Great, glad you got it solved.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply