I'm attempting to do the following from the Terminal Command Line:

Convert text file (allMovement_Regressors.txt; 12 columns; 548 rows) in each subfolder of a Directory into csv files stored within the same subfolder. This code was tried, but only gives me one file that stacks each dataset on top of one another:

cat */*/*/*/allMovement_Regressors.txt | tr -s '[:blank:]' ',' > ofile.csv

Directory structure:

/Volumes/My\ Book\ Studio/Directory/1/Results/allMovement_Regressors.txt
/Volumes/My\ Book\ Studio/Directory/2/Results/allMovement_Regressors.txt
/Volumes/My\ Book\ Studio/Directory/3/Results/allMovement_Regressors.txt

Concatenate all csv files across each subfolder into one csv file, where incremental data is concatenated to the right of the previous dataset, and columns are named according to the subfolder name and variable order, as indicated below:

1_1 1_2 1_3 1_4 1_5 1_6 1_7 1_8 1_9 1_10 1_11 1_12 2_1 2_2 2_3 2_4 2_5 2_6 2_7 2_8 2_9 2_10 2_11 2_12 3_1 3_2 3_3 3_4 3_5 3_6 3_7 3_8 3_9 3_10 3_11 3_12 

Thank you very much.

  • Please use find with -exec instead of that unholy cat command. – Panki Oct 7 '19 at 13:32
  • what is unholy with that cat command? As long as there is no problem with max args, there is no problem with that cat. – pLumo Oct 7 '19 at 14:05

To create the individual csv-files:

$ find /Volumes/My\ Book\ Studio/ -type f -name 'allMovement_Regressors.txt' \
-exec bash -c 'tr -s "[:blank:]" "," < "$1" > "${1%.txt}.csv"' tocsv {} \;

The "tocsv" will show up in your processlist as the name for the bash-script in the exec.

If you know the number of directories with 'allMovement_Regressors.txt', you can write the header-line like this (replace X with the correct number):

printf '%s,' {1..X}_{1..12} | sed 's/,$/\n/' > ofile.csv

And to put them together:

$ find /Volumes/My\ Book\ Studio/ -type f -name 'allMovement_Regressors.csv' -print0 \
| sort -z \
| xargs -0 paste \
| tr '\t' ',' >> ofile.csv

Note that the files will be numerically sorted before pasteing them together. I'm using -print0 here because you have spaces in your filenames. paste will separate columns with a tab-character that you don't want in your csv, so we need to replace that as well.


@bu5hman answered how to create the headline. I'm totally stealing this! Be sure to upvote @bu5hman's answer if you like mine :-)

Merge both our answers write a script thingy.sh that you can feed to -exec:

t=$(echo "$1" | grep -Po "(?<=/)[0-9]+(?=/)")
u=$(echo 1_$(seq -s " ${t}_" $(cat "$1" | awk "{print NF}")))

sed "i \\$u" "$1" \
| tr -s "[:blank:]" "," > "${1%.txt}.csv"

Then feed it to -exec:

find Volumes/My\ Book\ Studio/ -type f -name 'allMovement_Regressors.txt' \
-exec /path/to/thingy.sh {} \;

And then put things together with my second find-command.

  • Thanks very much for this. What do you mean by "numerically sorted." Will they be pasted horizontally in the vertical order they're in in the folder structure? – unix_person Oct 7 '19 at 15:57
  • 2. For the printf line, is there a manageable way to pull in the subfolder name into the X variable? – unix_person Oct 7 '19 at 15:57
  • 1: yes. 2: I'm pretty certain there is, I just didn't have an elegant idea yet. awk -F ',' '{print NF}' ofile.csv would show the number of columns. – markgraf Oct 7 '19 at 16:04
  • So awk -F ',' '{print NF/12 }' ofile.csv should be the number of files, thus "X", and we would need to inject that as line 1 with sed. Or something like that. – markgraf Oct 7 '19 at 16:16

Since the aggregation of files is fairly well answered above, how about the following to inject headers into each file before aggregating. Uses the directory which is only a number as the label and allows for any number of fields.

Remove the -i from sed to test.....

find ./tmp -type f -name "qwerty" -exec sh -c 't=$(echo "$1" | grep -Po "(?<=/)[0-9]+(?=/)"); u=$(echo ${t}_$(seq -s " ${t}_" $(cat "$1" | awk "{print NF}"))); sed -i "i \\$u" "$1"' sh {} \;

File at /home/bu5hman/tmp/1/end/qwerty


q w e r t y u i o p


1_1 1_2 1_3 1_4 1_5 1_6 1_7 1_8 1_9 1_10
q w e r t y u i o p

Of course the output can be redirected to a group of temporary files to preserve the originals.

  • Good one, but please quote "$1", OP has spaces in the path to his files. – markgraf Oct 8 '19 at 6:35
  • @markgraf Thx for the reminder. There was also another error in the first header as " 1_" now corrected to " ${t}_" – bu5hman Oct 8 '19 at 19:05

You want paste instead of cat:

paste -d' ' */*/*/*/allMovement_Regressors.txt | tr -s '[:blank:]' ',' > ofile.csv

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.