在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屋!