(*
Veritrope.com
Evernote - Text File / CSV Processor
Version 1.03
October 29, 2013

// TERMS OF USE:
This work is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
To view a copy of this license, visit http://creativecommons.org/licenses/by-nc-sa/3.0/ or send a letter to Creative Commons, 444 Castro Street, Suite 900, Mountain View, California, 94041, USA.

// LIKE THIS SCRIPT?
If this AppleScript is helpful to you, please show your support here:
http://veritrope.com/support

// SCRIPT INFORMATION AND UPDATE PAGE
http://veritrope.com/code/evernote-csv-and-text-file-importer

// CHANGELOG:
1.03 -- Bug Fixes (Source URL now works correctly)
1.02 -- Bug Fixes (Quote Character as Last Character of Record)
1.01 -- Additional Bug Fix for Unicode Support
1.0 (FINAL) -- Improved Date Detection, Fix for Location Data, Added Check / Fix for delimiters contained within "Escaped" columns
1.0 (Beta 3) -- Bug Fix for Unicode support
1.0 (Beta 2) -- Added better Unicode support
1.0 (Beta 1) -- Initial Test Release

// RECOMMENDED INSTALLATION AND USAGE INSTRUCTIONS:
-- You'll want to review your file beforehand and make note of how
-- rows and columns are separated (i.e., what character delimiters are used)

-- Since CSV or Text files can have many thousands of data points, I'd recommend
-- starting slowly with a subset of data and importing it into a separate folder.

*)

(*
======================================
// USER SWITCHES (YOU CAN CHANGE THESE!)
======================================
*)

-- CHANGE THIS TO FALSE TO ELIMINATE VERBOSE INSTRUCTIONS
property show_Instructions : true

-- DELIMITERS THAT SEPARATE THE ROWS AND COLUMNS OF DATA
-- (YOU CAN ADD OR DELETE ITEMS BELOW)
property delim_List : {"Tab", "Return", ",", ";"}

(*
======================================
// OTHER PROPERTIES (USE CAUTION WHEN CHANGING)
======================================
*)

-- SCRIPTNAME FOR DIALOG BOXES
property script_Name : "Veritrope.com | Evernote CSV/Text File Importer"

-- MATCH COLUMN WITH EVERNOTE PROPERTY
property evernote_Props : {"(NONE)", "Note Title", "Note Body", "Tag", "Creation Date", "Source URL", "Longitude", "Latitude", "Altitude"}

(*
======================================
// MAIN PROGRAM
======================================
*)
-- RESET OF TEXT ITEM DELIMITERS
set AppleScript's text item delimiters to ""

-- SCRIPT DESCRIPTION
if show_Instructions is true then
    display dialog ¬
        "This application will attempt to create new Evernote items from data contained in a CSV or Text file.

You will now be asked a series of questions to help map the data to the appropriate Evernote item property.

Press "OK" to Continue" with title script_Name with icon path to resource "Evernote.icns" in bundle (path to application "Evernote")
end if

tell application "Finder"
    -- PICK FILE
    set exportedFile to (choose file with prompt "Choose CSV or TXT File To Import As Individual Evernote Items")
   
    -- CHOOSE NOTEBOOK
    set ev_Notebook to my pick_Notebook()
   
    -- DELIMITER INSTRUCTIONS
    if show_Instructions is true then
        activate
       
        display dialog ¬
            "In CSV or text files, individual records or row items are separated by a delimiter -- a character which serves as a boundry. Often times, this is a return or new line.

The next dialog will ask you how items in your CSV or text file are separated


