# Get the last line from each of a large number of files, transform them, and write all results to a single new file

I have several hundreds of .csv files that are not logically named (contains numbers, characters, and special characters), all stored in a directory. Each file has large but unequal number of rows and columns. I do not need any information other than the last line of each file.

What would be an efficient way to use Mathematica to get the last line from each file, and summarize them to a new .csv file with the file name as the tag ?

@WReach suggested this, but I am not sure how to proceed, especially given the irregular titles.

• take WReaches module: myFunction[file_]:=Module[...] then make a list of your files: ls = FileNames["*.csv", "/path/to/files", 2] and then map that list onto your function to get the last line from all files. Then you can save this to a new files. You can also join the files names to the output so that each last line is associated with its filename. Jul 22, 2014 at 5:51
• You may also refer to this. Jul 22, 2014 at 7:46
• You can even make a palette! (OK I guess it won't work with hundreds of files, but self-promotion is self-promotion)
– acl
Jul 22, 2014 at 11:20

A simple way to read the last line of a file uses ReadList to get all lines from the file and then returns the last element of the list:

lastLine[file_] := ReadList[file, String] // Last


This code ignores the possibility of an empty file. If we care about such an eventuality, we can deal with it using a bit more logic:

lastLine[file_] := ReadList[file, String] /. {{} -> $Failed, ls_ :> Last @ ls}  This method reads the whole file to get the last line. An alternate method will be shown below which avoids that, but for now let's look at the rest of the problem. Here is a function that produces a summary that lists each CSV file in a directory along with the file's last line: summarizeFiles[directory_] := Module[{files} , files = FileNames["*.csv", directory] ; {FileNameTake[#, -1], lastLine @ #} & /@ files ]  It uses FileNames to get the CSV pathnames. The filename portion of the path is extracted using FileNameTake and then our helper function lastLine is used to get the last line. We can export the summary as a CSV like this: $dataDirectory = FileNameJoin @ {NotebookDirectory[], "data"};
$outputFile = FileNameJoin @ {NotebookDirectory[], "result.csv"}; Export[$outputFile, summarizeFiles @ $dataDirectory]  This example assumes that the input data and output file reside in the notebook directory -- change this assumption to suit your taste. The final result looks like this: FilePrint @$outputFile
(*
TI_2000_0.07_508.csv,"x[1] -> 8,x[2] -> 8,x[3] -> 7,x[4] -> 7,x[5] -> 0,x[6] -> 8,x[7] -> 0,x[8] -> 8,x[9] -> 7,x[10] -> 5"
TI_2000_0.13_846.csv,"x[1] -> 8,x[2] -> 7,x[3] -> 7,x[4] -> 7,x[5] -> 7,x[6] -> 7,x[7] -> 0,x[8] -> 7,x[9] -> 6,x[10] -> 5"
...
TI_2300_0.12_104.csv,"x[1] -> 8,x[2] -> 8,x[3] -> 8,x[4] -> 7,x[5] -> 8,x[6] -> 8,x[7] -> 4,x[8] -> 8,x[9] -> 4,x[10] -> 4"
TI_2300_0.1_966.csv,"x[1] -> 8,x[2] -> 8,x[3] -> 8,x[4] -> 8,x[5] -> 4,x[6] -> 4,x[7] -> 8,x[8] -> 8,x[9] -> 4,x[10] -> 4"
*)


Now we return to the issue of reading the entire file to obtain the last line. The first question is whether this overhead is really a problem. For the sample files, I would suggest that it is not. However, we can optimize if the actual data files are much larger in size, number, or both. The trick is to start reading from somewhere near the end of the file when trying to find the last line. The problem is, from which byte should we start reading given that we do not know the length of the last line?

If we can make some simplifying assumptions, then this is not a difficult problem. The first assumption is that there is a known maximum line length (including the newline delimiter sequence). We can start reading that far from the end of the file. We can ignore any extra lines or line fragments picked up in the subsequent read.

The second assumption is that the file encoding is such that there are no variable-length multibyte character sequences in the file. This is important because otherwise when we start reading from some arbitrary point in the file we might be unlucky enough to start in the middle of a multibyte sequence and read some very strange results. This assumption appears to hold true for the sample data set.

Under these assumptions, the code to skip most of the file and read the last line looks like this:

ClearAll[lastLine]

lastLine[file_, maxLineBytes_:1024] :=
Module[{s, line}
; SetStreamPosition[s, Max[0, FileByteCount@file - maxLineBytes]]
; line = ReadList[s, String] /. {{} -> \$Failed, ls_ :> Last @ ls}
; Close[s]
; line
]


SetStreamPosition is used to set the read position. FileByteCount and maxLineBytes are used to calculate the starting read point. Adjust maxLineBytes as desired.

If the simplifying assumptions are violated, then the methodology can still be used but the determination of the read starting point becomes more complex. It would involve a trial-and-error process that reads from various candidate start points until the last line is found without any multi-byte errors. It is left as an exercise to the reader to implement that additional logic :)

Edit

@librik points out that SetStreamPosition accepts negative positions indicating an offset from the end of the file. Thus, the use of FileByteCount is unnecessary if the SetStreamPosition is changed to:

Quiet[SetStreamPosition[s, -maxLineBytes], SetStreamPosition::stmrng]


Quiet is necessary to guard against the case when the file length is less than maxLineBytes.

• Excellent response! +1 Jul 22, 2014 at 15:33
• SetStreamPosition will take a negative number, meaning "position backwards from the end." You don't have to call FileByteCount to compute it. Jul 29, 2014 at 8:41
• @librik Thanks, that never occurred to me. I've added a note to that effect to my response. Jul 29, 2014 at 13:13
• @WReach, nice. I'm interested in extending stream-based processing to read/write, say, selected columns or cell ranges to & from text tables and spreadsheets. But before potentially reinventing the wheel, any existing APIs available to do this? Jan 11, 2016 at 19:15
• @alancalvitti I am not aware of any higher-level functionality for such variable-length record extraction in stream-based fashion. Perhaps Leonid's new (as yet unofficial) streaming extensions would be useful when implementing such a thing? Jan 12, 2016 at 1:09