Frinton Fogey

Q: Sort csv file with Unix sort in Applescript

I am trying to sort a csv file on field 5 using unix sort but nothing happens, where am I going wrong? Any help gratefully received!

 

set filePath to quoted form of POSIX path of "/Users/Ours/Documents/Test File.csv"

 

do shell script "sort   -t ',' -k5 " & filePath & " -o " & filePath


Posted on Aug 26, 2016 8:54 AM

Close

Q: Sort csv file with Unix sort in Applescript

  • All replies
  • Helpful answers

  • by Frinton Fogey,

    Frinton Fogey Frinton Fogey Aug 26, 2016 8:57 AM in response to Frinton Fogey
    Level 1 (4 points)
    Mac OS X
    Aug 26, 2016 8:57 AM in response to Frinton Fogey

    Not sure what happened to the script, should be:

     

    set filePath to quoted form of POSIX path of "/Users/Ours/Documents/Test File.csv"

     

    do shell script "sort   -t ',' -k5 " & filePath & " -o " & filePath

  • by Camelot,

    Camelot Camelot Aug 26, 2016 11:25 AM in response to Frinton Fogey
    Level 8 (47,233 points)
    Mac OS X
    Aug 26, 2016 11:25 AM in response to Frinton Fogey

    What actually happens? What do you expect to happen?

     

    As written the script should simply write the sorted data back to the file. Are you saying that the file doesn't contain the sorted data? or are you expecting the sorted data to be passed back to your script somehow?

     

    If you're expecting to get the data in your script then either remove the '-o' switch in your shell command, or re-read the file after the shell command executes.

  • by Frinton Fogey,

    Frinton Fogey Frinton Fogey Aug 27, 2016 1:30 AM in response to Camelot
    Level 1 (4 points)
    Mac OS X
    Aug 27, 2016 1:30 AM in response to Camelot

    I want to sort a csv file into ascending sequence using the 5th field and output the sorted file back onto itself. However, the resultant sorted file is identical to the original, the sequence has not changed and no matter which field I use (1st, 2nd, 3rd etc) the sequence of the output file does not change. I have tried outputting to a temporary file but again the sorted file remains the same as the original. I have used this script on other files in the past and it has always worked so am totally confused hence the poorly written original post for which I apologise!

  • by Hiroto,Solvedanswer

    Hiroto Hiroto Aug 27, 2016 4:33 AM in response to Frinton Fogey
    Level 5 (7,276 points)
    Aug 27, 2016 4:33 AM in response to Frinton Fogey

    Hello

     

    Most likely your csv file uses CR as line ending where sort(1) expects LF.

     

    Try something like the following script, which will translate CRLF and CR to LF in source text before passing it to sort(1).

     

     

    set f to "/Users/Ours/Documents/Test File.csv"
    do shell script "f=" & f's quoted form & "
    perl -lp0e 's/\\015\\012|\\015/\\012/og;' \"$f\" | sort -t, -k5 -o \"$f\""
    

     

     

     

    Regards,

    H

  • by Frinton Fogey,

    Frinton Fogey Frinton Fogey Aug 27, 2016 7:51 AM in response to Hiroto
    Level 1 (4 points)
    Mac OS X
    Aug 27, 2016 7:51 AM in response to Hiroto

    Hi

    Your script works, no idea what the perl command is but it worked!

    I then when back to my script to try again and now that works on the same file so must be to do line endings, will do some more research, I would have never thought of that, so, many thanks for saving my sanity!

    Regards

  • by Hiroto,

    Hiroto Hiroto Aug 27, 2016 11:19 AM in response to Frinton Fogey
    Level 5 (7,276 points)
    Aug 27, 2016 11:19 AM in response to Frinton Fogey

    You're quite welcome. And the perl script is just replacing CRLF or CR with LF.

     

    By the way, in these days, text file using CR as line ending is rather rare because CR line ending is typically of (classic) Mac OS. Unix uses LF and Windows uses CRLF.

     

    One source is AppleScript itself in which you can create text of CR line ending unknowingly. For instance, "do shell script" command translates LF in output text from shell to CR unless its "altering line endings" parameter is set to false.

     

    E.g.,

     

     

    do shell script "printf '%s\\n' a b c d"
    set t to result
    {t, t's id}
    
    --> LF is translated to CR and trailing LF is removed
    (*
    {"a
    b
    c
    d", {97, 13, 98, 13, 99, 13, 100}}
    *)
    

     

     

    do shell script "printf '%s\\n' a b c d" without altering line endings
    set t to result
    {t, t's id}
    
    --> LF remains as is and trailing LF is preserved
    (*
    {"a
    b
    c
    d
    ", {97, 10, 98, 10, 99, 10, 100, 10}}
    *)
    

     

     

    So be careful. If you write the text returned by the first "do shell script" example, it ends up with text file with CR line ending, which may not be what you intend.

     

     

    Regards,

    H