Exporting from Apple’s Numbers (spreadsheet) to Evernote

[KuriousKevin 0]

I have over 300 notes that I had in Mac’s Address Book, and a friend helped me massage and clean up, finally getting them into Apple’s spreadsheet program, Numbers.

Figured that was the end of the hassle: I’d exported data from Excel using CSV probably a half-dozen times since 1992 into various environments and programs for data storage, for mailing lists, mostly.

But wrong: Number records don’t export directly - 2nd tier Apple support confirmed this.

So how do I get these 300+ notes into Evernote?

Want to preserve of course the Notebook they’re in, their title, body, and formatting (if possible).

[DTLow 5,736]

On 2018-03-03 at 12:18 PM, KuriousKevin said:

So how do I get these 300+ notes into Evernote?

My solution is Scripting (Mac Applescript)
I use it parse csv files and import to Evernote notes; or directly from Contacts or Numbers
I know scripting might be overwhelming to a newbie.  The screenshots to the right illustrate the code required to read a file contents and create a note

>>Want to preserve of course the Notebook they’re in, their title, body, and formatting (if possible).

I'm not clear on the source of the information
 You mentioned Mac Address Book, Numbers, and now you're talking about preserving Notebook...

>>But wrong: Number records don’t export directly 

Can you explain this further; I've exported to csv from Numbers

[JMichaelTX 4,117]

On 3/3/2018 at 2:18 PM, KuriousKevin said:

I have over 300 notes that I had in Mac’s Address Book, and a friend helped me massage and clean up, finally getting them into Apple’s spreadsheet program, Numbers.

Figured that was the end of the hassle: I’d exported data from Excel using CSV probably a half-dozen times since 1992 into various environments and programs for data storage, for mailing lists, mostly.

But wrong: Number records don’t export directly - 2nd tier Apple support confirmed this.

So how do I get these 300+ notes into Evernote?

You can use AppleScript to do this, without exporting anything to CSV file.

So you can use AppleScript to export from either Mac Address Book (do you mean the Contacts.app ?) or from Numbers, and then import into EN Mac.

On 3/3/2018 at 2:18 PM, KuriousKevin said:

Want to preserve of course the Notebook they’re in, their title, body, and formatting (if possible).

I'm not sure what you mean by this, since neither Contacts nor Number have Notebooks.  Perhaps if you could provide a good, representative example of the data as it is now, and how you would like it to appear in EN Mac, then we could be more helpful.

[KuriousKevin 0]

Pulled a couple of notes: the notebook they’d be in would be “Ancient Karma,” and the other two columns are the title of the note, and the body. You can see there is some formatting I’d like to preserve.

[KuriousKevin 0]

The goal again is the title of this posting.

No more, no less. I already have a few hundred notes in EN.

[DTLow 5,736]

On 2018-03-05 at 8:19 PM, KuriousKevin said:

Pulled a couple of notes: the notebook they’d be in would be “Ancient Karma,” and the other two columns are the title of the note, and the body. You can see there is some formatting I’d like to preserve.

I see this as do-able using a script

I'd recommend the data as a .csv file and working from there

edit: Working with a spreadsheet is do-able, but adds to the complication of the script and your leaning curve.

[KuriousKevin 0]

Bueno DT. Now then, I’m not a tech guy - I’m a guy who uses tech daily, for hours. 

Have never run/used/executed a script: only know what they are. So it’d have to be a complete, step-by-step. 
 

1. Could you pick an existing script tutorial that you judge idiot-proof (I’d rather not open a porter to a hell-realm and/or ***** up something on my PowerBook) and ...
2. ... incorporate it with the script that will work, after you’ve tried it yourself to make sure it does work correctly?
3. Will it “overwrite” or damage data already in my Evernote account?

Thanks for the time, stranger!

[DTLow 5,736]

Why don't you start with the script I posted above,

The first step is to read your csv file.

After you get that running, we can advise on

• parsing the data
• creating notes

[KuriousKevin 0]

Agreed ... but what does it mean, to “start a script?” Thus the request for complete step-by-step.

Thanks again.

[DTLow 5,736]

On 2018-03-05 at 8:35 PM, KuriousKevin said:

Agreed ... but what does it mean, to “start a script?” Thus the request for complete step-by-step.

Run the Script Editor app; you'll find in your Applications/Utilities folder 

Enter the commands in the first screenshot

Click on the run icon 

 

 

[KuriousKevin 0]

Except of course make the file name my file name.

Is the coloring  in the script (green/blue) something that happens automatically? I just type the words, and they alter on their own?

[DTLow 5,736]

5 minutes ago, KuriousKevin said:

Is the coloring  in the script (green/blue) something that happens automatically? I just type the words, and they alter on their own?

Yep, the editor does syntax highlighting and indentation

To the right of the run icon, the hammer icon is used for compiling the script.  It identifies coding errors

