A few days ago I ran into a problem. The list of “notes” in Colornote, a note-taking app that came with my Android phone, had grown too large to sort through visually. So I thought I’d export them for reading on the computer. But Colornote only lets you do that one at a time. Oops.
After a lot of searching, trial, and error, I figured out how to do it. First, I found a decent (but, it turns out, incomplete) guide here: Exporting Data from ColorNote Android.
But as with just about all software these days, I ran into a few snags. I had already installed Android SDK a while back in trying to figure out how to back up my phone’s internal storage, because unlike the SD card, it can’t simply be copied to my computer. That is a confusing process in itself if you’re not already accustomed to this stuff, and maybe when I have the time, I can fully explain it here. For now, suffice to say that everything worked up until the extraction step:
- extract the file:
dd if=backup.ab bs=1 skip=24 | python2 -c "import zlib,sys;sys.stdout.write(zlib.decompress(sys.stdin.read()))"= | tar -xvf -
After searching for answers for a while and giving up, the returning to it a couple of times, I found an approach that worked.
First, I had to go find something called Android Backup Extractor, which is available on GitHub, a site that never ceases to confuse me with a mess of tabs and links and anything but a clear “DOWNLOAD THIS HERE” button. First I had to find the Release page, which you’d think would be important enough to have a tab at the top, but instead is buried in a sidebar.
So I clicked on the link and looked to see what was there under “Assets”. Turned out it was just two “Source code” links, a “License” link, and something ending in “.jar”. So, since that was the only thing that looked promising, I downloaded the .jar file to the same directory as the Colornote backup.ab file I had already created with Android SDK. As I was writing this, the actual file of the latest release was this one: abe-0059753.jar. (For simplicity, let’s call the directory I used “D:\Colornote.”
Ok, now what is a .jar file and how in the world do I use it? I got the necessary clue at the former download page for the tool at Sourceforge, thanks to a helpful review comment from 10 years ago (thank you dannyforge):
Worked like a charm. Just unzip and type java -jar abe.jar unpack NAME_OF_BACKUP.ab NAME_OF_BACKUP.tar <password> That will convert the ab-file to a tar-archive.
So to save myself some typing and headaches in the next couple of steps, I renamed that .jar file to simply “abe.jar”. Then I modified the command above to simply note the backup file name, omitted the password, and left the rest alone. (I figured leaving the example NAME_OF_BACKUP.tar for the file name would make it easy to tell if it worked.) Then I opened a command prompt in the directory I was in (by typing “cmd” at the beginning of the file path in Windows Explorer then hitting enter), and punched in my command:
D:\Colornote>java -jar abe.jar unpack colornotebackup.ab NAME_OF_BACKUP.tar
Bam! It worked:
D:\Colornote>java -jar abe.jar unpack colornotebackup.ab NAME_OF_BACKUP.tar
0% 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% 16% 17% 18% 19% 20% 21% 22% 23% 24% 25% 26% 27% 28% 29% 30% 31% 32% 33% 34% 35% 36% 37% 38% 39% 40% 41% 42% 43% 44% 45% 46% 47% 48% 49% 50% 51% 52% 53% 54% 55% 56% 57% 58% 59% 60% 61% 62% 63% 64% 65% 66% 67% 68% 69% 70% 71% 72% 73% 74% 75% 76% 77% 78% 79% 80% 81% 82% 83% 84% 85% 86% 87% 88% 89% 90% 91% 92% 93% 94% 95% 96% 97% 98% 99% 100%
2423808 bytes written to NAME_OF_BACKUP.tar.
Then I renamed the .tar file to colornotebackup and used 7zip to unzip it. It took a few clicks, but buried a few layers down, I found the database file I needed:
D:\Colornote\colornotebackup\apps\com.socialnmobile.dictapps.notepad.color.note\db\colornote.db
Then I continued with the instructions found above and installed the standard installer, 64-bit version of DB Browser for SQLite. It gave me some weird prompt on opening for the first time, but whatever, I just closed it and moved on, opening my colornote.db file in it.
Trouble was what to do with this step of the above instructions:
- List all the notes: SELECT datetime(created_date/1000,’unixepoch’) as created, datetime(modified_date/1000,’unixepoch’) as modified,note FROM notes ORDER BY created
Maybe that was a SQL command? I clicked into the “Execute SQL” tab of DB Browser for SQLite and pasted the string after the colon in:
SELECT datetime(created_date/1000,’unixepoch’) as created, datetime(modified_date/1000,’unixepoch’) as modified,note FROM notes ORDER BY created
But how to execute? I found I needed to click the little triangular “play” button. Then, sure enough, I got a nice table showing my notes in the window below.
And then one more mystery step. How to export?
- Export as CSV
I looked through the menus and found the one I needed was File > Export > Table(s) as CSV file…
Then I had to choose from a list of four tables:
- android_metadata
- notes
- sqlite_sequence
- SyncAccount
Since three of those looked like technical junk, I chose the only one that looked promising, “notes”, and sure enough, got the CSV file I wanted, albeit with an error:
Ok, whatever. Instead of trying to open with LibreOffice, I closed out of that and opened with Notepad++, which has come in very handy lately, letting me edit Videopad, Photostage, and Reaper project files manually to do things the programs don’t have a way to do with the user interface, and voila, no problem. But to be safe and ensure I had something easily readable by my own eyes, I also simply copied the table directly from the DB Browser for SQLite window and saved it as a text file. And then I copied it into a LibreOffice Writer document and saved it that way, too. Turned out to be 447 pages and 675KB.
Whatever. At least I have it all now and can go delete everything from Colornote and find some better note app, preferably with time tracking features, too, so I can both take notes and keep track of the time I spend on all my tasks at once.
So once again, here we have an example of what, if I recall correctly, Fr. Jim Dufner called “all these time-saving devices that take all our time.”