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

Read data from Excel and while inserting getting data type error Expand / Collapse
Author
Message
Posted Thursday, November 29, 2012 5:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 5:09 AM
Points: 45, Visits: 111
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.
Post #1390432
Posted Thursday, November 29, 2012 5:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,123, Visits: 603
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 Hensbergen

Help 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
Post #1390435
Posted Thursday, November 29, 2012 8:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 5:09 AM
Points: 45, Visits: 111
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....??
Post #1390974
Posted Friday, November 30, 2012 12:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
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?




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 #1391077
Posted Friday, November 30, 2012 12:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 953, Visits: 2,626
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
Post #1391083
Posted Friday, November 30, 2012 3:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 5:09 AM
Points: 45, Visits: 111
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??
Post #1391175
Posted Friday, November 30, 2012 3:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
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?




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 #1391177
Posted Friday, November 30, 2012 4:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 5:09 AM
Points: 45, Visits: 111
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...
Post #1391209
Posted Sunday, December 2, 2012 11:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 5:09 AM
Points: 45, Visits: 111
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!!!!

Post #1391782
Posted Monday, December 3, 2012 1:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
Great, glad you got it solved.



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 #1391795
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse