11
$\begingroup$

I have to provide data exports from Mathematica to CSV format and it is very slow compared to other formats as can be seen in the following table:

  • JSON, 15 sec, 8.4 MB
  • TXT, 23 sec, 5.6 MB
  • XLSX, 37 sec, 3.0 MB
  • CSV, 704 sec, 4.4 MB

Can somebody give me a hint on how to speed this up? Or an explanation why it is so slow?

Improve this question
$\endgroup$
4
  • 1
    $\begingroup$ Which mma version? Can you post an example of what you are doing? $\endgroup$
    – Ajasja
    Commented Apr 10, 2013 at 17:38
  • $\begingroup$ MMA 9.0.1 on Windows 7. $\endgroup$
    – Patrick Bernhard
    Commented Apr 11, 2013 at 7:10
  • $\begingroup$ Cant post the data since it is company data. $\endgroup$
    – Patrick Bernhard
    Commented Apr 11, 2013 at 7:12
  • 1
    $\begingroup$ I appreciate that you can't post the original data. But without "fake" data that reproduces the problem it's hard to give advice. $\endgroup$
    – Ajasja
    Commented Apr 11, 2013 at 12:28

2 Answers 2

Reset to default
12
$\begingroup$

Intermediate ExportString makes "CSV" exports much faster

I actually can reproduce this extraordinary slow CSV export on my two systems, on a "9.0 for Microsoft Windows (32-bit) (January 24, 2013)" even when I work locally (see below), and on a "9.0 for Mac OS X x86 (64-bit) (January 24, 2013)" when I work over a network share (not shown here):

t = RandomReal[{0, 1000}, {4000, 80}];

Export[FileNameJoin[{$TemporaryDirectory, "Matrix.CSV"}], t, "CSV"]

This may take eons. Let's see how long it takes:

{FileExtension@#, First@AbsoluteTiming[Export[#, t, FileExtension@#]],
     FileByteCount@#} & /@ {FileNameJoin[{$TemporaryDirectory, 
     "Matrix.CSV"}]} // Grid

CSV 159.78120 5815838

To me, this amazingly long time seems to be caused by laborious interactions with the file system, in particular I suspect every line of the CSV is being sent separately. That is why I propose a work around, where firstly all data is "CSV"ed using ExportString and secondly is exported using the simple "Table" formatting, which seems to work en bloc:

Export[FileNameJoin[{$TemporaryDirectory, "Matrix.CSV"}],  ExportString[t, "CSV"], "Table"]

This appears to work much faster, let's see how long it takes this time:

{FileExtension@#, 
    First@AbsoluteTiming[
      Export[#, ExportString[t, FileExtension@#], "Table"]], 
    FileByteCount@#} & /@ {FileNameJoin[{$TemporaryDirectory, 
     "Matrix.CSV"}]} // Grid

CSV 10.234375 5815838

About 16 times faster. Enjoy!

Edit

I made the same test on an ancient "7.0 for Mac OS X PowerPC (32-bit) (February 19, 2009)", interestingly here the direct "CSV" Export (to $TemporaryDirectory) is approx. 10% faster than my work around. Hmm. In contrary, on the abovementioned "9.0 for Mac OS X x86 (64-bit) (January 24, 2013)" system it is 30% faster. But for network shares my work around anyway is highly recommended for comma separated value exports!

Improve this answer
$\endgroup$
3
$\begingroup$

I can't reproduce this on my system (Win7-64, MMA v9):

t = RandomReal[{0, 1000}, {4000, 80}];

(
    fn = FileNameJoin[{$TemporaryDirectory, "Matrix." <> #}];
    {#, AbsoluteTiming[Export[fn, t];] // First, FileByteCount@fn}
) & /@ {"JSON", "TXT", "XLS", "CSV"} //Grid

enter image description here

(Note that I used "XLS" instead of "XLSX" as Export didn't seem to work for this matrix size)

Improve this answer
$\endgroup$
5
  • $\begingroup$ In[54]:= matrix = ToString[#] & /@ RandomReal[1, {1000, 100}]; AbsoluteTiming[ Export["C:\\Users\\Patrick\\Desktop\\export.json", matrix]][[1]] Out[77]= 0.733201 AbsoluteTiming[ Export["C:\\Users\\Patrick\\Desktop\\export.txt", matrix]][[1]] Out[78]= 0.124800 AbsoluteTiming[ Export["C:\\Users\\Patrick\\Desktop\\export.xlsx", matrix]][[1]] Out[79]= 0.410001 AbsoluteTiming[ Export["C:\\Users\\Patrick\\Desktop\\export.csv", matrix]][[1]] Out[81]= 0.124800 $\endgroup$
    – Patrick Bernhard
    Commented Apr 11, 2013 at 7:11
  • $\begingroup$ Thanks for your comment. With random data I cannot reproduce the results. The original data is from a company so I cannot post it. The datasets are 10000x30 matrixes with strings and dates and numbers. But then I understand that this is not a common problem for many people and this is already helpful. Thanks again and regards from Berlin! $\endgroup$
    – Patrick Bernhard
    Commented Apr 11, 2013 at 7:19
  • $\begingroup$ @PatrickBernhard Perhaps if you provide the type of field per column we can mimic your data more accurate. Would 10 columns for each type (strings, dates and number) do? $\endgroup$
    – Sjoerd C. de Vries
    Commented Apr 11, 2013 at 8:37
  • $\begingroup$ @PatrickBernhard The problem may lie in the representation of your date fields. What kind of format do you use? $\endgroup$
    – Sjoerd C. de Vries
    Commented Apr 11, 2013 at 22:11
  • 1
    $\begingroup$ @Sjoerd C. de Vries: For large size "XLSX" Export you need to enlarge the JVM maximum memory, which is preset to 512M doing something like this: Needs["JLink"];ReinstallJava[CommandLine -> "java", JVMArguments -> "-Xmx1024m"];` $\endgroup$
    – UDB
    Commented Nov 1, 2013 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.