Click here to Skip to main content
15,881,757 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I have a requirement to import excel data into a datatable and validate the same with table in sql server. I have problem with importing data, it takes more time, is there any way to decrease the execution time. i am using excel reader dll..
Only open office is installed in my machine and it only supports .xlsx files..
I want to speed up execution time with available resources.. please suggest me an alternative.

C#
FileStream stream = File.Open(strTempPath, FileMode.Open, FileAccess.Read); 
if (Path.GetExtension(strTempPath).Equals(".xls")) 
    excelReader = ExcelReaderFactory.CreateBinaryReader(stream); // Reading from a binary Excel file ('97-2003 format; *.xls) 
else 
    excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //Reading from a OpenXml Excel file (2007 format; *.xlsx) 
excelReader.IsFirstRowAsColumnNames = true; 
//DataTable tblSchema = (new BusinessFacade.CommonSystem()).GetTableSchema(strTableName); 

DataTable result = new DataTable(); 
//result = tblSchema.Clone(); 
result = excelReader.AsDataSet().Tables[0]; 
excelReader.Close(); 
var dateColumns = from DataColumn d in result.Columns where d.DataType == typeof(DateTime) select d.Ordinal + 1; 
return result;
Posted
Updated 18-Sep-12 0:20am
v3
Comments
Kuthuparakkal 18-Sep-12 5:54am    
plz post code
[no name] 18-Sep-12 6:05am    
FileStream stream = File.Open(strTempPath, FileMode.Open, FileAccess.Read);

if (Path.GetExtension(strTempPath).Equals(".xls"))
excelReader = ExcelReaderFactory.CreateBinaryReader(stream); // Reading from a binary Excel file ('97-2003 format; *.xls)
else
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //Reading from a OpenXml Excel file (2007 format; *.xlsx)

excelReader.IsFirstRowAsColumnNames = true;

//DataTable tblSchema = (new BusinessFacade.CommonSystem()).GetTableSchema(strTableName);

DataTable result = new DataTable();
//result = tblSchema.Clone();
result = excelReader.AsDataSet().Tables[0];
excelReader.Close();
var dateColumns = from DataColumn d in result.Columns
where d.DataType == typeof(DateTime)
select d.Ordinal + 1;

return result;

1 solution

You could try to use Microsoft Access Database Engine 2010 Redistributable[^]

With that you can create ODBC connection to Excel file to read and query it.
It's also useful for importing data from Excel to SQL database.
 
Share this answer
 
Comments
[no name] 18-Sep-12 6:50am    
Thank you for your suggestion.... but......
I am looking for dll sort of file.... and i have date conversion issue too

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900