通过ADO连接打开csv文件 - 列限制为255 /或仅使用其他文件类型？ [英] Opening csv-File via ADO-connection - Column Limitation to 255 / or just use another file type?

问题描述

我有一个相当大的csv-File（约800MB），我需要通过VBA从Excel文件访问（我正在使用Excel 2010）运行一些计算。现在我想避免直接打开Excel的文件，需要aeons来完成。

因此，我决定将其作为ADO-Recordset打开，在这篇文章（也在stackoverflow）
不幸的是，似乎列数限制为255 至少这是调用 AdoRecordset.Fields.count 方法时获得的数字。

我尝试搜索一些解决发布在stackoverflow中，发现：

1. 无法从csv文件传输超过255条记录以访问


2. 使用Microsoft Jet OLEDB的CSV列限制

没有人回答了第一个问题，我想知道是否可能有soluti除了第二篇文章中描述的内容外，我想避免安装其他软件。

我的问题是：

1. 是否有一种打开csv-file作为ADO记录集的方法，它具有超过255个可用字段/列 - 我需要大约3000列和10000行。


2. 如果不是这样，没有其他任何方式读取csv-File而不实际打开它（因为这需要多年，如果文件是巨大）？

csv文件不是强制性的，实际上我可以将数据转换为任何格式必要。访问将不起作用，因为我有3000列和10000行，MS Access不能处理3000列。是否有可能更容易和更快地处理的文件类型？ （使用ExcelVBA打开阅读）

这是一个常见的问题，我想知道为什么没有在网络上找到解决方案。 / strong>

解决方案

3000似乎很大，但有一个kludge：

  Dim FileNum As Integer 
 Dim DataLine As String 
 Dim SplitData（）
 
 
 FileNum = FreeFile（）
打开文件名输入为#FileNum 
 
虽然不是EOF（FileNum）
行输入#Filename，DataLine一次读取数据1个记录
 SplitData = Split（DataLine，，）
'处理大数组
 Wend 
  

读取数据的每一行都将进入 field1 SplitData（0） to field3000 SplitData（2999）（基于零的数组）

I have quite a large csv-File (about 800MB) which I need to access from an Excel File via VBA (I am using Excel 2010) to run some calculations. Now I would like to avoid opening the file directly for Excel needs aeons to accomplish that.

Thus, I decided to open it as an ADO-Recordset as desrcibed in this post (also on stackoverflow) Unfortunately it seems that the number of columns is limited to 255 At least this is the number I get when calling the AdoRecordset.Fields.count method.

I tried searching for some solved posts here in stackoverflow and found:

1. Can't transfer more than 255 records from a csv file to access
2. Column limitation on CSV using Microsoft Jet OLEDB

Nobody has anwered the first question yet and I was wondering whether there might be solutions other than described in the second post - I would like to avoid installing additional software if possible.

My Questions are:

1. is there a way to open a csv-File as an ADO-recordset which has more than 255 avaliable fields/columns - I need about 3000 columns and 10000 rows.
2. If this is not the case are there any other ways of reading a csv-File without actually opening it (for this takes years if the file is huge)?

The csv file is not obligatory and I could actually convert the data to any format necessary. Access won't work for I have 3000 columns and 10000 rows and MS Access can not handle 3000 columns. Is there perhaps a file type that can be handled easier and faster? (in terms of being opened and read with ExcelVBA)

This must be frequent problem I am wondering why there is no solution to be found on the web.

解决方案

3000 seems big, but there is a kludge:

Dim FileNum As Integer
Dim DataLine As String
Dim SplitData()


FileNum = FreeFile()
Open "Filename" For Input As #FileNum

While Not EOF(FileNum)
    Line Input #Filename, DataLine ' read in data 1 record at a time
    SplitData = Split(DataLine, ",")
    'Process big array
Wend

each row read of your data will be into field1SplitData(0) to field3000SplitData(2999) (zero based array)

这篇关于通过ADO连接打开csv文件 - 列限制为255 /或仅使用其他文件类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！