Sometimes it happens that you have substantial amount of data in an excel file and you need to fetch all the data into the database. it's a very labour task if you have to insert data into database manually one by one( just imagine what will happen if you have 1 million rows in an excel file.!!!!)
Below code snippet, written in C# language, will help you overcome these situation.
NOTE: This code snippet is written with consideration that the user has enough knowledge about ASP.Net, ADO.Net and Excel File
Explanation:
In above code snippet, i have used OleDBConnection and OleDBDataAdapter to connect and fetch data from excel file.
Let's understand the ConnectionString:
As you can observe, i have used Microsoft.Jet.OLEDB.4.0 as a Provider here. You can use this provider for excel files of Versions Excel 97 and Excel 2000, where as you can use Microsoft.ACE.OLEDB.12.0 as a provider for excel files of Excel 2003 and later versions.
The Datasource contains the physical location of the excel file you want to import.
Now the most important part of the connectionstring Extended Properties must be specified as Excel 8.0 or Excel 12.0 depending on the version of the MS Excel you are using.
HDR=Yes specifies that there is a header row in the cell range, so the provider will not include first row in the recordset.
IMEX=1 specifies the mixed datatypes are accepted as a TEXT in the cells.
Below code snippet, written in C# language, will help you overcome these situation.
NOTE: This code snippet is written with consideration that the user has enough knowledge about ASP.Net, ADO.Net and Excel File
DataSet ExcelDataset = new DataSet();
string filePath = @"D:\TestData.xls";
string sConnectionString = string.Empty;
sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
string filePath = @"D:\TestData.xls";
string sConnectionString = string.Empty;
sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=0\"";
try
{
using (OleDbConnection ExcelConnection = new OleDbConnection(sConnectionString))
{
ExcelConnection.Open();
using (OleDbDataAdapter ExcelDataAdapter = new OleDbDataAdapter("SELECT * FROM [Query$]", ExcelConnection))
{
ExcelDataAdapter.Fill(ExcelDataset);
}
}
}
Catch(Exception e)
{
try
{
using (OleDbConnection ExcelConnection = new OleDbConnection(sConnectionString))
{
ExcelConnection.Open();
using (OleDbDataAdapter ExcelDataAdapter = new OleDbDataAdapter("SELECT * FROM [Query$]", ExcelConnection))
{
ExcelDataAdapter.Fill(ExcelDataset);
}
}
}
Catch(Exception e)
{
throw;
}
}
finally
{
}
Explanation:
In above code snippet, i have used OleDBConnection and OleDBDataAdapter to connect and fetch data from excel file.
Let's understand the ConnectionString:
As you can observe, i have used Microsoft.Jet.OLEDB.4.0 as a Provider here. You can use this provider for excel files of Versions Excel 97 and Excel 2000, where as you can use Microsoft.ACE.OLEDB.12.0 as a provider for excel files of Excel 2003 and later versions.
The Datasource contains the physical location of the excel file you want to import.
Now the most important part of the connectionstring Extended Properties must be specified as Excel 8.0 or Excel 12.0 depending on the version of the MS Excel you are using.
HDR=Yes specifies that there is a header row in the cell range, so the provider will not include first row in the recordset.
IMEX=1 specifies the mixed datatypes are accepted as a TEXT in the cells.
