Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Read data from Excel and while inserting getting data type error


Read data from Excel and while inserting getting data type error

Author
Message
subramani.rudrappa 78855
subramani.rudrappa 78855
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 116
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.
Ronald H
Ronald H
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1131 Visits: 612
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
subramani.rudrappa 78855
subramani.rudrappa 78855
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 116
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....??
Koen Verbeeck
Koen Verbeeck
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: 16455 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
subramani.rudrappa 78855
subramani.rudrappa 78855
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 116
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??
Koen Verbeeck
Koen Verbeeck
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: 16455 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
subramani.rudrappa 78855
subramani.rudrappa 78855
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

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