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

问题描述

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

因此，我决定以此帖子（也在stackoverflow上）
不幸的是，列数限制为255 这是我调用 AdoRecordset.Fields.count 方法时获得的数字。

在stackoverflow中发帖：

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


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

没有人回答了第一个问题，我想知道是否可能有第二篇文章中描述的解决方案 - 我想避免安装额外的

1. 有一种方法可以打开一个csv-File作为ADO-recordset，它有超过255个可用字段/列 - 我需要大约3000列和10000行。


2. 如果不是这种情况，是否有任何其他方法读取csv文件而不实际打开它（如果文件很大，则需要几年时间）？

csv文件不是必须的，我可以将数据转换为任何必要的格式。访问将不工作，我有3000列和10000行和MS Access无法处理3000列。是否可能有一个文件类型可以更容易，更快地处理？ （在使用ExcelVBA打开和阅读方面）

这是必须经常出现的问题我想知道为什么在网络上找不到解决方案。 / strong>

解决方案

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

  Dim FileNum As Integer 
 Dim DataLine As String 
 Dim SplitData（）
 
 
 FileNum = FreeFile b $ b打开Filename输入为#FileNum 
 
而不是EOF（FileNum）
行输入#Filename，DataLine'每次读取数据1条记录
 SplitData = Split（DataLine，，）
'处理大数组
 Wend 
  

每行读取您的数据将会进入 field1 SplitData（0）到 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屋！