database - Opening csv-File via ADO-connection - Column Limitation to 255 / or just use another file type?

Question

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.

Answer 1

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)