(Archived) Importing records from SQL database?

[cyberpine 0]

I have my own KB I've been evolving for over 10 years. The current version is a SQL 2005 dabase table with two columns.. title varchar(80) and body text. I have over 9000 rows and some of the body columns are are over 16,000 lines long. It's all text. Some of the text contains html tags, quotes and commas.

I'd like to migrate to EverNote.

Any suggestions for the best and easiest way to do this?

Thanks.

[Metrodon 2,188]

What do you want to do with it in Evernote?

How big is is the file? - if you just want to back it up and it falls within the allowed size then update to Premium and drag it into a note.

[jbenson2 2,149]

I sympathize with you. I'm a pack-rat when it comes to data.

I started my database collection with DOS-based Lotus Agenda (a product ahead of its time, but Lotus could not handle the cross over when Windows came on the scene), then migrated to Lotus Symphony for many years, then on to a 10-year stint with Frontrange Goldmine, and today I am in Evernote with a Fujitsu ScanSnap.

Each transition was difficult and I know I lost quite a bit of information each time. I am still manually pulling stuff from Goldmine and pasting it into Evernote. But the increased efficiencies and search capabilities made it worth the change.

[BurgersNFries 2,407]

Can you create a text file for each note in your database? I deal with mostly legacy code, so there is probably a more up to date way to do this...but here's what I'd do. Create an ascii/csv file with one record for each SQL record. You may have to do some parsing and/or replacing a quote mark with two quotes & stuff like that. Or make it tab delimited... Something that will preserve your data as much as possible but still work in an ascii file. Then using a programming language like business basic, write a very simple program to read in each record & build a txt file for each record. You'd end up with 9000+ txt files that you could then copy & paste into a watched/folder (once you've verified they seem good) and then you're home free. Like I said, there's probably a better way to do this with current code/languages/tools. But that's what I'd do.

Also, in case the size of the files exceeds your monthly upload limit, you may have to store some in local/non-sync'd folders until your cycle refreshes.

[cyberpine 0]

Thank you for responses.. sorry for delay.

The search features on my current solution are very powerful. The search guts is REGEX so I can search for things like give me every record that has the string "mary" and "jane" within 80 characters of each other and return every row by most recently modified.

Where it's lacking is that it does not allow me to store images or links. I could build in this functionality, but I'd have to build a mobile client too.. basically a lot of work for one user (me).

What am looking to do? Well, have all my valuable information in one easy to access place ..from my favorite and handy clients.

I guess I could create one document per record and end up with 9000+ documents. I was hoping there might be a better migration path. I understand all this legacy stuff would be character only, but I could then add images and links to it.

I do fear giving up control of the database. For me it's my external brain and on the few occassions it's been unavailable I've been complete lost. Also, the biggest part of having a KB.. and I was doing KB way before internet was the internet.. it's been over 20 years actually.... is IMHO, conditioning yourself to always use it in place of human memory, paper or email history.

Thanks.

[BurgersNFries 2,407]

The search features on my current solution are very powerful. The search guts is REGEX so I can search for things like give me every record that has the string "mary" and "jane" within 80 characters of each other and return every row by most recently modified.

Where it's lacking is that it does not allow me to store images or links. I could build in this functionality, but I'd have to build a mobile client too.. basically a lot of work for one user (me).

but I could then add images and links to it.

For me it's my external brain and on the few occassions it's been unavailable I've been complete lost.

The Evernote search is very powerful, but not as specific as the example you mentioned. But you can order results by most recently updated, too. From what you've posted, your current system has a more refined search function but is limited to text. I'm not sure how often you'd really need to be that specific in your searches. Maybe the ability to add urls & images will outweigh forfeiting some of the more advanced search abilities? If it were me, I'd probably try to do what I suggested above (export to ascii then write Q&D program to read in each record & create a new txt file for each record & then import the txt files into EN) and see how well you got a long with using the EN search functions. IMO, it's pretty powerful for every day use. If it seems like EN may be a fit for you, you could even continue to maintain your SQL db, then export the new records, use the aforementioned Q&D program to make new txt files for the newly added/exported records, import them to EN & add any urls or images. Yeah, it's double entry but I do that for some things as well. That whole eggs all in one basket thing.

[cyberpine 0]

Thanks again for these response!

I'm back looking at Evernote again. My Android phone is bringing me back here. Did some import test and about to pull the trigger.

One followup - and sorry I will probably also post a fresh forum entry for it as well since somewhat off topic.

Why doesn't Everynote recognize the underscore as a word seperator on it's search index? Also, any way to search with wildcards like this *INV*. For example, say I want all notes with string invoicenumber. I noticed notes witht his string don't pull up

sp_invoicenumber

But a contradiction.. If I add sp-invoicenumber to the document it does pull up and then it even highlights a match in sp_invoicenumber.

Thank you.

[JMichaelTest 19]

I have my own KB I've been evolving for over 10 years. The current version is a SQL 2005 dabase table with two columns.. title varchar(80) and body text. I have over 9000 rows and some of the body columns are are over 16,000 lines long. It's all text. Some of the text contains html tags, quotes and commas.

I'd like to migrate to EverNote.

Any suggestions for the best and easiest way to do this?

Thanks.

Another approach to import is to use email.

You could write a simple email send program (in the tool of your choice) to read the SQL DB, put the data into one email per SQL record/EN Note, and send to your EN email account. This would allow you to specify the EN folder and tags in the email subject line.

For those of you wondering what tool to use, there are many choices. One tool that many people already own is MS Office VBA. This is easily done using VBA , and there are tons of examples on the Internet.

HTH.

[C.Noize 2]

Also, any way to search with wildcards like this *INV*. For example, say I want all notes with string invoicenumber. I noticed notes witht his string don't pull up

EN doesn't search that way. It doesn't search for the whole string in your notes. It uses a treelike structure as far as i know. If you type in "a" in your search box it only selects the "a" branch of the tree and from there it goes on with the second letter and the second branch in the tree. In each branch you find links to the notes, where theses words can be found.

This way you can do a very fast and error-tollerant search, which is necessary if you want a good OCR recognition. For example. If Evernotes OCR routine scans the letter "o" it just puts a link into the "o", "ö", "0" and "c" branch since the recognized character somehow looks like it. This way you will definitly find the correct answer when you are looking for it. You would also find it if you are looking for it with the wrong letters instead of the o, but you wouldn't search for that, would you?

As for your question, why you can't use wildcards. What would happen, if you type in a "*" at the beginning? You basically have to but a link to your note into every branch and every subbranch of the tree. This wouldn't work.

Evernotes search is designed for quick on the fly searching. This has it's pros and cons. The cons are, that you can't use wildcards, but that's acceptable if you see how well the OCR recognition works.

Well, it isn't actually that good by itself. As you can see, it fakes it a little bit and is pretty fuzzy on it's own. Only in combination with the search-tree it delivers such good results as it does in EN.