Import data from an Excel File into a dataTable but DataTable return empty column why?
In this article I am going to explain, how to solve the issue of generating empty coulumn in DataTable while importing data from Excel File. I will be solving this error using JET engine's additional property called IMEX .
Description
In our project many times we have to import data from excel into data table, recently I got one issue while importing data fro Excel to DataTable, I was not able to upload data of a particular column values into data table.
For example I have excel data like below screen shot.
In that above excel data first few row values only are empty in the salary column. Whenever I read this excel data into data table it return whole column value its empty because first few rows of salary column is empty then it take whole column is empty even data are present in the middle rows take this data table screen shot
Reason was that issue OLEDB Driver is misunderstood column data type based on the value present in that column. You can use IMEX properties to define that field types may be text.
Here in this code snippet I have add additional Extended property to denote Excel column values contain mixed of text, number etc.
OleDbConnection MyConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Exfilepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
After using IMEX property now check the output of datatable, it returns correct column all values see below screen shot output. 
What is IMEX property? Why we need to use in connection string?
IMEX property is used to tells the driver to always read "intermixed" data from the excel sheet like numbers, dates, strings etc data are present in that column. So take that column data as text during import data from excel.Full Source Code
using System.Data.OleDb;
using System.IO;
public partial class CSV_Test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
//Import data From Excel
ImportFromExcel();
}
void ImportFromExcel()
{
//Assign Excel path here
string Exfilepath = Server.MapPath("sample.xls");
//Assign conncetion string
//Without IMEX property connection string if you use this connection string then salary column becomes empty
//OleDbConnection MyConnection = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + Exfilepath + "';Extended Properties=Excel 8.0;");
//Using IMEX property to get data from excel
OleDbConnection MyConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Exfilepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';");
DataSet DtSet = null;
DataTable dt = new DataTable();
OleDbDataAdapter MyCommand = null;
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dt = DtSet.Tables[0];
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
Source Code
Here I have attached full source code for the same download it and test it.
Client Side : ASP.NET
Code Behind : C#Conclusion
I hope this article is solve your Import data from Excel empty column error issue.