Press "OK" to Continue" with title script_Name with icon path to resource "Evernote.icns" in bundle (path to application "Evernote")
    end if
    activate
   
    -- CHOOSE LINE DELIMITER
    set line_Delimiter to (choose from list delim_List with title script_Name with prompt "How Are Your Records / Row Items Separated?")
    if line_Delimiter is false then
        return
    else if (item 1 of line_Delimiter) is "Return" then
        set line_Delimiter to return
    else if (item 1 of line_Delimiter) is "Tab" then
        set line_Delimiter to tab
    end if
   
    -- DELIMITER INSTRUCTIONS
    if show_Instructions is true then
        activate
       
        display dialog ¬
            "In CSV or text files, columns or data items within each record are also separated by a delimiter. Often times, this is a comma or a tab.

The next dialog will ask you how columns or data items in your CSV or text file are separated


Press "OK" to Continue" with title script_Name with icon path to resource "Evernote.icns" in bundle (path to application "Evernote")
    end if
    activate
   
    -- CHOOSE COLUMN DELIMITER
    set col_Delimiter to (choose from list delim_List with title script_Name with prompt "How Are Your Columns Or Data Items Separated?")
    if col_Delimiter is false then
        return
    else if (item 1 of col_Delimiter) is "Return" then
        set col_Delimiter to return
    else if (item 1 of col_Delimiter) is "Tab" then
        set col_Delimiter to tab
    end if
   
    -- GET THE FILE CONTENTS
    set fileRef to open for access exportedFile
    set file_Contents to (read fileRef)
    close access exportedFile
   
    -- MAKE A LIST WITH EACH ROW OF DATA
    set note_Items to {}
    set old_Delims to AppleScript's text item delimiters
    set AppleScript's text item delimiters to line_Delimiter
    set note_Items to every text item of file_Contents
    set AppleScript's text item delimiters to old_Delims
   
    -- TEST ITEMS BEFORE PROCESSING
    set test_Item to (item 1 of note_Items)
    set test_Records to {}
   
    -- SCAN FOR ESCAPED DATA AND FIX, IF NECESSARY
    set test_Item to my find_Escaped(test_Item)
   
    set old_Delims to AppleScript's text item delimiters
    set AppleScript's text item delimiters to col_Delimiter
    set test_Records to every text item of test_Item
    set AppleScript's text item delimiters to old_Delims
    set column_Count to (count of test_Records)
    display dialog "" & column_Count & " Column Items Detected

Is this correct?" with title script_Name buttons {"YES", "NO"} default button "YES" cancel button "NO" with icon path to resource "Evernote.icns" in bundle (path to application "Evernote")
   
    -- MAPPING INSTRUCTIONS
    set map_List to {}
    if show_Instructions is true then
        activate
        display dialog ¬
            "We now need to assign each column or data item to a property of a new Evernote item.

The next dialogs will help you "map" columns or data items from your CSV or text file to Evernote item properties.


Press "OK" to Continue" with title script_Name with icon path to resource "Evernote.icns" in bundle (path to application "Evernote")
    end if
   
    -- MAP COLUMNS TO EVERNOTE PROPERTIES
    activate
    set map_Count to 1
    repeat column_Count times
        set column_Number to map_Count
        set map_Prompt to "The value of column " & column_Number & " is "" & (item column_Number of test_Records as text) & "".

What Evernote property would you like this to correspond to?"
        set col_Item to (choose from list evernote_Props with title script_Name with prompt map_Prompt)
        if col_Item is false then
            return
        else
            copy {column_Number, col_Item} to end of map_List
            set map_Count to (map_Count + 1)
        end if
    end repeat
   
    -- PREVIEW RESULTS
    set preview_Count to 1
    set preview_Text to "PREVIEW OF THE DATA MAPPED TO EVERNOTE FIELDS" & return & return
    repeat with test_Record in test_Records
        set test_Property to (item 2 of (item preview_Count of map_List))
        set test_Text to (test_Property as text) & ": " & (test_Record as text) & return
        set preview_Text to (preview_Text & test_Text)
        set preview_Count to (preview_Count + 1)
    end repeat
    display dialog preview_Text with title script_Name with icon path to resource "Evernote.icns" in bundle (path to application "Evernote")
   
    -- PROCESS EACH ROW INTO A NEW NOTE
    if show_Instructions is true then
        activate
        display dialog ¬
            "Your import of " & (count of note_Items) & " items is now ready to start!


