Hey guys, as there are several Excel to Website posts, I figured I'd ask for help here. I would like to save the entire cell contents for every row to a variable, and use those variables to fill out a web form. After each row, the script would run again with the new variables until the end. I am able to do the web form part ok, but I just haven't found a good post to help me accomplish Excel cell contents -> KM Variable per row.
Here's an example of the variables I'd need, in this case the script would run twice as there are two rows of variables.
Since this is more about extracting data from Excel, I've moved it to a new topic.
When posting such requests please provide a proper data sample for testing – e.g paste-able text or a zipped Excel file. Paste-able should be fenced like so:
It will render like this and won't have any funky artifacts:
Customer Product Type Name Weight Cost
Ronald’s Toys Ball BounceyBall 4 2
Toys Express Stuffed Animal Lion 25 7
In case it isn't obvious a zipped Excel file is the better choice of the two.
Here's how to take the selection in Excel into a Keyboard Maestro variable as a tab-delimited list of records:
tell application "Microsoft Excel"
set selValList to value of selection
end tell
set AppleScript's text item delimiters to tab
repeat with i in selValList
set contents of i to (contents of i) as text
end repeat
set AppleScript's text item delimiters to linefeed
set selValList to selValList as text
From there you can easily use a For Each Action to loop through each line/record.
Hey Chris, do you happen to have an example of how you'd get the variables out using a For Each loop? I'm thinking I need to take a regex class if that's the solution!
@jayknowstheway, here's an example macro created by using the other example as a starting point. I have also revised the RegEx to make it very easy to add/remove fields: (?m)^([^\t]+)\t([^\t]+)\t([^\t]+)\t([^\t]+)\t([^\t]+)$
Each field (Excel cell) is represented by this sequence: ([^\t]+)
followed by either a TAB \t or End of Line $ metacharacter.
So the easiest way to change is at the beginning of the RegEx pattern.
To add more fields, insert([^\t]+)\t immediately after (?m)^
To remove fields, delete([^\t]+)\t immediately after (?m)^
Chris @ccstone gave you a good workable macro, but I have taken a different approach that does not require any AppleScript; and I have added in the For Each Action loop KM Actions Set Front Browser Form Field for each of the Web Form Fields. You will need to change each of these to select the action form field. You can just click on the button to show a popup list of form fields.
The entire GROUP of form fields, and submit button, is disabled. After you change the form field Actions, you need to ENABLE this Group Action.
Questions?
Here's my example macro:
Example Output
MACRO: Use Excel to Fill Chrome (or Safari) Form Fields [Example]
Wow you are the greatest, I modified your macros and it got me exactly where I needed to be, this is a huge breakthrough for me!! Thank you so much! Both you and ccstone showed me great macro design techniques too. I am going to enjoy this weekend now and hope you have a blast as well
I do have an issue however, when cells contain no data. When encountering a cell with no data I would expect the script to return nothing for that variable, and then move to the next.
However, it actually misses it out, and then returns the next cell as the variable.
Example being Excel sheet containing the following data:
Parsing text is tricky stuff, and the OP did not specify that there might be empty cells – nor was I paid for my work, so this simple example falls short of professional standards.
Can we just thank cc for being such a superstar thank you, you definitely pushed my career to the next level. I didn't use KM for by projects but it helped me get into coding. You were a key part of my journey.
Post a testable data sample in the form of a zipped Excel worksheet – as well as the desired outcome in the form of a preformatted text block – and I'll take a look.
button to show a popup list of form fields.
