VBA: How to efficiently read and process large CSV files line by line without exhausting memory?
I'm stuck trying to I'm trying to figure out I'm following best practices but I'm working on a VBA project where I need to read a large CSV file (around 100,000 rows) into Excel for data analysis. However, when I try to load the entire file directly into an array, Excel throws a memory behavior due to the size. I've tried using `Workbooks.Open` and reading the data into a range, but it still leads to performance optimization and crashes when the file is too large. Here's a snippet of the code I used: ```vba Dim wb As Workbook Set wb = Workbooks.Open("C:\path\to\largefile.csv") Dim data As Variant data = wb.Sheets(1).UsedRange.Value wb.Close False ``` I considered using `FileSystemObject` to read the file line by line, but I am unsure how to effectively parse the data and store it in a more manageable format. Also, I want to avoid loading everything into memory at once. Is there a recommended approach or best practice for processing large CSV files in VBA? Any insights into handling edge cases, like inconsistent row lengths or special characters, would also be helpful. I've tried the FSO approach but ran into issues with parsing the lines correctly due to varying delimiters. Here’s a basic example of what I attempted: ```vba Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.OpenTextFile("C:\path\to\largefile.csv", 1) Do While Not ts.AtEndOfStream Dim line As String line = ts.ReadLine ' Parsing logic goes here Loop ts.Close ``` I'm getting errors when trying to split the lines that have inconsistent columns. Any advice on how to efficiently handle this scenario would be greatly appreciated! What's the best practice here? How would you solve this? This is for a application running on CentOS. Thanks, I really appreciate it! The stack includes Vba and several other technologies.