Press "OK" to Continue" with title script_Name with icon path to resource "Evernote.icns" in bundle (path to application "Evernote")
    end if
    activate
    set theNum to 0
   
    -- SPECIAL CASE FOR TAGS
    set note_Tags to {}
   
    repeat with note_Item in note_Items
        -- RESET EVERNOTE PROPERTIES
        set note_Title to ""
        set note_Body to ""
        set note_Source to ""
        set note_Altitude to ""
        set note_Lat to ""
        set note_Long to ""
        set note_Alt to ""
        set note_Creation to ""
        set note_Author to ""
       
        -- RESET RECORD PROPERTY
        set note_Records to {}
       
        -- SCAN FOR ESCAPED DATA AND FIX, IF NECESSARY
        set note_Item to my find_Escaped(note_Item)
       
        -- PROCESS RECORD INTO COLUMN DATA
        set old_Delims to AppleScript's text item delimiters
        set AppleScript's text item delimiters to col_Delimiter
        set note_Records to every text item of note_Item
        set AppleScript's text item delimiters to old_Delims
       
        -- MAP COLUMNS TO EVERNOTE PROPERTIES
        set note_Count to 1
        repeat with note_Record in note_Records
            try
                set note_Property to (item 2 of (item note_Count of map_List))
               
                -- WHICH EVERNOTE PROPERTY IS IT?
                if (item 1 of note_Property) is "(NONE)" then
                    set note_None to (contents of note_Record)
                else if (item 1 of note_Property) is "Note Title" then
                    set note_Title to (contents of note_Record)
                else if (item 1 of note_Property) is "Note Body" then
                    if note_Body is "" then
                        set note_Body to (contents of note_Record)
                    else
                        set note_Body to note_Body & return & (contents of note_Record)
                    end if
                else if (item 1 of note_Property) is "Creation Date" then
                    set note_Creation to (contents of note_Record as string)
                    set note_Creation to my setDate(note_Creation)
                else if (item 1 of note_Property) is "Source URL" then
                    set note_Source to (contents of note_Record)
                else if (item 1 of note_Property) is "Longitude" then
                    set note_Long to (contents of note_Record)
                else if (item 1 of note_Property) is "Latitude" then
                    set note_Lat to (contents of note_Record)
                else if (item 1 of note_Property) is "Altitude" then
                    set note_Alt to (contents of note_Record)
                else if (item 1 of note_Property) is "Tag" then
                    if (contents of note_Record) is not "" then
                        copy (contents of note_Record) to end of note_Tags
                    end if
                end if
            end try
            set note_Count to (note_Count + 1)
        end repeat
       
        -- PUT BACK REPLACED COMMAS, IF NECESSARY
        -- (html_Comma set as string below to work with download link)
        set html_Comma to "&#" & "44;" as string
        if note_Body contains html_Comma then set note_Body to my replace_Comma(note_Body, html_Comma, ",")
        if note_Title contains html_Comma then set note_Title to my replace_Comma(note_Title, html_Comma, ",")
       
        -- MAKE THE NOTE
        tell application "Evernote"
            set importedNote to (create note with text note_Body title note_Title notebook ev_Notebook)
            -- TAGS
            if note_Tags is not {} then
                set tag_List to my Tag_Check(note_Tags)
                assign tag_List to importedNote
                set note_Tags to {}
            end if
           
            -- ADD OTHER PROPERTIES
            if note_Creation is not "" then
                set creation date of importedNote to note_Creation
            end if
            if note_Source is not "" then
                set source URL of importedNote to note_Source
            end if
            if note_Long is not "" then
                set longitude of importedNote to note_Long
            end if
            if note_Lat is not "" then
                set latitude of importedNote to note_Lat
            end if
            if note_Alt is not "" then
                set altitude of importedNote to note_Alt
            end if
           
            set theNum to theNum + 1
        end tell
    end repeat
