在C＃中从excel读取批量数据 [英] Read bulk data from excel in C#
问题描述
我正在开展一些迁移项目，其中数据从Oracle DB导出到Excel工作表。提取工具不同，它能够将数据提取到Excel大约1百万。在提取工具Oledb Data reader正在使用，它正在成功提取。即使它在同一个excel中提取超过1M的数据以及多个工作表，如Sheet1，Sheet2等。我需要读取整个excel数据并将其存储在Data表中。它适用于低于6的lac数据。但是当我尝试使用OleDb数据适配器或数据读取器从Excel读取数据时，如果记录超过8 Lac并且出现超出系统资源等错误，则会失败。我的服务器是免费的，没有任何东西正在运行和它的64 GB内存。
找到下面的代码。
尝试使用数据适配器和数据读取器。
使用（var cmd = conn.CreateCommand（））
{
cmd.CommandText =SELECT * FROM [+ sheet +];
// var adapter = new OleDbDataAdapter（）;
//adapter.SelectCommand = cmd;
OleDbDataReader reader = cmd.ExecuteReader（）;
if（sheet.Contains（DocSheet））
{
//adapter.Fill(dDDocExcelData）;
dtDocExcelData.Load（读者）;
LogWriter.LogWrite（No of Files / docs to fo fo文件：+ inputFilePath +Sheet Rows Count：+ dtDocExcelData.Rows.Count;
}
else if（ sheet.Contains（FileSheet））
{
//adapter.Fill(dtFileExcelData）;
dtFileExcelData.Load（reader）;
LogWriter.LogWrite（文件处理文件数/文件数：+ inputFilePath +Sheet Rows Count：+ dtFileExcelData.Rows.Count）;
}
//清除资源
cmd.ResetCommandTimeout（）;
cmd.Dispose（）;
reader.Dispose（）;
}
visual studio是2013版本，代码是C＃。 Excel版本也是最新的2016（.XLSX）。
请检查上面的代码部分，并告诉我哪里没有从excel加载批量数据。有没有其他方法可以解决这个问题。
当我用谷歌搜索它时，一个选项是补丁更新，但客户端可能还没有为补丁更新做好准备。为我提供任何其他替代解决方案。项目将在月底前投入使用。 UAT是阶段失败。请尽快给我替代解决方案。
等待您的宝贵建议。提前致谢。
问候，
Venkat
我尝试过：
需要读取Excel到数据表的批量数据。
Hi,
I am working on some migration project where data is exported from Oracle DB to Excel sheets. Extraction tool is different which is able to extract data to Excel around 1 Million also. In extraction tool Oledb Data reader is using which is extracting successfully. Even its extracting more than 1 M data as well in to multiple sheets in the same excel like Sheet1,Sheet2 etc. I need to read the entire excel data and store it in Data table. its working fine for below 6 lac data. But when i am trying to read the data from Excel using OleDb Data Adapter or Data Reader its failing when records exceed 8 Lac and giving error like "System Resources exceeded." My server is free and nothing is running ther and its 64 GB ram as well.
Find the code snap below.
tried with both Data Adapter and Data reader.
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [" + sheet + "]";
//var adapter = new OleDbDataAdapter();
//adapter.SelectCommand = cmd;
OleDbDataReader reader = cmd.ExecuteReader();
if (sheet.Contains(DocSheet))
{
//adapter.Fill(dtDocExcelData);
dtDocExcelData.Load(reader);
LogWriter.LogWrite("No of Files/docs to process for the File : " + inputFilePath + " Sheet Rows Count : " + dtDocExcelData.Rows.Count);
}
else if (sheet.Contains(FileSheet))
{
//adapter.Fill(dtFileExcelData);
dtFileExcelData.Load(reader);
LogWriter.LogWrite("No of Files/docs to process for the File : " + inputFilePath + " Sheet Rows Count : " + dtFileExcelData.Rows.Count);
}
//clearing the resources
cmd.ResetCommandTimeout();
cmd.Dispose();
reader.Dispose();
}
visual studio is 2013 version and code is in C#. Excel version is also latest as 2016 (.XLSX).
please check the above code part and let me know where it is failing to load the bulk data from excel. Is there any other way to resolve this.?
When i googled it, one option is patch update, but client may not be ready for patch update. Provide me any other alternative solutions to over come this. Project is going to live by month end. UAT is phase is failing. please give me alternate solution at earliest.
Awaiting for your valuable suggestions. Thanks in advance.
Regards,
Venkat
What I have tried:
Need to read the bulk data from Excel to Data table.
推荐答案
考虑尝试将大量数据写入两张纸上。你很快就会用完空间。
Think about trying to write that much data onto two pieces of paper. You will soon run out of space.
这篇关于在C＃中从excel读取批量数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
