# Writing CSV or alternative

I've tried to export a list to a CSV file. I shut it down after about 20 minutes of processing. The list has 261,000 rows and 30 columns. I finally exported it to a TXT file and its size was 111 MB. TXT went slowly but was acceptable. However the format is not nearly as good to work with. Is there a way to output a table that runs faster than Export?

The data has integers, doubles, dates and strings. I did a test using the last 2 cols and output that. I learned a few things that help a lot. The last two columns look like:

Dimensions[aOutput]
aOutput[[1 ;; 2]]
{261136, 2}
{{14., "Casualty Treaty XS CM"}, {13., "Casualty Treaty XS Working"}}.


They're all pairs of an integer followed by a string. The 2 column test timed at 17 seconds, but took 6 minutes of "wall" time. I was outputting to a network share at a remote data center with a 100 Mb connection. When I output the test to my C drive it took 8 seconds and about 10 seconds of wall time.

• RandomReal[10000, {261000, 30}] takes only about 100 seconds to export as a CSV using Export, though it's a 142 mb file, so it might help if you tell us what kind of data is in your columns (to reproduce the issue). – C. E. Aug 20 '13 at 14:47
• That seems really slow. Is it possible that you have some other computation tangled up with exporting the data? – Pillsy Aug 20 '13 at 14:52
• Please describe the data that you are exporting; it will affect the answers that are applicable. – Mr.Wizard Aug 20 '13 at 16:52

If the OutputForm of your expressions is acceptable for export you might use WriteString as follows:

dat = RandomReal[{-9, 9}, {261000, 30}];

str = OpenWrite["speedtest.csv"];
WriteString[str, Row[#, ","], "\n"] & ~Scan~ dat // AbsoluteTiming
Close[str];


{15.9369116, Null}

The file looks like:

1.18859,3.64721,2.2051,-3.76912,-8.36245,1.8706,-0.824268,3.00288,-5.60642, . . .
5.0864,-1.41888,5.12253,-8.98868,4.59512,-7.87181,-3.28615,-7.69277,8.52969, . . .


By comparison:

Export["speedtest2.csv", dat] // AbsoluteTiming


{45.5986081, "speedtest2.csv"}

The file looks like:

1.1885948579885053,3.6472076948684347,2.205102634330057,-3.769121848976269,-8.362445973589985, . . .
5.0863975133372605,-1.4188767356183476,5.122531261249101,-8.988682170593862,4.595118939236766, . . .


I'll wait for specifics of your data to propose other alternatives.

• Interesting. Using the test described in my previous comment your suggestion reduced the time from 8 seconds to 5 seconds when exporting to my C drive. Output to the network drive dropped from 6 minutes to about 3 minutes. Timing shows 11 seconds for the Network write. Maybe there's some sort of back and forth communication that happens with the destination device? I can copy the file in Windows from my local drive to the network drive almost instantly. the test is about 8MB, so at 100Mb/sec it should happen instantly. Maybe there's some sort of buffer size that can be adjusted? – Mitchell Kaplan Aug 21 '13 at 14:16