Return to Revisions

Summary

This is generalized piece of code for reading FAST rows terminated with a newline from a CSV or TSV text stream with UTF8 encoding.

You may specify options for the filetype, the starting and ending line number, as well as a string to search for a position in the file (find).

The import option (True/False) controls whether there is going to be a post-processing of the rows read from the file with the ImportString function. This is slower than the ReadList with user specified types option or with import->False.

Code

null[_String] := Null
countLines = Length@ReadList[#, null@String, NullRecords -> True] &;

ClearAll[readRows];
Options[readRows]={filetype->"CSV",start->0,end->-1, types->{}, import->True, find->""};
readRows[fname_String?FileExistsQ,OptionsPattern[]]:=Module[
{txtstrm, ftype,typesval,impmethod,
 startLine,endLine,line1,str, 
 hdr, body, bodyImport},

(* Blank function for skipping word separators, e.g. commas *)
blnk[]=blnk[Character];
blnk[_String]=Sequence[];

(* Get Option Values *)
startLine=OptionValue[start];endLine=OptionValue[end];
ftype=OptionValue[filetype];typesval=OptionValue[types];
impmethod=OptionValue[import];str=OptionValue[find];

(* Open Stream *)
txtstrm=OpenRead[fname];

(* Process Header, i.e. first line *)
line1 = ReadList[txtstrm,Record,1][[1]];
Which[
 ftype=="CSV", hdr = StringSplit[line1,","],
 ftype=="TSV", hdr = StringSplit[line1],
 True, Abort[] ];

(* If user has not specified any types for ReadLine, types\[Rule]{} *)
If[Length@typesval==0,typesval= Riffle[Table[Word,Length@hdr],blnk[]]];

(* If user has not specified the line to end reading *)
If[endLine==-1,endLine=countLines@fname];

(* If user specified to start reading the text stream from a specific line *)
If [startLine!=0,Skip[txtstrm,Record,startLine]];

(* If user specified a string to find in a file *)
If[str!="",Find[txtstrm,str]];

(* Check the import option for post-processing each row with ImportString *)
If[impmethod,
 body =ReadList[txtstrm,Record,endLine-startLine]; 
 bodyImport= ImportString[StringJoin[Riffle[body,"\n"]],ftype,CharacterEncoding->"UTF-8"],
(* else *)
body =ReadList[txtstrm,typesval,endLine-startLine,WordSeparators->{"\n",","}]];

(* Close the stream *)
Close[txtstrm];

(* Add the header at the beginning, return the result *)
If[impmethod,Prepend[bodyImport,hdr],Prepend[body,hdr]]   
] (* End of readRows *)

Test

fname = "ExampleData/financialtimeseries.csv";

Take[Import[fname], 10] // TableForm

(* With ImportString *)
(res = readRows[financialFileName, end->10]) // TableForm
Head /@ res[[2]]

(* With default ReadLine types *)
(res = readRows[financialFileName, end->10, import->False]) // TableForm
Head /@ res[[2]]

(* With User Specified ReadLine types *)
sk[] = sk[Character];
sk[_String] = Sequence[];
mytypes = {Word, sk[], Number};

(res = readRows[financialFileName, end->10, import->False, types->mytypes]) // TableForm
Head /@ res[[2]]

Acknowledgements

• The countLines function is taken from this answer of Mr. Wizard.

• The combination of ReadList with ImportString and Riffle is taken from an answer in the same post here by Leonid Shifrin.

• The best part of the code, which is the fastest reading with ReadLine based on user specified types, is taken from this answer of Mr. Wizard. It would be great if someone analyze how exactly this blank, skipping characters work and whether I am using them appropriately here.

Open Source DBAPI Project

I am planning to include this in my DBAPI project so If you come up with any improvements on the code above or ideas for further enhancements please let me know.

PS: Can you make an improvement to understands quoted strings with embedded commas as in this file "ExampleData/TreesOwnedByTheCityOfChampaign.csv" for example ? What is going to be the types or the blanks to use in ReadLine e.g. for "5,600" strings ?