Click here to Skip to main content
12,633,764 members (30,997 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#
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.

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 18-Sep-12 0:45am
PBGuy1.8K
Updated 18-Sep-12 1:20am
sjelen9.5K
v3
Comments
Kuthuparakkal 18-Sep-12 5:54am
   
plz post code
shabari7 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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  
Comments
shabari7 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161208.2 | Last Updated 30 Sep 2015
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100