Automator and/or script editor for finding and replacing text in a textedit document

[laramwol]

Hello! This is my first post! I'm hoping to get some help with automating a workflow inside a TextEdit document.

I help out with billing for the company I work for. And I'm able to generate a list of customers who are past due from Braintree. But the report needs to be made into a proper csv file and it can't be exported the way I want. My steps would be the following...

1) Generate the report from Braintree and copy the list of customers past due.

2) Paste what I copied into a TextEdit document (as text, not rich text format).

---The following is where I need some automation ---

3) Search for all line breaks and replace with commas.

4) Search for all tabs and replace with "nothing". That is no characters. Just remove the tabs.

5) Each customer listing conveniently ends with the name of the company I work for so, to create a line break for each individual customer's listing I'll need the automation to find...
,companyname,
and replace with
,companyname[line break]

I'm handy enough to do all the above manually. But it would be nice to have a script that would allow a co-worker to take the TextEdit document, drop it into the script, and automatically have the document turn into a csv file with all the commas and line breaks in the correct places.

Would anybody be able to help, please? Thanks in advance!

 

[Cory Cooper]

Hello and welcome.

I am not very well-versed with Automator or AppleScript, but well see if we can find a solution.

A few troubleshooting questions for you:
-Which version of OS X/macOS?
-I am not familiar with Braintree - is it the PayPal service - Braintree Payments?
-What format is the report generated and downloaded in?

C

 

[laramwol]

Cory,

Thank you! I appreciate your offer to help!

In answer to your questions...

- MAC OS Sierra

- BrainTree is owned by PayPal. It’s basically a web based billing system. The company I work for uses it to charge our customers for a service to which they subscribe.

- The report is generated from BrainTree on their web based platform via an html table. But simply exporting the table as a csv is not as helpful as you’d think. This is because some of the data I want in distinct fields are stuffed into to same columns (this is what causes the tabs when I copy and paste the data into a text file).

Please let me know if you have any more questions.

And, again, thank you for taking the time to look into this!

 

[Cory Cooper]

OK, thanks.

Have you tried opening the downloaded CSV file with Numbers or Excel? You may have flexibility to control multiple delimiters.

C

 

[laramwol]

Cory,

Thanks for the suggestion! But it turns out I'm wrong about being able to export a CSV from the Braintree report. I can only copy and paste from the html table.

 

[Cory Cooper]

OK, thanks.

Copying and pating HTML tables can be quite tricky. I think it may be wise to contact BrainTree to see if they have any suggestions on how to download any reports from their site.

C