end tell

(*
======================================
// PREPARATORY SUBROUTINES
======================================
*)

-- PICK NOTEBOOK
on pick_Notebook()
    --PREPARE A EVERNOTE'S LIST OF NOTEBOOKS
    tell application "Evernote"
        activate
        set listOfNotebooks to {}
       
        -- GET THE NOTEBOOK LIST
        set EVNotebooks to every notebook
        repeat with currentNotebook in EVNotebooks
            set currentNotebookName to (the name of currentNotebook)
            copy currentNotebookName to the end of listOfNotebooks
        end repeat
       
        -- SORT THE LIST
        set Folders_sorted to my simple_sort(listOfNotebooks)
       
        -- GET THE DEFAULT NOTEBOOK
        set list_Default to name of (every notebook whose default is true)
       
        -- USER SELECTION FROM NOTEBOOK LIST
        set SelNotebook to choose from list of Folders_sorted with title script_Name with prompt ¬
            "Which Evernote Notebook Would You Like To Import Items Into?
(Default Notebook Highlighted)" OK button name "OK" cancel button name "New Notebook" default items list_Default
       
        -- CREATE NEW NOTEBOOK OPTION
        if (SelNotebook is false) then
            set userInput to ¬
                text returned of (display dialog "Enter New Notebook Name:" with title script_Name default answer "")
            set EVnotebook to userInput
        else
            set EVnotebook to item 1 of SelNotebook
        end if
    end tell
end pick_Notebook

-- ASSEMBLE TAG LIST, CREATING TAGS IF THEY DON'T EXIST
on Tag_Check(theTags)
    tell application "Evernote"
        set finalTags to {}
        repeat with theTag in theTags
            if (not (tag named theTag exists)) then
                set makeTag to make tag with properties {name:theTag}
                set end of finalTags to makeTag
            else
                set end of finalTags to tag theTag
            end if
        end repeat
    end tell
    return finalTags
end Tag_Check

(*
======================================
// UTILITY SUBROUTINES
======================================
*)

-- SET THE DATE
on setDate(dateText)
    -- TRY TO PARSE DATE
    try
        set the_date to date dateText
    on error
       
        -- IF IT DOESN'T WORK, TRY TO DETECT AND REFORMAT DATE
        set AppleScript's text item delimiters to ""
        set oldTID to AppleScript's text item delimiters
       
        -- TEST FOR VARIOUS DATE SEPARATORS
        if dateText contains "-" then
            set AppleScript's text item delimiters to "-"
        else if dateText contains "/" then
            set AppleScript's text item delimiters to "/"
        else if dateText contains "." then
            set AppleScript's text item delimiters to "."
        end if
       
        -- DETECT THE YEAR, IF POSSIBLE…
        -- (ASSUMES Y/M/D T FORMAT)
        set charCount to (count of characters) of (first word of dateText)
        if charCount is 4 then
            set y to (first word of dateText)
            set m to (second word of dateText) & "/" as string
            set d to (third word of dateText) & "/" as string
            set AppleScript's text item delimiters to " "
            set t to (second text item of dateText)
            set AppleScript's text item delimiters to oldTID
            set dateText to m & d & y & space & t as string
            set the_date to date dateText
        end if
    end try
end setDate

-- SORT SUBROUTINE
on simple_sort(my_list)
    set the index_list to {}
    set the sorted_list to {}
    repeat (the number of items in my_list) times
        set the low_item to ""
        repeat with i from 1 to (number of items in my_list)
            if i is not in the index_list then
                set this_item to item i of my_list as text
                if the low_item is "" then
                    set the low_item to this_item
                    set the low_item_index to i
                else if this_item comes before the low_item then
                    set the low_item to this_item
                    set the low_item_index to i
                end if
            end if
        end repeat
        set the end of sorted_list to the low_item
        set the end of the index_list to the low_item_index
    end repeat
    return the sorted_list
