Problems automating combining CSV files with embedded bash script in Hazel

This may not be the best forum to ask this question about Hazel and the bash script. Happy to put elsewhere if anyone has suggestions

I am pretty new to bash scripting but have watched several Lynda.com courses recently and figured out what I want to do in the terminal but now want to automate with Hazel. Likely could do in the terminal but I’m not that advanced. But willing to consider if people feel its best

I am receiving web based customer data from a web form in a CSV file. The headers for the data are in column 1 and customer input in column 2. Each customer input is a separate CSV file

I want to automatically combine each CSV file so I can dashboard some of the data. Ultimately hope to display with the Charty App.

In the terminal, I have used awk to extract the headers and place them in row 1 of a new file ill refer to as combined.csv

I have created another awk command to extract column 2 ( the customer data) and put it in a row separated by commas. The command I’m using is: awk ‘BEGIN {FS=“,”}{RS=“\r”}{ORS=“,”}{print $2}’ file_name.csv >> combined.csv

One issue I had is this places the new data on the same row as the headers which I can’t figure out how to overcome. I can make my combined not have the headers if necessary but sure there is a way to fix.

Next, I decided to automate with Hazel. I have pointed it folder where the individual CSV files get placed. They have no tag so I want Hazel to identify the files with no tag and run an embedded bash script

awk ‘BEGIN{FS=","}{RS="\r"}{ORS=","}{print $2}’ $1 >>

filepath Combined.csv

In this reportedly the $1 is how you denote the current file Hazel is working on.

After running I would like Hazel to tag the file with “complete” so it won’t be triggered again.

Hazel reports back an error saying it can’t run the shell script.

Sorry for the long post. Any help would be appreciated.

I can’t help you with Hazel, but I might be able to help with the shell stuff.

First, with ORS set to a comma, awk is ending each command ouput with a comma instead of a newline character (\n). So when you >> the next output, it goes onto the end of your combined file. You could pipe the awk output through sed to fix this. To get the header line, run

awk 'BEGIN{FS=",";ORS=","}{print $1}' cust1.txt | sed 's/,$//' > combined.csv

And then for each of the data files, run

awk 'BEGIN{FS=",";ORS=","}{print $2}' custN.txt | sed 's/,$//' >> combined.csv

where custN.txt represents any customer file.

You’ll note that I’m not including RS="\r" in my code. That’s because I don’t understand why it’s in your code. Mac files have lines that end with \n (and that’s what Unix commands expect), Windows files typically have lines that end with \r\n. No files have line endings of just \r, at least not since the classic Mac OS days. Now, you may well have good reason for including RS="\r", and it may be necessary because of the input files you’re getting from your customers. If that’s the case, keep it in there. It just seems weird to me.

By the way, I tested the above commands with a cust1.txt file of

A,1
B,2
C,3

and a cust2.txt file of

A,9
B,8
C,7

When I was done, combined.csv was

A,B,C
1,2,3
9,8,7

which I think is what you’re looking for.

Thanks…I will play with what you have sent.

The \r really only came from multiple search after i couldnt get my initial attempts to work. Since I am new to this I am certain your method is likley better.

Appreciate the help and will let you know how it goes

By the way Python has CSV support built in. I use it a lot. What you’re doing sounds like it’s getting complex enough to use Python instead of awk.

Just a thought.