0

I have a large number of CSV files, each file having multiple numeric columns. The first column is Unix timestamp. How can I sort such file? Thank you.

An example row of the file as below:

1376317246; 4;  11703.99824;    10477.029091173334; 89.51666666666667;  6.7108864E7;    2.0937962933333334E7;   0.0;    1.4;    235.53333333333333; 3.8666666666666667
|improve this question
1

I wouldn't call it a CSV file if it looks like that.

sort -t";" filename

should work. This will sort on all columns. If that's unappealing, then this should suit you.

From the docs:

   -t, --field-separator=SEP
          use SEP instead of non-blank to blank transition
|improve this answer
  • Specifying the delimiter is of no use here as you don't use it. That would be useful in sort -t ';' -k2,2 to sort based on the second field. But not here we're you're sorting based on the full content of the line. – Stéphane Chazelas Mar 22 at 14:42
  • Although I missed that, @StéphaneChazelas It's still necessary because ; is larger than digits. So if there's a row whose first field is 137631724 (this is the value in the example without the trailing 6), sorting on the whole content will place this below the 1376317246, which would be incorrect. – y_wc Mar 22 at 15:07
  • Still, -t will make no difference. You'd need sort -t ';' -k1,1 for it to make a difference. But here, all you need and want is sort -n. – Stéphane Chazelas Sep 30 at 13:37
1

you could use sort

sort --field-separator=';' --key=1 yourCSVfile.csv

if you need to create a new sorted file just add an output file:

sort --field-separator=';' --key=1 yourCSVfile.csv > sortedCSVfile.csv
|improve this answer
1

I would use sort with following option:

  • -g (--general-numeric-sort) for numeric sort just in case, because default sorting mechanism is alphanimeric and sorting {9..11} would result in 10 11 9.
sort -g filename
|improve this answer
  • you're right. I'll edit the answer. So -g would be sufficient. – Jakub Jindra Mar 22 at 14:56
0

If all lines start with a timestamp, a simple

sort filename

would do the trick, 'cause all the lines will then start by a same-length string of digits-only... at least if they are of current events.

If this was not the case (ie, dates too close to 1970, or leading spaces), then this sort would fail.

You could then workaround by adding the -n flag (or -g for GNU)

sort -n filename

|improve this answer
  • 1
    all the lines will then start by a same-length string of digits-only, unless there are timestamps from before September 2001, or there is some variable space or zero padding or some of the numbers are in the 1.4e9 format... sort -n or sort -g (GNU only for the latter) would make it more robust. – Stéphane Chazelas Mar 22 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.