1

I have a CSV file containing the following two columns:

  1. Column A: From Directory (e.g. /Volumes/Backup Plus/Salesforce/Order Test/OR-1000049439).

  2. Column B: To Directory (e.g. /Volumes/Backup Plus/Salesforce/Opportunity Test/Q-DNYE20180508-2308).

I need to write a script in Terminal that will loop through the CSV and move the directory from Column A into the directory from column B. The end result would be the OR-1000049439 folder residing inside the Q-DNYE20180508-2308 folder). There are roughly 13,000 rows in which I need this to occur.

Can someone please help me on how to do this?

Here is a copy of the first few rows in the CSV file:

enter image description here

I need the folder in A2 to end up in the folder on B2. I need the folder in A3 to end up in the folder on B3, etc...

Brians-MacBook-Pro:Data Migration briangannuscio$ Head OrderFileV2.csv
/Volumes/Backup Plus/Salesforce/Order Test/OR-1000050081,/Volumes/Backup Plus/Salesforce/Opportunity Test/Q-RGORD20170207-1526

Brians-MacBook-Pro:Data Migration briangannuscio$ File OrderFileV2.csv
OrderFileV2.csv: ASCII text, with CR line terminators
2

There are many ways to tackle this task. Below is an approach using the Go language. I expect others will soon be along with alternative tools and languages.

If this is a one-time task, expect to spend time handholding the process.

If this going to become a regular automated task, spend time now to get the script or tool right.

Things to be aware of:

  • Appreciate that the csv file format is not fully standardized.
  • Know that Excel has a reputation with the csv format.
  • Does any file or folder name contain a quote, space, or comma?
  • What happens if folders are listed but missing on disk?
  • What happens if a folder for moving is listed twice?
  • What happens if a folder for moving already exists at the destination?

Back-up your data before running any answers.

csv-move

The csv-move tool requires UNIX/LF line endings. Converting the line endings is a problem solved elsewhere.

Ready to use binaries of csv-move are available in the latest release. Below is an extract of the code:

// Read from stdin - expects LF line endings
r := csv.NewReader(os.Stdin)
r.FieldsPerRecord = 2

for {
    line, err := r.Read()
    // ...

    // Perform the command
    cmd := exec.Command(mvPath, "-vn", line[0], line[1])
    if err := cmd.Run(); err != nil {
        log.Fatalf("move failed: %s", err.Error())
    }
}

Test your CSV file can be parsed as two entries per line:

./csv-move < input.csv

Once tested, and you are happy with the printed move commands, perform the move:

./csv-move -move < input.csv
  • 1
    Thanks for pointing out the risks of using a csv to drive such an operation. They most probably will apply to any solution offered here. – nohillside Jun 26 at 9:48
1

You should expect that any csv file will need to be sanitized before using it in the shell environment. At minimum, the header, if it exists, should be removed, all line terminating characters will need to be converted to LF (the linefeed character), and the last line of the csv file will need to be terminated.

The file command shows that your csv file has CR (carriage return) terminated lines. So, using simple available commands, you can convert the CR characters to LF characters with tr then pipe the results to sed to remove the header and terminate the last line, creating a new sanitized file.

tr '\r' '\n' < OrderFileV2.csv | sed '1d;a\' >sanitizedOrderFileV2.csv

The new file should be checked for contiguous rows. Empty fields and lines should be removed. You now should have a file that you can feed into a while read loop.

1

On shell level

while IFS=\, read -t $'\r' s t; do
    echo mv -n "$s" "$t/"
done < orderfile.csv

Remove the echo once you are sure the result looks ok.

As a small safeguard against errors I would recommend to limit a CSV to about 1000 lines.

  • Using echo is fine to see if the command is being formatted properly; however, it's not going to stop errors from occurring due to other issues, i.e., "PS: If the same source folder is listed several times you will loose data.", or other possible errors. At a minimum, I would use the -n option with the mv command, then there will be no data loss. Going a bit further, I would also add the -v option so as to be informed if the -n option came into play, e.g.: mv -nv "$s" "$t/" – user3439894 Jun 26 at 12:35
  • However, since the default Scrollback buffer is 10,000 lines and the CSV file has 13,000 lines, I'd also use tee to create a log.txt file, e.g. mv -nv "$s" "$t/" 2>&1 | tee log.txt The log.txt could then be used to more easily rectify any issues. It's that or code the script differently to accommodate any errors that may occur. – user3439894 Jun 26 at 12:35
  • 1
    @user3439894 The answer assumes a one-off solution, and even then it‘s better to split the CSV into files < 1000 lines. Getting that snippet ready for production code is a totally different thing, I would definitively use an environment with a proven CSV library for that, and all a lot of error handling as well – nohillside Jun 26 at 13:03
  • I try not to assume anything when it comes to code in an answer and whether or not it a "one-off solution" you should, under the circumstances, at a minimum include the -n option in the mv command. – user3439894 Jun 26 at 13:32
  • 1
    According to the file output in the question, the file (or at least the test file) has CR line endings rather than LF. It'll either need to be converted, or use read -t $'\r' to make it recognize CR endings. Also, I'd add a third variable argument to read to absorb anything past the second column. – Gordon Davisson Jun 26 at 18:44

You must log in to answer this question.

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