showing Wrong Data while loading excel sheet, need help urgent
Hi, I am trying to load data from an excel sheet to a table in sql server. in the excel-source when i press preview button data for one column in the excel sheet is coming as null. the column has numbers and the format of the cell is 'general'..? what could be wrong and how could i resolve.? need help urgently..?
October 29th, 2010 1:49pm
in the excel connection manager properties, in the connection string add IMEX=1 like this: http://swap.wordpress.com/2007/09/18/solution-for-excel-data-uploading-problem-using-ssis/http://www.rad.pasfu.com
October 29th, 2010 1:57pm
something like Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\SampleExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=Yes; IMEX=1;" Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
October 29th, 2010 5:02pm
Hi, I tried by putting IMEX=1 in the connectionstring, but the result is same. my connection string is same as Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\SampleExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=Yes; IMEX=1;" take a look at the below image, it is a screen shot of the excel file in which data is wrongly read. http://i55.tinypic.com/261272r.jpg in the above picture in column 'C', I have highlighted few rows with yello color. these rows have value '8300200' for column-C. And the rows above in the picture have value '9603000' for column-C. there are many rows which have different values for column-C. every row is read perfectly except the rows which have value '8300200' for column-C, for these rows value for column-C is coming as NULL. I have tried by putting IMEX=1 in the connection sting still problem is not solved. And one more thing i want to point out is, the excelsheet is sorted before taking the screenshot with respect to column-C.as you can see in picture the value '8300200' is coming after the value '9603000', this point may help you a bit. kindly let me know if you are able to solve this problem.
November 15th, 2010 3:38am
try this article and I think you will find an appropriate method there: http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/62697/http://www.rad.pasfu.com
November 15th, 2010 4:21am
Hi Reza, Thank you very much. link that you posted has a method that worked for me. can you tell me what is IMEX=1 and when to use it. and thanks to all who helped me.
November 15th, 2010 5:44am
Glad to help, Usually Excel GUESS type of columns at the time of IMPORT, by configuring IMEX=1 , excel doesn't guess it anymore and import the field as it be. for example ABC123 will import as NULL usually, but if you set IMEX=1, then it will import as ABC123. http://www.rad.pasfu.com
November 15th, 2010 6:20am