Sort files by spreadsheet

I have a folder than contains over 1000 text files. The text files are supposed to be organised in folders.

I have a spreadsheet which states which text file goes in which folder:

Is there any way I can make the "Finder" respond to this spreadsheet, so it puts the correct file in the correct folder. Maybe something involving Applescript or the terminal? It's just a simple "Apple Numbers" spreadsheet, so I can copy and paste the data out into something else.

Solution 1:

The following assumes that neither file nor folder names contain comas (,) and that the exported CSV file uses , as the separator.

As small errors can lead to unexpected results and data loss it's recommended to make a backup first.

• Remove any additional columns, header lines and empty lines at the end from your table (so it only contains two columns with file name and folder name respectively)
• Export the data as a CSV file named files.csv into the folder containing the text files
• Open Terminal
• cd into/the/folder
• Run perl -pi -e 's/\r\n/\n/;' files.csv to convert the CSV from DOS-like line ending to the standard Unix style

• Copy/paste the following (test code, will not create/move anything)

  while IFS=, read file folder; do
      # if the folder doesn't exist yet -> create it
      [[ -d "$folder" ]] || echo mkdir "$folder"
      # if file exists -> move it
      if [[ -r "$file" ]]; then
          echo mv "$file" "$folder"/
      else
          echo "Uups, $file not found"
      fi
  done < files.csv
  

• If the output looks ok (especially if no "Uups, xxx not found" output is shown, nor any other error messages), run

  while IFS=, read file folder; do
      [[ -d "$folder" ]] || mkdir "$folder"
      if [[ -r "$file" ]]; then
          mv "$file" "$folder"/
      else
          echo "Uups, $file not found"
      fi
  done < files.csv