Exporting a Large List to a CSV File Kills Kernel in 12.3?

Question

I noticed that Mathematica 12.3 (also 12.1) kills the kernel when I try to Export a large list to a CSV file. No error messages, no warning, just a hard stop. Here is the code I wrote to replicate the issue:

rows = 100000000; (*MemoryInUse: 5.7GB, Expected CSV file size: 680MB*)
largeTable = Table[{0, 0, 0}, rows];
Export[NotebookDirectory[] ~~ "largeTable.csv", largeTable, "CSV"]

Any ideas on how this can be addressed?

Answer 1

You can also use numpy's csv writter to export large files, on my computer it is more than 10 times faster than the built in Export function for csv files.

The following implementation writes a binary file to disk in Mathematica, then reads it to python and then writes it to a csv file.

writeLargeTableToCSV[file_, data_] := 
 Module[{tmp, sess, convertBinaryToCSV},
  sess = StartExternalSession["Python"];
  ExternalEvaluate[sess, "import numpy"];
  
  convertBinaryToCSV = ExternalFunction[sess, "
def addtwo(in_file,size,out_file):
    d = numpy.fromfile(in_file,dtype=numpy.int32).reshape(*size)
    numpy.savetxt(out_file, d, fmt='%i', delimiter=',')"
    ];
  
  tmp = CreateFile[] <> ".bin";
  BinaryWrite[tmp, Flatten[largeTable], "Integer32"];
  Close[tmp];
  convertBinaryToCSV[tmp, Dimensions@data, file];
  DeleteObject[sess];
  file
  ]

Even it read/write three times, the performance is still more than 9 times faster.

rows = 1000000;(*smaller for benchmark*)
largeTable = Table[{k, 0, 0}, {k, rows}];

Export["largeTable.csv", largeTable, "CSV"] // AbsoluteTiming
(*{9.40056, "largeTable.csv"}*)

writeLargeTableToCSV["largeTable_from_python.csv",largeTable] // AbsoluteTiming
(*{1.06015, "largeTable_from_python.csv"}*)

The full data can be exported to csv in about 1.5 minutes.

rows = 100000000; largeTable = Table[{k, 0, 0}, {k, rows}];
writeLargeTableToCSV["largeTable_from_python.csv", largeTable] // AbsoluteTiming
{82.5153, "largeTable_from_python.csv"}

Note that int32 type is used so that the exported file is identical to that from builtin Export function. You need to change to float32 for floating numbers, but the performance is similar.

Answer 2

Based on the helpful comments from the community, I was able to solve the problem by writing the file in chunks. Here is the code with the adjustable chunk size. It worked on a small laptop with 8GB of RAM, even though it took 45 minutes.

rows = 100000000; (*MaxMemoryUsed: 5GB, Expected CSV file size: 1.3GB*)
largeTable = Table[{k, 0, 0}, {k, rows}];

Clear[ExportLargeTableToCSV]; 
ExportLargeTableToCSV[largeFileName_String, largeTable_List, 
  maxRowsPerChunk_Integer : 1000000] := Block[{largeFile, maxRows, i},
  
  If[FileExistsQ[largeFileName], DeleteFile[largeFileName];];
  largeFile = OpenAppend[largeFileName];
  maxRows = Min[Length[largeTable], maxRowsPerChunk];
  Print[ToString[Length[largeTable]] ~~ 
    " rows in the table. Chunk size:" ~~ ToString[maxRows] ~~ 
    " rows."];
  For[
   i = 1, i <= Length[largeTable], i += maxRows,
   Print["Saving chunk " ~~ ToString[Quotient[i, maxRows] + 1] ~~ 
     " of " ~~ 
     ToString[
      Quotient[Length[largeTable], maxRows] + 
       If[Mod[Length[largeTable], maxRows] == 0, 0, 1]] ~~ "..."];
   Export[largeFile, 
    largeTable[[i ;; Min[i + maxRows, Length[largeTable]]]], "CSV"];
   ];
  Close[largeFileName];
  Print["Done."];
  ];

ExportLargeTableToCSV[NotebookDirectory[] ~~ "largeTableChunks.csv", largeTable];