Can KM Find/Replace text in CSV file (and maybe delete columns)?

[Evan_Pike]

Hello all! I just found KM yesterday so super new user. I'm on Mac 10.15.17 and KM 9.2

I'm a photographer and I get an individual export for each order as a CSV file. This file goes from Column A to Column AX, I barely need any of these columns for the final result which will be a CSV file that I import into a photography printing lab software for ordering. Here are screen shots of a sample.

Here is what the final file looks like:

Here is how I get there:
I open this file in Numbers and then proceed to delete all the columns I don't need. Then, for the print sizes, I find/replace the different sizes so that 3.5x5 gets replaced by the package definition in my lab software which is th-1. I need to do this for 4x6, 5x7, 8x10, 11x14 and 4 wallets. I also copy and paste the shipping address to every row so my lab knows these are all part of the same order. ROES is the software I use if that matters.

After that, I use the csv file to data match images to print sizes for the order.

If possible, I'd love to automate this so the columns I don't need are deleted but at minimum, I guess I just need the sizes (5x7) replaced with the package definitions (fi-1) for my lab.

Also, in case i'm on the right track, I tried to create my first macro to replace the sizes with package definitions but when I execute it, the first cell is blank and messes up the matching.

What I created:

Before:

After:

Thank you in advance for any help.

[ccstone]

Hey Evan,

Please post a representative sample file and the result of the same file processed to the desired outcome.

I.E. Before & After Samples.

If we're not testin' we're guesin', and guesin' often wastes everyone's time.

-Chris

[MartinPacker]

I would consider python - with its CSV support - as a robust way of cutting the data down.

[Evan_Pike]

Thank you for the reply Chris and of course! I have no frame of reference how difficult this is. If it's super complicated, just a way to automate replacing the products (5x7, 8x10....) would be awesome.
KM samples.zip (2.6 KB)
Thank you again!

[drdrang]

The answer to the title of your question is yes, but that's mainly because Keyboard Maestro allows you to embed other scripting languages. If I were doing this for myself, I'd follow @MartinPacker's advice and use Python. An AppleScript solution would also be feasible, because Numbers has pretty good AppleScript support. Other scripting languages can handle CSV files, but I know less about them.

I have two questions:

1. Is your goal to produce a CSV file like the one you uploaded? Is that the type of file you need to send to the lab? Is it OK if there's no spreadsheet ever involved? (OK, that's three questions, but they're related.)
2. Does your input CSV file always have just one entry for the name and address fields, and are the filled-in fields always in the first row?

[Evan_Pike]

Hello and thank you for your message! I don't know anything about python or programming really, so I am not even sure what to say about that or where I would start.

1a Yes, that is my goal but also just replacing the print sizes to package definitions only would be a huge time saver for me.
1b and c It needs to be a csv that is sent but if it's a different type of file that can be easily converted to a csv, for example a text file that is tab delimited.

2. yes csv always one entry for the name/address and yes it's always the first row.

[ccstone]

@Evan_Pike

If it's super complicated …

Hey Evan,

That statement is relative.

The task is NOT very complicated for me, but for a newcomer to Keyboard Maestro and programming concepts it's a tall order.

Your 01 KM Sample Before.csv file doesn't look properly representative of the real problem, but let's start with that.

• Import the appended macro.
• Make sure it and the macro group it's in are enabled.
• Select the 01 KM Sample Before.csv file in the Finder.
• Run the macro.

I'm taking advantage of the fact that Keyboard Maestro understands comma-delimited arrays.

You'll get a dialog with the result.

-Chris

---

Extract CSV Information From The Selected CSV File in the Finder v1.00.kmmacros (13 KB)

Macro-Image

[drdrang]

Ah, @ccstone beat me to it! I went with a Python solution that mostly matches the input and output @Evan_Pike posted. Here's a screenshot:

and here's the macro itself:
Photo order converter.kmmacros (5.0 KB)

To use it:

