0

I have a CSV file that exceeds the Excel raster (6 columns and over 1.500.000 rows). With a VBA code, I want to rearrange the data within that CSV and place it in an Excel sheet in a more convenient form. The issue is when I converted the CSV into a XLSX with Macros, the end result doesn't include all the data (makes sense since XLSX didn't load all the data from the CSV). I've tried to split the CSV data set into two subsets but I still get a memory issue when running the VBA-code. I can go ahead and split the CSV into more smaller subsets, but I was wondering if there is a more efficient way to solve the problem. E.g. would that be better to open the CSV in MS Access, run the VBA from there, and then create an Excel sheet as output of the VBA?

Any help?

Thank you!

The VBA-code (where Out_VBA is the sheet with the end result):

Sub looparray() 
Dim MyWorkbook As Workbook
Set MyWorkBook = ThisWorkbook
Dim MyTable As Worksheet
Set MyTable = MyWorkbook.Sheets("MyTable") 
Dim tact As Long
Dim station As Long
Dim oarray As Variant
Dim rw As Long 
tact = 1
station = 1 
oarray = MyTable.Cells(2, 1).CurrentRegion 
For rw = 2 To UBound(oarray) 
tact = MyTable.Cells(rw, 4)    
station = MyTable.Cells(rw, 2)    
If oarray(rw, 5) > 0 Then    
Out_VBA.Cells(tact, station) = oarray(rw, 1) & oarray(rw, 3) & oarray(rw, 5) & "+" & oarray(rw, 6)    
End If
Next 
End Sub
TheWayfarer
  • 1
  • 2
  • There are other programs that deal with large datasets much better than Excel, we used SAS. – Solar Mike May 21 '22 at 21:07
  • If you absolutely *must* use VBA, open the CSV file for reading, and count until you have reached desired split line count. Then continue from there. The forward cursor (read-next-line) should not be limited by file count. – MyICQ May 21 '22 at 21:11
  • Doing that through VBA sounds awfully cumbersome - you could easily take in 1.5m rows through PowerQuery or, for a more retro solution, use the not-often used on Windows [gnumeric](https://portableapps.com/apps/office/gnumeric_portable) spreadsheet, which has the interesting feature whereby a worksheet can be resized to up to 16m rows – Spectral Instance May 21 '22 at 21:18
  • The issue is, I have 6 columns and out of the 6 I want to create a 2-dimensional matrix. The matrix should be filled in with data from the remaining 4 columns under a certain condition. Can PowerQuery create such a matrix? Thanks. – TheWayfarer May 21 '22 at 21:27
  • I'm not a PowerQuery expert but, even if I were, I wouldn't be able to answer what is probably a not-so-simple problem condensed into a single sentence - you would be better off posting a new PowerQuery-tagged question, with sufficient detail that an expert could properly evaluate it. – Spectral Instance May 21 '22 at 21:32
  • This is easily done by reading the csv file line by line into memory, and loading it into an array. From there you can place it onto sheets as you see fit. [An example](https://stackoverflow.com/a/11528932) – chris neilsen May 22 '22 at 00:10
  • Could you share the code you've got that doesn't consider the size (number of rows)?What does *"... data from the remaining 4 columns under a certain condition"* mean? – VBasic2008 May 22 '22 at 04:45
  • @VBasic2008 To answer your question: Let's say I have 6 columns: A, B, C, D, E, F E.g. for A=1 and B=1 the value of C is different from zero then the matrix celle (1,1) receives that value of C + value of D, E, and F. – TheWayfarer May 22 '22 at 13:18
  • @chrisneilsen Is the output of DataLine an array? – TheWayfarer May 22 '22 at 13:23
  • @TheWayfarer no, you'll need to split the lines as you read them – chris neilsen May 22 '22 at 19:14

0 Answers0