# How to read data file quickly?

I have a tab separated value file with 10 million rows each of which has three tab separated values. The first value is a string, the second an integer, and the third another string. How to read efficiently (in terms of timing and memory footprint) the $n^{th}$ to $(n+100)^{th}$ rows of the file into Mathematica as

{
{_String, _Integer, _String},
...
}


?

-

For a one-off read you can Skip a number of records:

str = OpenRead["test.tsv"];
Skip[str, Record, n - 1];
data = ReadList[str, {Record, Number, Record}, 100, RecordSeparators -> {"\t", "\n"}];
Close[str];


If you will be reading from the same file many times, it may be worth building an index you can use with SetStreamPosition

str = OpenRead["test.tsv"];
index = Table[pos = StreamPosition[str]; Skip[str, Record]; pos, {100000}];

SetStreamPosition[str, index[[n]]];
ReadList[str, {Record, Number, Record}, m, RecordSeparators -> {"\t", "\n"}]]



On my PC building the index took about half a second for 10^5 rows in the file, assuming it scales linearly this would be about a minute for 10^7 rows. So this is only worth doing if you are going to be doing a lot of reads.

-
Since this is going to be used on a very large file, it is very likely that at some point Abort will be called while the file is being Read. So, it needs to be wrapped in a CheckAbort so that you can ensure that you close the stream if an Abort occurs. Here's a cautionary tale of what happens if too many Streams are left open. Also, an error is thrown if you attempt to open a file with an existing Stream. –  rcollyer May 5 '12 at 16:14
+1 - Nice use of streams. You can build index about 5-6 times faster (according to my benchmarks) with this function: buildIndex[file_] := With[{bytes = BinaryReadList[file]}, Prepend[#, 0] &[ Most@Flatten[(SparseArray[#, Length[#], 1] & @Unitize[bytes - 10])["NonzeroPositions"]] - 1]], when there is enough memory to hold all file's contents in RAM at once. A speed - memory tradeoff. –  Leonid Shifrin May 5 '12 at 18:48
What if the rows are of form _String _Integer _String _Integer? –  qazwsx May 6 '12 at 1:57
@Leonid: I'm sure you are aware of it but probably not every other reader: you could build the index with BinaryReadList as you suggested even if the file content doesn't fit in RAM by handling the content in chunks. That would let you take control over speed vs. memory in even more detail... –  Albert Retey May 6 '12 at 9:35
@AlbertRetey Thanks, a very good point! –  Leonid Shifrin May 6 '12 at 9:51

Here is a solution for Linux/( ? Mac ? ) uses, or Windows users with a Linux toolkit.

To read line 23681 to 23781, use the following variants of head and tail.

data=Import["!head -23781 /tmp/data.txt | tail -n 100", "Text"];

-
Any idea how to make this also work on Windows? –  qazwsx Nov 16 '12 at 0:41
@Problemania Reports say that this sourceforge.net/projects/unxutils or msysgit will give you unix command line utils in the windows environment. –  image_doctor Nov 16 '12 at 1:22
I confirm that this works on Mac too. –  Taiki May 16 '13 at 20:51

You can use set stream position which sets the current point in an open stream. Then you can use Read which reads one expression from an input stream, and returns the expression with byte, character, expression, Number, Real, String or Word options. Something like:

n = 4;
Skip[str, Record, n - 1]
Close[str];


Edit 2 with Read and Loop no abort check

data = {};
n = 4;
Skip[str, Record, n - 1]
For[i = 0, i < 100, i++,
{a, b, c} = Read[str, {Word, Number, Word}];
data = Append[data, {a, b, c}];
]
Close[str];
data


Edit 2 with Read and Loop with abort check

data = {};
n = 4;
Skip[str, Record, n - 1]
For[i = 0, i < 100, i++, CheckAbort[Module[{a, b, c},
{a, b, c} = Read[str, {Word, Number, Word}];
data = Append[data, {a, b, c}];
], Close[str]]]
Close[str];
data

-
That gives a Read::readn: Invalid real number found when reading from ... error. –  qazwsx May 5 '12 at 15:46
SetStreamPosition[str, n] doesn't seem to be correct. It shouldn't be n. n is the row index. But SetStreamPosition doesn't seem to operate on rows. –  qazwsx May 5 '12 at 15:52
For won't work as written, see this question and my answer. Also, String captures the entire line, Word should be used instead. Lastly, as this is a large file, the For loop should be wrapped in a CheckAbort to ensure that the Stream is closed properly. I'd use the OpenAndRead function I define here. –  rcollyer May 5 '12 at 16:09
@ rcollyer Thanks for the links. But, some how they don't point to the right page. I found via search and it was useful. –  s.s.o May 6 '12 at 0:31
@ MaThEmAtika, I wrote them lack of time in order to give you some possible paths and functions. –  s.s.o May 6 '12 at 0:34