1. Select all the CSV files you want to process in the Finder.
2. Run the macro (I've left it to you to choose the trigger).

New CSV files will appear in the folder where the original CSV files are. They will have the same name as the originals, but with a "-converted" suffix.

I made a couple of interpretations:

1. I included the Address2 column in the output. Seemed like might be useful; if not, you can remove it easily enough by editing one like of the script.
2. I left out all the rows that didn't have a ProductName of "5x7", "3.5x5", "8x10", "11x14", or "4 Wallets". This removes some rows that might be needed (although you did remove all these rows in your example).

Generally speaking, I don't like posting a script this long in a language the OP doesn't know, but Python is relatively easy to read, and I did my best to explain everything in the comments. Good luck!

[ccstone]

Here it is in 14 lines of AppleScript just for fun.

tell application "Finder"
   set finderSelectionList to selection as alias list
   if length of finderSelectionList = 0 then error "No files were selected in the Finder!"
   set theItem to item 1 of finderSelectionList
end tell
set csvTextList to paragraphs of (read theItem as «class utf8»)
set AppleScript's text item delimiters to ","
set csvNewList to {}
repeat with csvLine in csvTextList
   set csvNewLine to {item 5, item 6, item 7, item 9, item 11, item 12, item 43, item 47} of (get text items of csvLine)
   set end of csvNewList to csvNewLine as text
end repeat
set AppleScript's text item delimiters to linefeed
return csvNewList as text

I wouldn't write it this way for real use, but it'll do for proof-of-concept.

-Chris

[rlivingston]

I am more familiar with Excel but I believe that Numbers offers similar functionality.
This is for the task of looking up f1-1; wa-1 etc. from the inputs of 3.5x5; wallets, etc.

Look at the documentation for VLOOKUP.
You can put a little table somewhere on your spreadsheet of
3,5x5 | f1-1
8x10 | ei-1
etc.
This little table has all the input values in the left column and the output values in the right columns. Using the VLOOKUP function in Numbers you should be able to create a column that contains the "looked-up" values that could lie adjacent to the original input values column.

[rlivingston]

Deleting columns:
If you can figure out how to do a task in Numbers using the keyboard then in general it is easy to create a Keyboard Maestro script to do the same thing.

Again I do not have Numbers but I will use Excel as an example and it is very likely that in Numbers you can do the same thing.

Lets say I want to delete the first four columns in Excel.
I click on the header (A) of the first column and then I can do the rest with the keyboard
Shift-RightArrow
Shift-RightArrow
Shift-RightArrow
Delete

Now it is easy to write a little KM script that consists of those 4 keystrokes. Now the task is done. As the user, you only have to invoke the KM script - perhaps with a single Keystroke.

Now with Excel, the Delete key does not make the columns disappear. To make the columns disappear, you need the command Delete which is under the Edit menu. KM will allow you to specify that you want to invoke a menu command.

Edit>>Delete

So if you want the columns to actually disappear (which seems likely from your example) you could set up your script to do this.

Shift-RightArrow (KM Command: Type a Keystroke)
Shift-RightArrow "
Shift-RightArrow "
Edit>>Delete (KM Command: Select or Show Menu Item)

I do not want to get too specific because the details are likely different in Numbers. The basic point is that if you are doing a repetitive task (ANY TASK) that is "formulaic" you can do the same task with KM. You just record/figure out all the steps that you do and figure out how to tell KM to do the same thing.

It is easy just to proceed slowly. Rather than jumping in with Python to write a script to accomplish the task, I would recommend just experimenting with KM and with the functions of Numbers it self to see what you can do. Once you get some little piece of your workflow automated and it seems stable, then you can move onto the next thing. In this way you gradually build up a familiarity with KM and gradually improve your workflow. And in this way, you start to see other areas of your life that can be improved with KM.

P.S. When you are trying to automate some task that you do repeatedly it is helpful to figure out just how much of the task can be done just using the keyboard because setting up KM scripts to control the keyboard are easy. Often it is worthwhile to look in the disability settings for ways to control more of an app simply from the keyboard.

P.P.S It is easy for KM to get ahead of itself. It can type keyboard commands faster than any human and can overwhelm the app. It can be necessary to issue the Pause commands between steps so the app has a chance to catch up, particularly if the task that you are invoking is a little time-consuming.

[Evan_Pike]

Wow, thank everyone VERY VERY much! I will try these out and surely be able to get one (all?) to work. Besides multiple solutions I appreciate the tips of advice.