如何在SSIS中将大Excel文件拆分为多个小文件？ [英] How to split a large Excel file into multiple small files in SSIS?
问题描述
我想读取一个250 Mb大，超过30万行的Excel文件。因此，当我尝试在SSIS的Excel源代码中处理它时，这花费了太多时间，最终导致处理失败。谁能告诉我如何将大文件拆分为小文件或任何其他有帮助的方法？
I want to read an Excel file which is 250 Mb large and has more than 300 000 rows. So when I try to process it in Excel source in SSIS it takes too much time and at last fails to process. Can anyone tell me how to split the big file into small ones or any other method that can help?
推荐答案
最好的方法是按块读取数据，我这样做是为了从SQL数据库读取巨大的表。但是要读取大的excel文件，我在C＃应用程序中而不是在SSIS中实现了它，因为它更复杂。我将提供一些链接以获取更多信息：
Best way is to read data by chunks, i have done this to read huge tables from SQL databases. But to read big excel files, i implemented this in a C# application not in SSIS since it is more complicated. I will provide some links for more info:
- 在执行前从Sqlite向SQL Server读取大量数据失败 （SQLite）
- SSIS无法保存软件包并重新启动Visual Studio （Oracle）
- 为数百万人优化SSIS软件包SQL命令中按/排序并合并联接的行数 （MySQL）
- 从db2获取前n至n行 （DB2）
- 尝试读取时出现OutOfMemoryException将大型Excel文件插入DataTable （Excel C＃）
- Reading Huge volume of data from Sqlite to SQL Server fails at pre-execute (SQLite)
- SSIS failing to save packages and reboots Visual Studio (Oracle)
- Optimizing SSIS package for millions of rows with Order by / sort in SQL command and Merge Join (MySQL)
- Getting top n to n rows from db2 (DB2)
- OutOfMemoryException while trying to read big Excel file into DataTable (Excel C#)
仅需执行此操作使用SSIS，稍后我可以提供类似于上面第一个链接（SQLite）中提供的详细答案。
If you need to do this only using SSIS, i can provide a detailed answer later similar to what i provided in the first link above (SQLite).
最近，我在Git-Hub上启动了一个新项目，该项目是使用C＃开发的类库。您可以使用它使用架构映射方法将Excel，word，powerpoint，文本，csv，html，json和xml中的表格数据导入具有不同架构定义的SQL Server表中。它提供了导入大型excel文件的功能。在以下位置查看：
Recently i started a new project on Git-Hub, which is a class library developed using C#. You can use it to import tabular data from excel, word , powerpoint, text, csv, html, json and xml into SQL server table with a different schema definition using schema mapping approach. It gives the ability to import big excel files. check it out at:
- SchemaMapper: C# Schema mapping class library
您可以按照此Wiki页面进行逐步操作指南：
You can follow this Wiki page for a step-by-step guide:
- Import data from multiple files into one SQL table step by step guide
这篇关于如何在SSIS中将大Excel文件拆分为多个小文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
