MacOS – Split csv file using Automator Service (Finder Context Menu)

automatorbashfindermacos

I am trying to create an Automator Service for Finder's Right-Click Context Menu that can split any selected csv file, whilst copying in the original header at the top of every file.

My current attempt is to make Automator run this Bash Shell Script:

#!/bin/bash

FILE=$(ls -1 | grep MY_CSV_FILE.csv)
NAME=${FILE%%.csv}

head -1 $FILE > header.csv
tail -n +2 $FILE > data.csv

split -l 50 data.csv

for a in x??
    do
        cat header.csv $a > $NAME.$a.csv
    done

rm header.csv data.csv x??

This script will split MY_CSV_FILE.csv into new files with max 50 lines while copying in the original header at the top of every file. The new files will have the original name appended with xaa, xab, xac etc.

Regarding the Automator setup, this is the Service I'm currently working on. The problem right now is that I'm unable to pass the Selected File in Finder to the Bash script.

enter image description here

Notice that:

  • Service receives: files or folders in Finder.app.
  • Pass input to Shell script: as arguments.
  • I have removed #!/bin/bash from the top of the Shell Script and set the Shell to: /bin/bash.
  • I switched MY_CSV_FILE.csv for "$f" – not sure if that's correct.

Do I also need to specify the path using something like "$@" for both the input file and the resulting output files? I haven't done something like this before, so I'm not really familiar with that variable and "$f" for that matter.

How could I make this work? I'd like the resulting files to appear in the same folder as the file I select to run the Service on, via the Finder Right-Click Menu. It would be even better if the Service only accepted csv files.

enter image description here

Best Answer

I'd write the code a bit differently, and here is an example of how I'd do it:

#!/bin/bash

for f in "$@"; do
    if [[ -f $f ]]; then
        d="$(dirname "$f")"
        n="$(basename "$f")"
        t='/tmp'
        if [[ ${n##*.} =~ [cC][sS][vV] ]]; then
            head -1 "$f" > $t/h.tmp
            tail -n +2 "$f" | split -a 3 -l 50 - $t/tmp.
            i=1
            for s in $t/tmp.a??; do
                fn="$d/${n%.*}.$(printf '%03d' $i).csv"
                if [[ ! -f $fn ]]; then 
                    cat $t/h.tmp $s > "$fn"
                    ((i++))
                else
                    rm $t/h.tmp $t/tmp.a??
                    echo "The file '"$fn"' already exists!"
                    exit
                fi
            done
            rm $t/h.tmp $t/tmp.a??
            echo ''
        fi
    fi
done
  • As presently coded, it handles one or more files passed to the service.
  • Makes sure the object being acted upon is a file, not a directory.
  • Makes sure the file has a .csv extension (regardless of the case of the extension).
  • Creates the temporary files in: /tmp
  • Checks to see that the output filename doesn't already exist, and if it does, it cleans up and exits.
  • Writes to a file with a numerically incremented filename, e.g. file.001.csv, file.002.csv, etc., in the same directory as the file(s) passed to the service.
  • Removes the temporary files created in: /tmp
  • As presently coded, it handles files with a line count of up to 49,950 split to 50 line files, not counting the header.
    • Note that no error handling is coded for the total line count of the source file, however, could be easily added.
    • Or easily modified to handle files with a line count of up to 499,950 split to 50 line files, not counting the header, by changing -a 3 of the split command to -a 4 and '%03d' of the printf command to '%04d'. You'd also change $t/tmp.a?? in
      for s in $t/tmp.a??; do and rm $t/h.tmp $t/tmp.a?? to: $t/tmp.a???

I'd also add a Run Apple Script action to the service, with the following code:

on run {input, parameters}
    if (item 1 of input) is "" then
        display notification "Splitting of the target file(s) is finished!" with title "Split CSV File(s)"
    else
        display notification (item 1 of input as string) with title "Split CSV File(s)"
    end if
end run

This enables the output of the echo commands in the Run Shell Script action to display a notification if a output file already exists or when the splitting is finished.

Note that while notification could be done from within the Run Shell Script action using osascript, nonetheless, I did it this way as it was easier to code.

Automator Service Workflow

This was tested on a file named file.csv in Finder, which has 200 lines in it, and the images below show what was created by the Run Shell Script action portion of the Automator service when run on the file.

CSV File in Finder

Contents of split CSV File in TextEdit

Related Question