[JMichaelTX 4,117]

1 hour ago, DTLow said:

I'd recommend the data to a .csv file and working from there

I don't see any advantage in exporting Numbers to a CSV file.  Plus that will lose the rich text formatting.

@KuriousKevin, I would just use AppleScript to read each row in the Numbers file, and use that to create an EN Note.  You should be able to get the cell that will be the Note body as rich text, and convert that to HTML.  Then create the EN Note with the HTML as the body.

If you'd like to upload a zip file of your Numbers file (or at least a few rows of it), I'll take a look at it tomorrow.  Be sure to label each column with the name of the EN Note field you want to import it as.

[DTLow 5,736]

On 2018-03-05 at 9:48 PM, JMichaelTX said:

I don't see any advantage in exporting Numbers to a CSV file.

 I'm thinking it simplifies the Applescript learning curve if we take Numbers out of the process

@KuriousKevin can add Numbers processing after getting comfortable with scripting

>>Plus that will lose the rich text formatting.

That's a good point, but again it complicates the learning curve.
I know line feeds are captured in the csv file, but not other rtf.

[KuriousKevin 0]

Gentlemen, thank you, but I was quite ill with the flu and was just doing tasks like bathing for a while.

Attached is a model file. Of note:
 

1. Uploaded a CSV file
2. Forget formatting - the data is the key thing I wish to preserve (source list is again over 300 notes, some of them older than you)
3. these samples have three columns, the notebook I'd want them in, the note title, and the note
4. Made just a few: nothing in it I wouldn't want out in public
    

Please advise what I must do to at long last get these into Evernote.

 

Evernote Sample.csv

[DTLow 5,736]

On 2018-03-17 at 1:30 PM, KuriousKevin said:

Attached is a model file. ...  Please advise what I must do to at long last get these into Evernote.

My solution is scripting on my Mac (Applescript)

Looking at your sample file I see

1. Rows delimited by linefeed
2. Within each row, columns delimited by comma
3. The delimiters are ignored if within quotes

High Level Design

1. Read the data from the file
2. Parse the file data into rows
3. Repeat for each row
   1. Parse the row data into columns (Notebook, Title, Contents)
   2. Create an Evernote note

Here's the script I came up with CSV.scpt

• I wrote procedure ParseCSV to handle the data parsing.
  In AppleScript, these procedures are actually called handlers.
• Future improvements
  1. Ignore the first row
  2. Drop quotes if they are at the beginning/end of the data
  3. Error checking; missing data
  4. Dialog for specifying input file
  5. Add a GetDataCSV procedure that does all the parsing and returns a two dimensional list; rows and columns
     This procedure can be replaced with GetDataSpreadsheet that returns the same data

 

[KuriousKevin 0]

To simplify it finding the, moved it to the desktop (as I couldn't find how to properly name/signify a long folder name).

Ran script, got "Script error. End of file error."

Please advise. 

I have both dragged and dropped screenshots, as well as tried choosing them, but it's not working. 

It's hard to articulate how annoying this all is. Thanks for trying.

 

[DTLow 5,736]

1 hour ago, KuriousKevin said:

Ran script, got "Script error. End of file error."

Is it possible you specified the filename incorrectly?
For me, the filename would be    /Users/david/Desktop/EvernoteSample.csv

I retrieved the pathname by right clicking on the file and holding down the option key.

[KuriousKevin 0]

In my fatigue a half-year ago (!), I made several mistakes. File name wrong, format wrong. I was doing this migration for hours and hours, and was just plain fatigued.

Running it now. A little concerned that it hasn't finished after two minutes for 240 or so records.

[KuriousKevin 0]

Stopped after 12 minutes. Got, "error "User canceled." number -128"

Wondering if it's on desktop cloud, rather than true desktop.

[DTLow 5,736]

18 minutes ago, KuriousKevin said:

Stopped after 12 minutes. Got, "error "User canceled." number -128"

Wondering if it's on desktop cloud, rather than true desktop.

Maybe get more information from the log

Select View > Show Log before running the script

[KuriousKevin 0]

OK. Moved the exported (from Numbers) CSV to "downloads" when I saw that Desktop is as of late not the desktop - rather on the iCloud. Unfortunate choice of Apple's architects. Guess they got tired of people losing their files when they never put files away somewhere reasonable.

Next, copied the filepath name. Ran it for 3 minutes, stopped, got same code.

Maybe it's a notebook name issue? That column has punctuation including apostrophes (') slash marks (/) and periods (.).

 

[KuriousKevin 0]

4 minutes ago, DTLow said:

Maybe get more information from the log

Select View > Show Log before running the script

Took out row 128, just for fun. No difference.

Shouldn't something like this execute within a minute or two for 240 records, regardless of their complexity?

Show log looks like the script, not a record of what it did:

"

 

