I have a csv file that is a single column list of phrases. I need to add a Column 2 and a Column 3 with the same values as Column 1. So I need to transform:
One
Two
Three
into:
One, ,One
Two, ,Two
Three, ,Three
I would like to automate this KM, a script, or ?
Thanks, Automators!
If you’re doing this as a one off, import the CSV into our favourite spreadsheet app, copy and paste the first column into the third an then save it out as a CSV.
If you have a multi caret text editor like Sublime Text 3, select all, split the cursor across all lines, copy, jump to the end of the line, type two commas, hit paste and then save.
f you need to do this frequently I’d suggest using a script. Take a look at this posting:
A similar request and one that you can probably adapt without too much effort.
As the post indicates, if you are dealig with CSVs, you can easily get away with a pure BASH solution. If you are dealing with large files, AWK is absoutely your best option. This sort of thing is literally what it was built to do. It just might take a bit more time to get your head around it. But when you do it gives you a world of power (AWK manual by GNU).
Yes, I was looking for a scripting option, but I just couldn’t work it out with any of the options I found, including awk. I think it had to do with the source file, but I don’t have the skills to go any further so I found a less elegant solution on the file creation side. Thanks for your help.
Just in case it helps in future, here’s how I did it using awk and an alternative approach using sed.
awkawk -F "," '{print $1",,"$1}'
sedsed -E 's/(.*)$/\1,,\1/'
Hey thanks for sticking with me. I am actually really glad that you shared these two methods and screenshots because it confirms for me there is an issue with the source file. “cat original.csv” does not return the file contents. No error. Just blank prompt. However, if I open the file in BBEdit or Excel and save it with no changes, cat does return the contents in terminal The file is generated from a keyboard maestro macro, saved as a plain text file with csv extension. So there’s something going on on that end. At least I learned a little about pattern substitution along the way.
Any chance you could share an example file? I might be able to figure out what’s up with it. From that, there may be an automatable step to get the file into a processable state.
On a hunch, given a direct cat of the file content yields nothng (… or perhaps a blank line?), does the following command output anything useful?
tr '\r' '\n' < original.csv
I’m wondering if the line endings might be causing only the last line to be displayed, which may be a blank line.
I can’t check right now, but I’ll get back to you soon. Interestingly, I filtered the results that are written to the file in KM in order to give you a sample file instead of the long list with some private info (I didn’t want to edit it outside of the macro because that fixes the issue). Anyway, I filtered the results to items starting with “w” and the resulting list does work fine. So it seems the filtering may have filtered out the bad apple FWIW. Anyway, I’ll try your command and let you know.
Thanks for being so helpful!
That seems to do the trick. I tried replacing the carriage returns with line breaks at some point, but I probably did it wrong. Thanks so much.
Funny thing is the discovery that simply filtering the results in KM before writing to the file (leaving the filter field blank) solves the problem as well.
I always feel a lot better after getting to the bottom of a problem and getting things working.
Another option would be the paste command. It joins files horizontally (as opposed to vertically like cat)
paste file1 file2 file3
will take lines from each of the three files with a tab in between. You can change the delimiter
paste -d, file1 file2 file3
this will separate them using commas.
I just tested using the same file three times and it worked fine
Sorry, I just realised that I was solving a different problem and you wanted two commas in between two copies of the file.
I don’t think paste will do this. If you try
paste -d,, file1 file1
you just get a single ‘,’ in between because paste allows multiple delimiters
e.g. paste -d,- will put a ‘,’ between the first two entries and ‘-’ between the second and third
