uploading text and numeric data from an excel file shows NULL Value
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:
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.
Thanks for All.
Read more »
Posts
