导入 .CSV 然后通过 SSIS 导出到 Access 2007 DB 会导致巨大的膨胀
我有一个 90MB 的 .csv 摘录，需要推送到 Access 2007 DB 中。
作为测试 - 如果我将具有默认设置的文件直接导入到新表中，我最终会得到 134MB 的数据库。
我需要做什么 - .csv 提取包含几列，我需要在将它们推送到 Access DB 之前对其进行处理。为了实现这一点，我使用 SSIS（来自 SQL Server 2008 安装）并使用几个派生列来包含处理后的列，然后通过使用以下连接字符串“Data Source=C:\Import\InTheGarden.accdb;Provider=Microsoft.ACE.OLEDB.12.0;”的 ADO.NET 连接。 （来自 SSIS 连接管理器中连接的连接字符串）
当我使用 SSIS 处理数据时，我最终得到一个 1.16GB 的文件，压缩后大小约为 180MB！
两件事： -
- 首先，我不明白是什么导致了肿胀以及我如何解决它。我读过我不知道今天有多少关于“Access 2007 Bloat”的文章，并且似乎找不到任何可以说明到底发生了什么的内容:(
- 其次，虽然我可以导入上面的 .csv 文件，但我还有另一个需要导入 154MB 的文本文件，并且考虑到较小的文件导致数据库在导入时膨胀到 1.16GB，我对这个不超过 2GB 限制的较大文件不抱太大希望，
我通常会使用 SQL Server 作为 ！后端，但有一些我无法控制的原因导致这种情况不会发生:(
很高兴提供更多信息，Access不是我使用负载的东西，所以可能有我错过的关键信息！！:(
谢谢！
I've got a 90MB .csv extract I need to push into an Access 2007 DB.
AS A TEST - If I do a straight import of the file with default settings into a new table I end up with a DB of 134MB.
WHAT I NEED TO DO - The .csv extract contains a couple of columns I need to process before pushing them to into the Access DB. To accomplish this I am using SSIS (from SQL Server 2008 install) and using a couple of derived columns to contain the processed columns which then all get pushed into an existing Access table (which has no rows at the start of the process) via an ADO.NET Connection using the following connection string "Data Source=C:\Import\InTheGarden.accdb;Provider=Microsoft.ACE.OLEDB.12.0;". (Connection String from Connection in Connection Manager in SSIS)
When I process the data using SSIS I end up with a file of 1.16GB which when compacted comes down to a size of approximately 180MB!!!
Two things: -
- Firstly I don't understand what is causing the bloat and how I might get around to it. I've read I don't know how many articles today on "Access 2007 Bloat" and can't seem to find anything that exlpains what exactly is going on:(
- Secondly, whilst I can import the .csv file above I have another text file of 154MB which needs importing and given that the smaller file caused the DB to bloat to 1.16GB on import I'm not holding out much hope of success with this bigger file not exceeding the 2GB limit!
I would normally use SQL Server as a back end but there are reasons beyond my control as to why this can't happen:(
Happy to provide further information, Access is not something I use loads so there may be key information which I've missed!! :(
Thx!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
为什么不在 Access 中使用临时表来进行导入、处理数据，然后插入到真实表中？您可以为此使用单独的临时数据库，以避免导入两次造成的臃肿。我已经对 Access 进行了数十次导入，当我需要在最终追加之前处理数据时，这一直是我的做法。
Why not use a staging table in Access to do the import, process the data, and then insert to the real table? You'd use a separate temp database for that so as to avoid the bloat of importing twice. I've done dozens of imports into Access and this has always been the way I do it when I need to process the data before the final append.
尝试将 CSV 分成几个较小的文件，并在导入每个文件后运行 Access 的“压缩数据库”命令。
Try breaking the CSV into several smaller files and running Access' Compact Database command after importing each one.