set fileContents to ReadFileContents("Users/MaddenMigration/Downloads/MinusRow128.csv/MinusRow128.csv")

 

 

set rowContents to ParseCSV(fileContents, linefeed)

 

repeat with rowContent in rowContents

 

set theRow to ParseCSV(rowContent, ",")

set theNotebook to item 1 of theRow

set theTitle to item 2 of theRow

set theContents to item 3 of theRow

 

tell application "Evernote" to create note with text theContents title theTitle notebook theNotebook

 

end repeat

 

 

--=====================================

-- Read File Contents

--======================================

 

 

on ReadFileContents(theFile)

 

set fileRef to open for access theFile

set fileContents to (read fileRef)

close access theFile

 

return fileContents

 

end ReadFileContents

 

 

--======================================

-- Parse CSV

--======================================

-- Returns a list of parsed elements

 

on ParseCSV(theData, theDelimiter)

 

set theChars to every character of theData

set theDataList to {}

 

set theParse to ""

set withinQuotes to false

repeat with theChar in theChars -- walk through every character

 

if theChar as string = "\"" as string then -- test for quotes

set withinQuotes to not withinQuotes --toggle

end if

 

if theChar as string = theDelimiter as string and not withinQuotes then -- until delimeter is detected (ignore if within quotes)

copy theParse to end of theDataList

set theParse to ""

else

set theParse to theParse & theChar

end if

end repeat

 

copy theParse to end of theDataList

 

return theDataList

 

end ParseCSV"

 

[KuriousKevin 0]

Log history is none-too-useful, either.

Gotta go, but eager to learn what I am still doing wrong.

Thanks!

[KuriousKevin 0]

OK - tried it with 100 items, didn't work.

So tried it with 10 - kinda worked, however it put several of the titles and bodies in quotation marks, and several of the bodies of notes.

Took out a couple of the personal ones, but you can see in the attached. Also attaching log.

Thanks.

 

[KuriousKevin 0]

(also, it corrupted Bêtes Noires)

[DTLow 5,736]

1 hour ago, KuriousKevin said:

OK - tried it with 100 items, didn't work.

So tried it with 10 - kinda worked, however it put several of the titles and bodies in quotation marks, and several of the bodies of notes.

Took out a couple of the personal ones, but you can see in the attached. Also attaching log.

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

(also, it corrupted Bêtes Noires)

Thats promising.  I'd still like to find out why the 100 failed.
I ran your sample file on my Mac and it was successful

Confirmed; Bêtes Noires gets corrupted right from the initial import
Confirmed; Quotes are not being dropped from the input
- some script tweaking is required

The screenshot shows log information as the script is running
I can see the .csv file being opened, being read, ....

[KuriousKevin 0]

Yep. Can you help me, or send me somewhere to get these character substitutions fixed first (and some columns/notes suddenly appearing within quotation marks)?

Thanks in advance for your time!

[DTLow 5,736]

3 hours ago, KuriousKevin said:

and some columns/notes suddenly appearing within quotation marks)?

Looking at the script, I see every character of the input is processed, including quotes

repeat with theChar in theChars -- walk through every character
      if theChar as string = "\"" as string then -- test for quotes
          set withinQuotes to not withinQuotes --toggle
     end if

     if theChar
          ... Processing for the character                    <<< You don't want this processing if the character is a quote
     end if

so the revised code would be

repeat with theChar in theChars -- walk through every character
      if theChar as string = "\"" as string then -- test for quotes
          set withinQuotes to not withinQuotes --toggle

     else if theChar
          ... Processing for the character

     else if

[KuriousKevin 0]

Thank you DTLow for spending all the time. 

Evernote generously offered to let me open a ticket on this item: hope it helps many folks. 

I can’t imagine I’m the only info hoarder wanting to at last have everything in one place.

[KuriousKevin 0]

5 hours ago, DTLow said:

Looking at the script, I see every character of the input is processed, including quotes

repeat with theChar in theChars -- walk through every character
      if theChar as string = "\"" as string then -- test for quotes
          set withinQuotes to not withinQuotes --toggle
     end if

     if theChar
          ... Processing for the character                    <<< You don't want this processing if the character is a quote
     end if

so the revised code would be

repeat with theChar in theChars -- walk through every character
      if theChar as string = "\"" as string then -- test for quotes
          set withinQuotes to not withinQuotes --toggle

     else if theChar
          ... Processing for the character

     else if

Would you be so kind as to make a new Script, maybe naming it CSV91018?

Again, beyond my abilities, and would rather practice a card trick and stick to what I'm good at.

: )

[DTLow 5,736]

6 minutes ago, KuriousKevin said:

Would you be so kind as to make a new Script, maybe naming it CSV91018?

Again, beyond my abilities, and would rather practice a card trick and stick to what I'm good at.

I'm happy to help you improve your scripting skills, not enthused about writing scripts for others.