2

I'm trying to make a macOS service using Automator to extract PDF annotations and get the information into an Excel file.

I can successfully get the annotations from a PDF, and export them to a text file. But if I try to parse the same information to a new Excel file, all the information is pasted into a single cell.

What would be the correct steps in Automator, to have each text line in separated Excel rows?

0

Assuming you know the basics of Automator....Create a workflow with these Actions:

enter image description here

In the AppleScript Action copy & paste the following:

    on run {input, parameters}
    set delimitedList to paragraphs of (input as string)
    set myExport to ""
    do shell script "touch /tmp/myFile.csv"
    repeat with myLines in delimitedList
        set myLineExport to ""
        set AppleScript's text item delimiters to {"    "}
        set listItems to every text item of myLines
        repeat with eachItem in listItems
            set myLineExport to myLineExport & "\"" & eachItem & "\","
        end repeat
        set myExport to myExport & myLineExport & "
"
    end repeat
    write_to_file(myExport, (POSIX file "/tmp/myFile.csv" as alias), false)
    return POSIX file "/tmp/myFile.csv" as alias
end run

on write_to_file(this_data, target_file, append_data)
    try
        set the target_file to the target_file as string
        set the open_target_file to open for access file target_file with write permission
        if append_data is false then set eof of the open_target_file to 0
        write this_data to the open_target_file starting at eof
        close access the open_target_file
        return true
    on error
        try
            close access file target_file
        end try
        return false
    end try
end write_to_file

Note: You may run into issues if your annotations contain quotes, however there will be no problem if you use smart quotes.

You can select your pdf in the first prompt.


Sources: https://www.macosxautomation.com/applescript/sbrt/sbrt-09.html

  • Thanks JBis. The solution works but still having some issues. One is that each comment is filled in cells from left to right in different colums. Since the task is made by the script rather than Automator per se, I was trying to undestand how it works, but my modifications are making a mess. How can I make it to fill the info using rows (each comment in a new row), and each data type in columns (page, user_name, date, time, comment)? Also I’m getting wrong characters when the text contains á,é,í,ñ, etc. I’m seeing that this would be better accomplished by Applescript than Automator. Thx – George Jul 10 '18 at 16:02
  • @George Making the comments into rows is easy, but adding data type is possible, but difficult. I have idea. Let me see what I can do. – JBis Jul 10 '18 at 16:16
  • @George Done. Please upvote and mark as answered. – JBis Jul 10 '18 at 16:38
  • @George Please. – JBis Jul 12 '18 at 17:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .