Automator - delete first column of CSV

Scripting Forums Automator
#1

I have to deal with lots of CSV files where the first column needs to be deleted before I import them into an online database.

Instead of doing this manually, I’d like to create a simple QUICK ACTION that does just that and overwrites the original CSV file.

I managed to do this in TERMINAL where the result gets saved as a new file:

[format]cut -d , -f 2- file.csv > file_new.csv[/format]

But how can I turn this into a simple QUICK ACTION that changes and overwrites whatever CSV file I am feeding it?

Thank you so much in advance! (Obviously, I know next to nothing about scripting…)

#2

Here’s a way to do it in AppleScript. There are various ways to use to use an appleScript in a Quick Action, but I’m not the one to help you with that. I’m hoping someone else will because I’d like to see how it’s done!

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
tell application "Finder"
	set csvFiles to the selection as alias list
	repeat with thisCSVFile in csvFiles
		set fileName to name of thisCSVFile
		set fileExtension to name extension of thisCSVFile
		if fileExtension = "csv" then
			set newCVSFile to my StripCSVFirstColum(thisCSVFile, fileName)
		end if
	end repeat
end tell


on StripCSVFirstColum(myFile, fileName)
	set saveTID to AppleScript's text item delimiters
	set AppleScript's text item delimiters to {"new.csv", ".csv"}
	set newFilePath to text items of (myFile as text) as text
	set AppleScript's text item delimiters to saveTID
	set myFile to POSIX path of myFile
	set shellScript to "cut -d , -f 2- " & quoted form of myFile
	set cvsText to do shell script shellScript
	
	try
		set openFile to open for access file newFilePath with write permission
	on error errMsg number errNum
		close access file newFilePath
		set openFile to open for access file newFilePath with write permission
	end try
	set eof of openFile to 1
	write cvsText to openFile
	close access openFile
	return newFilePath
end StripCSVFirstColum
#3

Thank you so much, estockly. I very much appreciate you helping me out here.

I do know how to use an AppleScript in Automator. But there is another problem.
Your script produces an Automator -212 error. And when I run just the script it tells me:

[format]Syntax Error. Expected “end” but found “on”.[/format]

It’s referring to this line:

[format]on StripCSVFirstColum(myFile, fileName)[/format]

Do you know what that means?

Thank you again!

#4

That is odd. That is usually a compile error that comes up when there’s a tell block, repeat block or if/then block without an end tell, end repeat or end if following.

Try putting the word “end” in a line by itself above the On StripCSVFirstColum… line and see if that fixes it.

Otherwise I’ll defer to those who do more than dabble in Automator.

#5

FWIW, the cut command must output to a different file (or stdout). But it is possible to work with that new file in various ways.

For example:

% cut -d , -f 2- file.csv > newfile.csv; mv newfile.csv file.csv

The part after the semi-colon moves the newly created file into the original file, essentially replacing the source file with the resulting file. It’s sort of using ‘newfile.csv’ as a temp file. As I understand it, the semi-colon tells the shell to run the subsequent command after the prior command is complete. I assume it will work in the shell that automator uses but I haven’t tested it. There are non-obvious concerns to using the shell in automator (or applescript) so be as aware as you can be.

I have no idea if you need to be more fulsome with file paths in order to make sure that you are working with the correct file. So don’t go testing it with a filename that you use for other purposes.

I have an old system so I don’t see what ‘quick actions’ are but if they’re anything like a ‘service’, then when you create something new in automator, it should prompt you. Hope this helps.