Apple Script to parse name and create files

Question

I am a physician trying to parse files created from Filemaker Pro in the following format: CCDOC|FamID|PtID|DocType|Date|FileExtension Where

• CCDOC is prefix identifying clinical care documents
• FamID is Family ID
• PtID is Patient ID
• Type of Document (Radiology, Lab, Consult, etc)
• Date (in format mm_dd_yyy)
• Extension (mostly PDF, but some JPG, etc)

I want to make an Apple Script that will create nested folders based on these types (Family folder contains patient folders in that family, which contain different document types, etc). Then the files would be moved to that folder.

Can you help me with this?

Here's an image of the file format

Here's the CSV file which creates them

Answer 1

Updated Answer

Based on what I have gathered so far, this is about as far as I can get you at the moment. It is pretty well commented so you can see what it is doing. Add an example and I can probably get you further along...

#!/bin/bash
#
# Go to the Desktop, or wherever the CSV file is and the output tree must be made
cd ~/Desktop || exit

# Parse "data.csv" using "|" as field separator
awk -F'|' '
  {
     # Pick up all fields of record into appropriately named variables
     CCDOC=$1; FamID=$2; PtID=$3; DocType=$4; Date=$5; Ext=$6;

     # Output name of directory to create, surrounded by "" to permit spaces, e.g. "Lab Test"
     print "\"ROOT/" FamID "/" PtID "/" DocType "/" Date "\""

   }' data.csv | xargs mkdir -p

Given a data.csv file like this:

CCDOC|F999|P5678|Consult|02-03-2013|PDF
CCDOC|F7777|P1111|Radiology|03-04-1999|JPG
CCDOC|F999|P4321|Lab Test|06-12-1997|PDF

you will get this:

Original Answer

At its heart, the answer to your question is to use the mkdir -p command in the shell that makes a directory, INCLUDING ANY NECESSARY INTERVENING DIRECTORIES, without complaining if anything already exists, or doesn't exist, along the way. Try it for yourself by pasting this into the Terminal

mkdir -p ~/Desktop/Freddy/Frog/on/a/log

and you will get this on your Desktop

Now, the question is whether to go parsing that Filemaker Pro CSV file using Applescript or not. Personally, I find Applescript so unbelievably verbose as to be unintelligible, and would go with straight bash every day of the week... and don't forget you can harness all that bash power from Applescript simply by using

do shell script xyz

Just to give a little example, say you saved the following as a sample file on your Desktop and called it data.csv. Note there are two patients (P5678 and P4321) in family F999.

CCDOC|F999|P5678|Consult|02-03-2013|PDF
CCDOC|F7777|P1111|Radiology|03-04-1999|JPG
CCDOC|F999|P4321|Lab|06-12-1997|PDF

You could then paste the following one-liner into your Terminal

cd ~/Desktop &&  awk -F'|' '{print "ROOT/" $2 "/" $3}' data.csv | xargs mkdir -p

and you would end up with the following already done

Let me explain how the script works. First it changes directory to your Desktop (cd ~/Desktop), and then, provided that worked (&&), it starts awk and tells it your fields on each line are separated by the vertical bar (awk -F'|'). awk will then read all the lines from data.csv, and split the fields and number them for you - so $1 will contain the CCDOC, $2 will contain the FamilyId, $3 will contain the PatientId. So for each line in the CSV file, we print ROOT/FamilyId/PatientId. In effect, the output of the awk script is as follows:

ROOT/F999/P5678
ROOT/F7777/P1111
ROOT/F999/P4321

However, we pass those lines into xargs and tell it to run mkdir -p on each one of them, so it effecively does this, which makes the folders I screen-grabbed above.

mkdir -p ROOT/F999/P5678
mkdir -p ROOT/F7777/P1111
mkdir -p ROOT/F999/P4321

If, rather than doing that from the Terminal, you wanted to do it from Applescript, you would simply put the same commands in a file and call it, say MarksThing

#!/bin/bash
cd ~/Desktop &&  awk -F'|' '{print "ROOT/" $2 "/" $3}' data.csv | xargs mkdir -p

then in Applescript, you would do

do shell script "MarksThing"

Though you would have to make it executable first with

chmod +x MarksThing

I know this doesn't completely answer your question but I still don't understand the rest of it. Rather than guess and waste a load of time, you can maybe answer my question (in the comments below your original question) and let me know if you are interested in pursuing this "non-Applescript" approach before I do too much more.

Answer 2

Just put in a path for the rootFolder which is where you want the folders to be located. Good luck.

set rootFolder to path to desktop

set tempVar to display dialog "Enter the following information separated by \"/\"" & return & "CCDOC/FamID/PtID/DocType/Date/FileExtension" default answer ""
set folderPath to text returned of tempVar

set thePath to POSIX path of rootFolder & folderPath
do shell script "mkdir -p " & quoted form of thePath

Answer 3

I suggest you look at FileMaker’s AppleScript dictionary and attempt to write an AppleScript that tells FileMaker and Finder to create the files and folders you want, rather than exporting a CSV from FileMaker and then parsing it and creating files from that. You would likely create a much simpler script that is easier to write and to maintain.

If you don’t have access to FileMaker, you might want to write an AppleScript that tells Numbers and Finder to create the files and folders. Numbers can read the CSV for you and Finder can create the folders and move the files. The commands for doing so are in the Numbers AppleScript Dictionary and the Finder AppleScript dictionary and they are fairly easy to learn.

For example, to make your root folder on your Desktop with Finder:

tell application "Finder"
    set theDesktopFolder to the path to the desktop folder as alias
    set theRootFolderName to "Patient Care"
    set theRootFolder to make new folder at theDesktopFolder with properties {name:theRootFolderName}
end tell

… then you can tell Numbers to open the CSV and loop through it column-by-column, cell-by-cell, getting the cell values, and tell Finder to create corresponding folders within theRootFolder object, which is stored as a variable by the above script so that all you have to say is:

make new folder at theRootFolder with properties {name:theCellValue}

The above script is written in a deliberately verbose way that makes it easier to read and understand. When your script is done, you may share it within another person who may have to, for example, change the “Patient Care” name to “Client Care” or similar and that is easy for them to do in an explicitly-written AppleScript, even if they have no AppleScript skills. That is a major feature of AppleScript.

You can find out how to do repeat loops in AppleScript in the AppleScript Language Guide. It will also show you how to open the AppleScript Dictionaries for the apps you want to employ, such as Numbers and Finder.

Also note that shell scripting can be dangerous. You can wipe out data in ways that are not possible with AppleScript. If you screw up with AppleScript, you get an error. If you screw up with bash, you can wipe out gigabytes of data. A lot of people on Stack Overflow will answer your AppleScript question with a bash answer, but that is not necessarily a good answer if you don’t have bash experience. Compared to bash, AppleScript has rubber bumpers so that non-programmers can safely create their own solutions.