Speeding up Import and Export in CSV format

Question

I am handling large numerical data in Mathematica. In smaller problems everything worked fine using Export and Import with the parameter csv and nothing more.

Now I am facing a much larger data volume and plain Export and Import is way too slow for CSV format.

What I want to do: First, exporting a numerical list of approximately $1400 \cdot 260$. Then I perform some calculations outside of mathematica and finally I import a csv file back using Import.

In this question I read how to improve the speed of Export with CSV.

I tried

Export["data.csv", 
  ExportString[Transpose[temp], "CSV", "FieldSeparators" -> ", "], 
  "Table"];

for a toy-example of dimension $9 \cdot 6$. This could be

temp = RandomReal[{0, 1000}, {9, 6}];

The problem is that I got additional blank lines in my CSV file. How can I avoid those blank lines? I did not have them using plain Export.

Second part of the question: can I use a similar approach to speed up Import for CSV files?

I work in English locale in Windows 7 and Mathematica 8. My data should be comma-separated.

The result of the above looks like this:

155.9418457227914,427.72566448956945,462.4370455183139,434.230107096781,377.73423736605037,457.7044624877774,229.5721937681028,453.6973831247924,827.5146478718962

656.9573702857699,975.1048399942904,716.715190156526,67.07781324817643,168.78248854317894,863.1953962590844,997.7580107302701,427.94798294100747,565.2955778916687

192.3648037459477,435.0418975785194,126.17228368369842,772.0737083559297,453.73573640921836,957.9178360741387,920.4158275934401,234.75353158374764,162.82606110943834

841.7132070637356,799.1268178998612,931.2448706410551,950.7753472229233,114.01596316796622,145.0771999411104,287.47149951303663,786.9008107323455,99.09420650484662

116.9916885502289,715.7594598282562,970.6252946068753,654.1742185278038,262.3778046629968,200.13980161337577,347.24862854841354,314.5612015073982,241.11046402342163

203.65015448763597,952.1236458849723,578.2673369638862,527.2990305555661,655.1228742370724,318.81372163827496,311.2738362265584,315.97629887850667,514.7676854642548

EDIT: A small example of the data that I want to import (2nd step) can be found here. The true problem size is here.

Answer 1

Here's a much faster, purely Mathematica way than using Import to import your data:

UPDATE

As Leonid mentioned the previous code doesn't exactly replicate Import. The truth is I was only trying to retrieve the numerical part. Here's an updated version that tries to replicate the output from Import.

readYourCSV2[file_String?FileExistsQ, n_Integer] := Module[{str = OpenRead[file], data}, 
  data = ReadList[str, Table[Record, {n}], RecordSeparators -> {",", "\n"}]; 
  Close[str]; 
  ReleaseHold[ToExpression[data, InputForm, Hold] /. {Plus[Times[x_, E | e], y_] :> x * 10 ^ y}] 
 ]

Here, n is the number of columns.

UPDATE 2

Now for the Export, here's a fast, again, purely Mathematica way to export in CSV format.

writeYourCSV[file_String, list_List?MatrixQ] := 
 With[{str = OpenWrite[file, PageWidth -> Infinity], len = Length[ list[[1]] ]},
      Scan[Write[str, Sequence @@ (Flatten[Table[{FortranForm[ #[[i]] ], OutputForm[","]}, 
              {i, len - 1}]]) ~ Join ~ { FortranForm[ #[[len]] ] }] &, list]; Close[str];
]

This takes less than 10 seconds to write your large data back to CSV format:

writeYourCSV["testcsv.csv", databig] // AbsoluteTiming

{9.921969, Null}

Answer 2

Here is a Java-based solution, pretty fast but valid only when all your columns are numerical (double) values.

First, grab and run the code for the Java reloader (The linked version should work on Windows and probably Linux, but was reported to have issues for OS X. So, Mac users may try this one instead: Import["https://gist.github.com/lshifr/7307845/raw/SimpleJavaReloader.m"] - not yet tested this version for other platforms). Then, compile this class:

JCompileLoad["public class DoubleParser{
   public static double[] parseDouble(String[] strdub){
      double[] res = new double[strdub.length];
      int i = 0;
      for(;i < strdub.length;i++){
         try{
            res[i]= Double.parseDouble(strdub[i]);
         } catch (NumberFormatException e){
            res[i] = 0;
         }
      }
      return res;
   }
}"] 

Then, here is the Mathematica counterpart:

ClearAll[importDoubleCSV];
Options[importDoubleCSV]={"Headers"->True};
importDoubleCSV[file_String?FileExistsQ, opts : OptionsPattern[]]:=
  With[{fn=If[TrueQ[OptionValue["Headers"]],Rest,Identity]},
    Transpose[
      DoubleParser`parseDouble/@
        Transpose[
          DeleteCases[                
            StringSplit[fn[StringSplit[FromCharacterCode[BinaryReadList[file]],"\n"]],","],
            {s_String/;StringMatchQ[s,Whitespace]}
          ]
        ]
    ]
  ]

For your small file, the result agrees with what you get by using Import, after you remove empty rows:

res1=Rest[DeleteCases[Import["~/Downloads/returns_out_small.csv"],{""..}]];
res2=importDoubleCSV["~/Downloads/returns_out_small.csv"];
res1==res2

(* True *)

Your large file gets processed on my machine in about 4 seconds:

(resLrg=importDoubleCSV["~/Downloads/returns_out.csv"])//Short//AbsoluteTiming

(* 
    {4.789668,
     {{0.000449449,0.000418204,<<1415>>,0.000064701,0.000045972},
        <<1417>>,{<<1>>}}
    }
*)

which doesn't look bad to me. I wasn't patient enough to wait until Import["~/Downloads/returns_out.csv"] finishes, so did not compare results in this case - but the reader is most welcome to do that (and the timings too).

An added advantage here is that we get the results packed:

Developer`PackedArrayQ @ resLrg

(* True *)

Note that Java parsing code adopts a convention to replace all non-parsable strings with zeros. It is possible to improve on this, by returning also the positions of non-parsable strings, separately. Note also that the UTF-8 encoding is implicitly assumed.