从Excel表格导入到DataTable超过100k记录 [英] Import to DataTable from Excel Sheet above 100k records

问题描述

我需要将excel数据导入数据表，并使用sql server中的表对其进行验证。我有导入数据的问题，需要更多时间，有没有办法减少执行时间。我正在使用excel reader dll ..

我的机器上只安装了开放式办公室，它只支持.xlsx文件..

我希望加快执行时间资源..请给我一个替代方案。

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;

推荐答案

您可以尝试使用 Microsoft Access数据库引擎2010可再发行组件 [ ^ ]



使用它可以创建与Excel文件的ODBC连接来读取和查询它。

它也可以用于将数据从Excel导入到SQL数据库。

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.

这篇关于从Excel表格导入到DataTable超过100k记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！