LOF function in Visual Basic suddenly not working

Question

I am doing some analysis on the US data customer credit card complaints database - see https://catalog.data.gov/dataset/consumer-complaint-database

Saving the file to a csv and running the following code leads to an error

Sub test()

Dim FolderPath as String
Dim strfilename as String
Dim strtextline as String

strfilename = "C:\Work\VB\complaints.csv"

Open strfilename For Input As #1

strFileContent = Input(LOF(iFile),1)

Close #1

End Sub

Runtime error '5' Invalid Procedure Call or Argument

Is this an error with Visual Basic on my installation? I'm running VBA on Excel with Microsoft Office Professional Plus 2019 with Windows 10.

Answer 1

The file is simply too large to read it in one chunk - its size is 1.5 GB and it holds more than 3 million rows.

You could rewrite your routine to read the file in chunks or row by row, but before you do so, you need to make up your mind what you want to do with the data. You cannot store them in Excel - it's maximum row number is a little bit above 1m (1'048'576 to be precise) and even with this limitation, it's nearly impossible to work. If Excel doesn't crash at all, it will be painfully slow.

My advice would be to store the data in a real database. There are a lot of free db systems around, eg SQL server, PostGres, MariaDB... You need of course learn about databases and SQL to handle that, but I don't see any alternative - Excel, as said, is not build and not capable to handle that amount of data.