2

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.

Community
  • 1
  • 1
Andrey Lujankin
  • 479
  • 1
  • 7
  • 15
  • My understanding of the answer to the first/third question you posted is that you are *not* required to install any new software... ADO and DAO are both built-in options. – Gaffi Jun 25 '12 at 13:20
  • Hmm okey perhaps I should edit the question - I was wondering whether there is a way to cicumvent the 255-fields limitation of an ADO-Recordset. If I open an excel File as an ADO-recordset it seems that it only has 255 possible fields - I need 3000. – Andrey Lujankin Jun 25 '12 at 14:30
  • Does adding a [`schema.ini`](http://msdn.microsoft.com/en-us/library/ms709353%28VS.85%29.aspx) file make a difference? – Zev Spitz Dec 04 '12 at 10:27
  • @ Zev Spitz I have never worked with scheme.ini - files I will look into it. – Andrey Lujankin Dec 05 '12 at 17:17

1 Answers1

2

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)

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • Don't I need to demeinsify the array or does VBA take care of it on its own? Do I need to reference any kind of package? (like with ADO) – Andrey Lujankin Jun 25 '12 at 18:11
  • By the way - this is exactly what I have been looking for. The 3000 stems from the fact hat I have to process random-numbers. Stock data on a daily basis for about 10 years and this I have 10000 times - monte carlo as usual. I usually prefer using sefl generated randoms but in this case I must work with a huge csv-file :( – Andrey Lujankin Jun 25 '12 at 18:14
  • 1
    the use of `Open` and `Line Input` dates all the way back to the first incarnations of basic - no more is required. `Split` will dimension the array to however many records it finds - `UBound(SplitData)` will tell you how big the array is – SeanC Jun 25 '12 at 18:15
  • interesting thank you very much. I would "upvote" your answer but I do noat have enough reputation to do that :( – Andrey Lujankin Jun 25 '12 at 18:27
  • But I would have to write Split(DataLine, ";") if my file is ";" -delimted? – Andrey Lujankin Jun 25 '12 at 18:29
  • 1
    correct - change the delimiter to however your data is formatted - you can only use one delimiter, so ",;" wouldn't split on `,` and `;`, but would expect them both in sequence – SeanC Jun 25 '12 at 19:06