InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 19:54:07 GMT20http://www.sqlservercentral.com/Forums/Topic1390432-391-1.aspxGreat, glad you got it solved.Mon, 03 Dec 2012 01:13:26 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1390432-391-1.aspxHi Koen,as per your suggestion,setting the property of excel while reading the data worked fineProvider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";thank you!!!!Sun, 02 Dec 2012 23:49:01 GMTsubramani.rudrappa 78855http://www.sqlservercentral.com/Forums/Topic1390432-391-1.aspxHi,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...Fri, 30 Nov 2012 04:34:47 GMTsubramani.rudrappa 78855http://www.sqlservercentral.com/Forums/Topic1390432-391-1.aspx[quote][b]subramani.rudrappa 78855 (11/30/2012)[/b][hr]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??[/quote]Did you read the article I linked to?Fri, 30 Nov 2012 03:57:56 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1390432-391-1.aspxHi,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??Fri, 30 Nov 2012 03:57:00 GMTsubramani.rudrappa 78855http://www.sqlservercentral.com/Forums/Topic1390432-391-1.aspxThere 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. Fri, 30 Nov 2012 00:45:01 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1390432-391-1.aspxYes 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:[url=http://blogs.lessthandot.com/index.php/DataMgmt/ssis-1/what-s-the-deal-with]What’s the deal with Excel & SSIS?[/url]Fri, 30 Nov 2012 00:27:59 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1390432-391-1.aspxYep,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....??Thu, 29 Nov 2012 20:23:30 GMTsubramani.rudrappa 78855http://www.sqlservercentral.com/Forums/Topic1390432-391-1.aspxUsually 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.Thu, 29 Nov 2012 05:08:53 GMTRonald Hhttp://www.sqlservercentral.com/Forums/Topic1390432-391-1.aspxHi,I am reading data from excel sheet and inserting in to temp table something like thisSELECT * into Temp_tableFROM 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.Thu, 29 Nov 2012 05:00:18 GMTsubramani.rudrappa 78855