end simple_sort

(*
======================================
// ESCAPED DATA PROCESSING
======================================
*)

-- FIND ESCAPED DATA (I.E., "HERE IS SOME DATA, RIGHT?")
on find_Escaped(theItem)
    set theContents to contents of theItem
   
    if theItem contains """ then
        set the_Chars to {}
       
        -- THE CHARS IS EVERY CHARACTER IN WHOLE ROW
        set the_Chars to every character of theItem
       
        -- EXTRACT TEXT BETWEEN QUOTES
        -- QUEUE UP ESCAPED ITEM NUMBERS (IN PAIRS)
        set esc_Number to 1
       
        repeat while esc_Number is greater than 0
            -- ESC LIST CONTAINS POSITIONS OF QUOTE CHARACTERS FOR ENTIRE ROW
            set esc_List to my items_Position(the_Chars)
           
            -- ESC NUMBER CONTAINS COUNT OF ESCAPED ITEMS FOR ENTIRE ROW
            set esc_Number to (count of esc_List) / 2 as integer
           
            -- ASSEMBLE THE TEXT BEFORE THE QUOTE
            set pre_END to (item 1 of esc_List) - 1
           
            -- AND IF THE QUOTE IS THE FIRST CHARACTER OF THE ITEM…
            if pre_END is 0 then
                set pre_Text to ""
            else
                set pre_Text to (items 1 thru pre_END of the_Chars) as string
            end if
           
            -- ASSEMBLE THE TEXT AFTER THE QUOTE
            set post_END to (item 2 of esc_List) + 1
           
            -- AND IF THE QUOTE IS THE LAST CHARACTER OF THE ITEM…
            if post_END is greater than (count of the_Chars) then
                set post_Text to ""
            else
                set post_Text to (items post_END thru end of the_Chars) as string
            end if
           
            -- ASSEMBLE THE TEXT OF THE ESCAPED ITEM
            set char_Start to (item 1 of esc_List) + 1
            set char_End to (item 2 of esc_List) - 1
            set escaped_Column to (text char_Start thru char_End of the_Chars) as text
           
            -- REPLACE COMMAS IN ESCAPED COLUMN CONTENTS
            if escaped_Column contains "," then
                -- (html_Comma set as string below to work with download link)
                set html_Comma to "&#" & "44;" as string
                set escaped_Column to my replace_Comma(escaped_Column, ",", html_Comma)
            end if
           
            -- REASSEMBLE ROW FROM PARTS
            set escaped_Item to pre_Text & escaped_Column & post_Text as string
           
            -- SET UP FOR NEXT ITEM (IF PRESENT)
            set the_Chars to every character of escaped_Item
            set esc_Number to (esc_Number - 1)
           
        end repeat
        set theItem to (the_Chars as string)
    end if
    return theItem
end find_Escaped

-- DETERMINE ITEM POSITION OF QUOTATION MARKS
on items_Position(the_Chars)
    set esc_List to {}
    set the_Position to 1
    repeat with the_Char in the_Chars
        if contents of the_Char is """ then
            copy the_Position to the end of esc_List
        end if
        set the_Position to (the_Position + 1)
    end repeat
    return esc_List
end items_Position

-- REPLACE COMMAS INSIDE ESCAPED DATA
on replace_Comma(the_Text, the_Original, the_Replacement)
    set oldTID to AppleScript's text item delimiters
    set AppleScript's text item delimiters to the_Original
    set theSourceItems to text items of the_Text
    set AppleScript's text item delimiters to the_Replacement
    set the_Text to theSourceItems as string
    set AppleScript's text item delimiters to oldTID
    return the_Text
end replace_Comma