1 Reply Latest reply on Jun 13, 2017 9:33 PM by Dirk Becker

    Saving an XML file with AppleScript

    mBornbach Level 1

      I'm working on automating our page layout process. I've created a script that gathers information from an Excel file and pastes that information in XML format into Text Edit. My question is how do I get TextEdit (or another program) to save an XML file that can be imported into InDesign? Right now, I have to copy and paste the xml information from my created xml file to Adobe's sample xml file from the page automation demo. Then, I can save that file with a new name. Otherwise, it won't import into InDesign. Clearly, I'm doing something wrong with the saving options. Any suggestions would be appreciated. Here's the code if it helps:

       

       

       

      --Global variables

      global xml_list

       

       

      ------------------------------------------------------------------------------------------ ---------

      --PROMPTING THE USER TO SELECT THE FOLDER IN WHICH TO SAVE THE WORD FILES

      ------------------------------------------------------------------------------------------ ---------

       

      with timeout of 3000 seconds

       

        --Prompting the user to select the promotion's Excel file

        tell application "Finder" to set my_file to choose file with prompt "Choose the Promo's Excel File"

        set excel_report to POSIX path of my_file

       

       

        ------------------------------------------------------------------------------------------ ---------

        --SPECIFYING WHICH EXCEL COLUMNS TO WORK WITH

        ------------------------------------------------------------------------------------------ ---------

       

        set blain_number_column to "A"

        set image_name_column to "B"

        set image_path_column to "D"

        set blain_to_pic_number_column to "F"

        set image_to_pic_name_column to "G"

        set image_to_pic_path_column to "H"

        set page_number_column to "J"

        set vendor_column to "K"

        set description_column to "L"

        set LL_column to "M"

        set notes_column to "N"

        set reg_price_column to "O"

        set sale_price_column to "P"

        set possible_page_number_list to {"1.0", "2.0", "3.0", "4.0", "5.0", "6.0", "7.0", "8.0", "9.0", "10.0", "11.0", "12.0", "13.0", "14.0", "15.0", "16.0", "17.0", "18.0", "19.0", "20.0", "21.0", "22.0", "23.0", "24.0"}

       

       

        ------------------------------------------------------------------------------------------ ---------

        --CREATING EMPTY LISTS TO BE USED LATER IN DETERMINING IF

        --A FILE IS OPEN OR IF THERE IS A PROBLEM

        ------------------------------------------------------------------------------------------ ---------

       

        set problem_list to {}

        set number_list to {}

       

       

        ------------------------------------------------------------------------------------------ ---------

        --ADDING XML INFO TO A LIST

        ------------------------------------------------------------------------------------------ ---------

       

        tell application "Microsoft Excel"

        activate

        open excel_report

       

        repeat with i from 1 to count of possible_page_number_list

        set test_page_number to item i of possible_page_number_list

        my add_to_page_list(test_page_number, blain_number_column, image_path_column, image_to_pic_path_column, vendor_column, description_column, reg_price_column, sale_price_column, notes_column, page_number_column, LL_column) -->Calling the add_to_page_list function

        log xml_list

       

       

        ------------------------------------------------------------------------------------------ ---------

        --ADDING ALL NECESSARY INFO FROM THE LIST TO EACH XML FILE

        ------------------------------------------------------------------------------------------ ---------

       

        --Checking if the list for that page # is blank and only creating the XML file if it contains information

        if xml_list is not ("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" & linefeed & "<Root>" & linefeed & "</Root>") as string then

       

        --Opening Text Edit and Creating the XML File

        tell application "TextEdit"

        activate

       

        set new_document to make new document

        set text of new_document to xml_list

        --save document 1 in "/Volumes/NAS/Advertising Department/16_SCRIPTS/z_Future Scripts/Print Ad Layout/XML scripted/XML 4/XML_Testing.xml"

       

        end tell

        end if

       

        end repeat

        end tell

      end timeout

       

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

      --FUNCTIONS

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

       

       

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

      --FUNCTION TO ADD XML INFO FOR EACH PAGE TO A LIST

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

       

      on add_to_page_list(the_number, column1, column2, column3, column4, column5, column6, column7, column8, column9, column10)

       

       

        --Adding standard xml code to the top of the xml file

        set xml_list to "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" & linefeed & linefeed & "<Root>" & linefeed as string

       

       

        --Determining how many rows the Excel file has

        tell application "Microsoft Excel"

        tell active sheet

        set image_count to 1

        set the_start_row to 2

        repeat with i from 1 to count of rows of used range

        set current_row to i

       

        --Determining the last used row number

        set row_count to count of rows of used range

       

        --Determining the last column

        select none

        set row_to_test to range "1:1"

        set cell_count to count cells of row_to_test

        set last_cell to get address of (get end cell cell_count of row_to_test direction toward the left)

        set first_cell to get address of (get end cell cell_count of row_to_test direction toward the right)

        if item 1 of last_cell is "$" and item 3 of last_cell is "$" then

        set last_column to item 2 of last_cell

        else if item 1 of last_cell is "$" and item 3 of last_cell is not "$" and item 4 of last_cell is "$" then

        set last_column to items 2 thru 3 of last_cell

        else if item 1 of last_cell is "$" and item 3 of last_cell is not "$" and item 4 of last_cell is not "$" and item 5 of last_cell is "$" then

        set last_column to items 2 thru 4 of last_cell

        else if item 1 of last_cell is "$" and item 3 of last_cell is not "$" and item 4 of last_cell is not "$" and item 5 of last_cell is not "$" and item 6 of last_cell is "$" then

        set last_column to items 2 thru 5 of last_cell

        end if

       

       

        --Setting the cell address for each info field

        set blain_number_cell to column1 & current_row as string

        set image_path_cell to column2 & current_row as string

        set image_to_pic_path_cell to column3 & current_row as string

        set vendor_cell to column4 & current_row as string

        set description_cell to column5 & current_row as string

        set reg_price_cell to column6 & current_row as string

        set sale_price_cell to column7 & current_row as string

        set notes_cell to column8 & current_row as string

        set page_cell to column9 & current_row as string

        set previous_page_cell to column9 & (current_row - 1) as string

        set current_LL_cell to column10 & current_row as string

        set test_LL_cell to column10 & (current_row + 1) as string

        set current_LL to value of cell current_LL_cell

       

       

        --Setting the cell address for the next info field in case it is a Lower Level (LL)

        set LL_row_list to {}

        set test_LL to value of cell test_LL_cell

        if test_LL contains "LL" then

        repeat

        set next_row to (current_row + 1)

        set LL_row_list to LL_row_list & next_row

        set test_LL_cell to "L" & next_row as string

        set next_test_LL_cell to "L" & (next_row + 1)

        if the value of cell next_test_LL_cell does not contain "LL" then

        exit repeat

        else

        set current_row to (current_row + 1)

        end if

        end repeat

        end if

       

       

        ---------------------------------------------------------

       

       

        --Getting the values from every cell address

        if current_row is greater than or equal to the_start_row then

        set page_number to value of cell page_cell

        if (page_number as string) does not contain "0.0" then

       

        set the_vendor to value of cell vendor_cell as string

        set the_description to value of cell description_cell as string

        set reg_price to value of cell reg_price_cell as string

        set sale_price to value of cell sale_price_cell as string

        set the_notes to value of cell notes_cell as string

        set orig_blain_number to value of cell blain_number_cell as string

        set blain_number to my number_to_string(orig_blain_number)

       

       

        ---------------------------------------------------------

       

       

        --REMOVING ILLEGAL XML CHARACTERS IN THE DESCRIPTION & FIXING THE IMAGE PATH

        --Changing illegal # characters in the description to ones xml can understand

        (*if the_description contains "#" then

        set search_character to "#"

        set replacement_character to "&#35;"

        repeat 5 times

        set the_description to my replace_characters(the_description, search_character, replacement_character) -->See replace_characters function at the bottom

        if the_description does not contain "# " then exit repeat

        end repeat

        end if*)

       

       

        --Changing illegal & characters in the description to ones xml can understand

        if the_description contains "&" then

        set search_character to "&"

        set replacement_character to "&amp;"

        repeat 5 times

        set the_description to my replace_characters(the_description, search_character, replacement_character) -->See replace_characters function at the bottom

        if the_description does not contain "& " then exit repeat

        end repeat

        end if

       

       

        --Changing image path so it doesn't have double // after the 1_4c_IMAGES folder (the // breaks the xml image import)

        set image_path to value of cell image_path_cell

        if image_path contains "1_4c_IMAGES//" then

        set search_character to "1_4c_IMAGES//"

        set replacement_character to "1_4c_IMAGES/"

        set the_description to my replace_characters(the_description, search_character, replacement_character) -->See replace_characters function at the bottom

        end if

       

       

        --Changing image path so it doesn't have double // after the 1_4c_IMAGES folder (the // breaks the xml image import)

        if the_notes contains "DESIGNER NOTE: " then

        set tid to AppleScript's text item delimiters

        set AppleScript's text item delimiters to "DESIGNER NOTE: "

        set my_notes to last text item of the_notes

        set AppleScript's text item delimiters to tid

        else if the_notes is "" then

        set my_notes to ""

        else

        set my_notes to the_notes

        end if

       

       

        ---------------------------------------------------------

       

       

        --ADDING ALL NECESSARY INFO TO THE CURRENT PAGE LIST

        --Getting the large sale price and the cents

        set tid to AppleScript's text item delimiters

        set AppleScript's text item delimiters to "."

        set large_sale_price to first text item of sale_price

        set sale_price_cents to last text item of sale_price

        set AppleScript's text item delimiters to tid

       

        --Adding all the text info to the list

        if (page_number as string) is the_number then

       

        set xml_list to xml_list & "<body><body_price><large_price>" & large_sale_price & "</large_price><small_price>" & sale_price_cents & "</small_price><kerning> </kerning><sale_wording>SALE</sale_wording>" & linefeed & "<heading>" & the_vendor & " " & the_description & "." & "</heading><blain_number>" & " " & blain_number & "</blain_number><reg_price> Reg. " & reg_price & "</reg_price></body_price>" & linefeed & "</body>" & linefeed as string

       

        if the_notes is not "" then set xml_list to xml_list & "<disclaimer> " & my_notes & "</disclaimer>" & linefeed as string

        --Adding the image info to the list

        set xml_list to xml_list & "<image" & image_count & "a href=\"file://" & image_path & "\"></image" & image_count & "a" & ">" & linefeed as string

        set image_count to (image_count + 1)

        set image_to_pic_path_orig to value of cell image_to_pic_path_cell as string

        if (image_to_pic_path_orig as string) is not "N/A" and (image_to_pic_path_orig as string) is not equal to image_path then

        if image_to_pic_path_orig contains ", " then

        set tid to AppleScript's text item delimiters

        set AppleScript's text item delimiters to ", "

        set image_to_pic_paths to every text item of image_to_pic_path_orig

        set AppleScript's text item delimiters to tid

       

        else

        set image_to_pic_paths to image_to_pic_path_orig

        end if

       

        if image_path is not "" then

        set image_count to "2"

        else

        set image_count to "1"

        end if

        repeat with image_to_pic_path in image_to_pic_paths

        set xml_list to xml_list & "<image" & image_count & " href=\"file://" & image_path & "\"></image" & image_count & ">" & linefeed as string

        set image_count to image_count + 1

        end repeat

        else

        set xml_list to xml_list & linefeed as string

        end if

        else

        set xml_list to xml_list

        end if

       

        end if

       

        end if

        end repeat

       

        set xml_list to xml_list & "</Root>"

        log xml_list

        end tell

        end tell

      end add_to_page_list

       

       

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

      --FUNCTION TO CHANGE EXCEL SCIENTIFIC NOTATION TO REAL NUMBERS

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

       

      on number_to_string(this_number)

        set this_number to this_number as string

        if this_number contains "E+" then

        set x to the offset of "." in this_number

        set y to the offset of "+" in this_number

        set z to the offset of "E" in this_number

        set the decimal_adjust to characters (y - (length of this_number)) thru ¬

        -1 of this_number as string as number

        if x is not 0 then

        set the first_part to characters 1 thru (x - 1) of this_number as string

        else

        set the first_part to ""

        end if

        set the second_part to characters (x + 1) thru (z - 1) of this_number as string

        set the converted_number to the first_part

        repeat with i from 1 to the decimal_adjust

        try

        set the converted_number to ¬

        the converted_number & character i of the second_part

        on error

        set the converted_number to the converted_number & "0"

        end try

        end repeat

        return the converted_number

        else

        return this_number

        end if

      end number_to_string

       

       

       

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

      --FUNCTION TO REPLACE CHARACTERS

      ------------------------------------------------------------------------------------------ -----

      ------------------------------------------------------------------------------------------ -----

       

      on replace_characters(the_phrase, search_string, replacement_string)

        --Changing illegal xml characters to ones xml can understand

       

        set tid to AppleScript's text item delimiters

        set AppleScript's text item delimiters to search_string

        --set item_count to count of text items of the_phrase

        set text_items to text items of the_phrase

        set AppleScript's text item delimiters to replacement_string

        set the_phrase to text_items as Unicode text

        set AppleScript's text item delimiters to tid

        log the_phrase

       

        return the_phrase

      end replace_characters