在VBA中处理大分隔的文本文件 [英] Handle large delimited text files in VBA
问题描述
使用VBA，我需要将当前位于分隔的文本文件（数百列乘以数万行）中的数据拆分为标准化形式（四列乘以百万行）;也就是说，结果表将包含对于每个单元格的列：
Using VBA, I need to "unpivot" data that is currently in delimited text files (as hundreds of columns by tens of thousands of rows) into a normalized form (four columns by millions of rows); that is, the resulting table will comprise columns that, for each cell:
- 标识原始表格/文件;
- 标识原始表格中的单元格行;
- 标识原始表格中的单元格列;
- identify the original table/file;
- identify the cell's row in the original table;
- identify the cell's column in the original table;
- contain the value of that cell.
我通常对任何有关如何有效完成这项任务的想法表示感谢。
I would generally be grateful for any thoughts on how one can efficiently accomplish this task.
到目前为止，我已经考虑使用ADODB构造一个构建输出表的 SELECT INTO ... UNION ... 查询，但是默认文本文件提供程序可悲地限制为255列（有没有哪些不是？）。
So far, I have considered using ADODB to construct a SELECT INTO ... UNION ... query that builds the output table, but the default text file providers are sadly limited to 255 columns (are there any which aren't?).
SébastienLorion构建了一个了不起的快速CSV阅读器，我很想使用，但我不知道如何使用它从内VBA - 感谢任何想法（我不认为它已经被编译为导出COM接口，我没有工具来重新编译它）。对于这一点，Microsoft还提供了一个 TextFieldParser 类，但是我不知道是否/如何从VBA中使用。
Sébastien Lorion has built a terrific Fast CSV Reader, which I would love to use, but I do not know how to use it from within VBA - grateful for any thoughts (I don't think it has been compiled to export COM interfaces, and I don't have the tools to recompile it). For that matter, Microsoft also provide a TextFieldParser class, but again I do not know if/how this can be used from VBA.
另一种方法可能是让Excel> = 2007打开源文件并构造输出表，但是直觉上感觉好像会导致相当大的浪费开销...
Another approach might be to have Excel >=2007 open the source file and construct the output table from there, but that intuitively 'feels' as though it will incur considerable wasted overhead...
推荐答案
在VB.NET中的 TextFieldParser 周围构建一个小的COM感知包装器。不理想，但最好的我现在可以想出来。
I decided to build a tiny COM-aware wrapper around TextFieldParser in VB.NET. Not ideal, but the best I can come up with at present.
这篇关于在VBA中处理大分隔的文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
