2

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

  • Can you edit your question to clarify what it is about your attempted approach that isn't working? That is, do you get an error? Or does it work, but just not in the way you want? Or is it that you'd like it to do something in addition to what it's actually doing now? Something else? Also, can we assume the actual Shell Script you're referencing works, or is that your problem? – Monomeeth Aug 12 '17 at 2:25
  • @Monomeeth A very valid point, sorry about that. Running the service gave a macOS error box saying "Shell script error" or something like that. It didn't accomplish anything close to success. I didn't see any specific code error in Automator. It also seems to have created a file called x??.csv in my user folder (~). – Winterflags Aug 12 '17 at 9:24
6
+50

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

  • Bravo, this is very impressive work and a massive improvement on the original script. There's a lot to unpack here so I will have to do it in due time, but I can confirm that the Service works great upon first tests. Out of curiosity (not need), is there a reason to code for 49,950 instead of 499,950 lines, e.g. performance? I will probably never need the latter, but interesting to learn. – Winterflags Aug 12 '17 at 9:19
  • 1
    @Winterflags, There is almost always more then one way to do something and why I started off with "... here is an example ...". With the code of the OP, the split filenames were going to be incremented by the value of $a, i.e. x??, e.g. xaa, xab, etc. It's a personal preference for me, I want a numeric value in the split filenames, so I coded it for that. In the code of the OP, for a in x?? would have failed if the source file had line count more then 49,950 split to 50 line files because x?? only accounts for 999 files where x??? accounts for 9999 files. – user3439894 Aug 12 '17 at 14:51
  • 1
    @Winterflags, Both the code in the OP and my code suffer from the same hard coded limitation, because as coded both assume there will not be more the 999 files create by the split command So, either the pad has to be large enough to accommodate without doing the math first or the math can be done first and coded to dynamically accommodate. It's less coding to adjust the pad then checking the line count first. If you want to ensure a very high number of lines then increment the values even higher then as noted in my answer so the pad is more then adequate. – user3439894 Aug 12 '17 at 14:51
  • 1
    @Winterflags, Changing -a 3 of the split command to -a 5, and '%03d' of the printf command to '%04d' and $t/tmp.a?? in for s in $t/tmp.a??; do and rm $t/h.tmp $t/tmp.a?? to $t/tmp.a????, will handle files with a line count of up to 4,999,950 split to 50 line files. To make it even easier, $t/tmp.a?? can be set to $t/tmp.* and then you'd only have to modify -a 3 of the split command and '%03d' of the printf command setting each to a higher number, e.g. -a 8 and '%08d' will handle files with a line count of up to 4,999,999,950 split to 50 line files. – user3439894 Aug 12 '17 at 14:52
  • @Winterflags, The primary point is, I wanted you to know there was no error handling around this and how to adjust what to account for it. While I did say "... however, could be easily added." that really should have been "... however, could be added." and why changing the values as mentioned is actually the easiest thing to do. – user3439894 Aug 12 '17 at 14:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .