uploading text and numeric data from an excel file shows NULL Value

Posted by Venkat | Labels: , ,

Good Morning to everyone

I was working with one of the project , where i had the task of uploading excel ie: Read the Excel data to Dataset ie: from DataSet I am going to store the each field value to DB, so the excel sheet may contain numbers ,or string ,.. by default the Excel sheet  accept  the data based on the first rows of excel sheet.

If the first row and first column will be string - so further the whole first rows and first columns accept strings, if there is any Numeric found  on the first rows and first columns it consider as NULL ie: Empty value , as the same for Numeric also so it may also contain alphanumeric on some cells.

So while we read the Excel sheet  these problems are arise. then we tried to select all cell ie: full excel sheet -> Right Click on the Excel sheet - > format Cells - > Choose String - > click ok .Then I upload this Excel sheet to Read the Data.

Why we change the Format cell  to String means if we set it as string it will accept as both numeric , strings , or alphanumeric - so i am going end it up by doing this.

But the problem arises again - it works that time correctly after few day the same problem occurs, then i do some research or googled and came up with a solution :

The short answer is "IMEX=1" 

You have to apply this on Connection String

To connect to an excel file within ADO.NET use the "System.Data.OleDb" provider  a connection string similar to:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="Sample.xls";Persist Security Info=False;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1" 

 Thanks for All. 

 

PayOffers.in