Avatar for Username

Search Support

Avoid support scams. We will never ask you to call or text a phone number or share personal information. Please report suspicious activity using the “Report Abuse” option.

Learn More

This thread was archived. Please ask a new question if you need help.

Export Bookmarks to .CSV in one click, using SQLite3 in a .BAT file

sludge7051-x
sludge7051-x
more options

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

I thought of an improvment on how to do this, but it's not working (described below)

Previous way: . . . Bookmarks - How to extract them from places.sqlite, and save as a .CSV . . . https://support.mozilla.org/en-US/questions/1186163

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

The following "kind of" works, but I don't get the Bookmarks. Any idea why? The code for the query is not working for some reason. Here's what I did:

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

The "sqlite3" example code I'm using is here at the bottom . . . Help to automate SQLite script in Win7 . . . http://forums.whirlpool.net.au/archive/1700260

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

FOR WINDOWS . . . Go to the SQLite3 download page, and get the “Precompiled Binaries For Windows” . . . Download “sqlite-shell” and “sqlite-dll” archive files . . . SQLite Download Page . . . https://sqlite.org/download.html

sqlite-dll-win32-x86-3210000.zip . . . "sqlite-shell” and “sqlite-dll” . . . sqlite3.def . . . sqlite3.dll . . . Unpack them in C:\WINDOWS\system32 folder . . . or any other that is in your PATH

sqlite-tools-win32-x86-3210000.zip . . . These look good too, but not necessary? . . . A bundle of command-line tools for managing SQLite database files, including the command-line shell program, the sqldiff.exe program, and the sqlite3_analyzer.exe program

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

MUST BE A CLUE HERE . . . SQLite Command Line program . . . See "9. CSV Export" . . . https://sqlite.org/cli.html

INFORMATIONAL ONLY . . . The SQLite Amalgamation . . . http://www.sqlite.org/amalgamation.html

INFORMATIONAL ONLY, USED TO GET CLUES ABOUT HOW TO DO THIS . . . Instructions for Linux and Mac . . . https://www.sitepoint.com/getting-started-sqlite3-basic-commands/

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

EXAMPLE FROM FIRST WEB PAGE ABOVE, AND THE "VIEW / QUERY / CODE" I'VE BEEN USING . . . MAYBE GOING FROM TWO ROWS, TO ONE, IS PREVENTING IT FROM WORKING? PUT A COMMA/SPACE THERE? NO, STILL DOESN'T WORK.

sqlite3.exe -csv file_name "SELECT totalWatts, tempr, timestamp FROM currentcostdata WHERE DATE(timestamp) = DATE(CURRENT_DATE,'-1 day')" > output_%mydate%_%mytime%.csv

SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,"unixepoch","localtime") AS Date FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id

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

IT LOOKS LIKE I CAN BREAK IT DOWN LIKE THIS . . . IF THERE ARE SPACES IN THE PATH, PUTTING IT IN QUOTES MUST WORK . . .

sqlite3.exe -csv

file_name

"SELECT totalWatts, tempr, timestamp FROM currentcostdata WHERE DATE(timestamp) = DATE(CURRENT_DATE,'-1 day')"

> output_%mydate%_%mytime%.csv

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

WHAT I'M TRYING TO GET, BROKEN DOWN:

sqlite3.exe -csv

"C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite"

"SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,"unixepoch","localtime") AS Date FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id"

> "C:\Users\SLUDGE\Desktop\output.csv"

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

COMBINED, AND MADE INTO A ".BAT" FILE . . . IT RUNS WITH NO ERRORS, AND CREATES "output.csv" . . . BUT THERE IS NOTHING IN THE FILE, NO BOOKMARKS, NO DATA AT ALL:

sqlite3.exe -csv "C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite" "SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,"unixepoch","localtime") AS Date FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id" > "C:\Users\SLUDGE\Desktop\output.csv"

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I thought of an improvment on how to do this, but it's not working (described below) Previous way: . . . Bookmarks - How to extract them from places.sqlite, and save as a .CSV . . . https://support.mozilla.org/en-US/questions/1186163 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The following "kind of" works, but I don't get the Bookmarks. Any idea why? The code for the query is not working for some reason. Here's what I did: - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The "sqlite3" example code I'm using is here at the bottom . . . Help to automate SQLite script in Win7 . . . http://forums.whirlpool.net.au/archive/1700260 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - FOR WINDOWS . . . Go to the SQLite3 download page, and get the “Precompiled Binaries For Windows” . . . Download “sqlite-shell” and “sqlite-dll” archive files . . . SQLite Download Page . . . https://sqlite.org/download.html sqlite-dll-win32-x86-3210000.zip . . . "sqlite-shell” and “sqlite-dll” . . . sqlite3.def . . . sqlite3.dll . . . Unpack them in C:\WINDOWS\system32 folder . . . or any other that is in your PATH sqlite-tools-win32-x86-3210000.zip . . . These look good too, but not necessary? . . . A bundle of command-line tools for managing SQLite database files, including the command-line shell program, the sqldiff.exe program, and the sqlite3_analyzer.exe program - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MUST BE A CLUE HERE . . . SQLite Command Line program . . . See "9. CSV Export" . . . https://sqlite.org/cli.html INFORMATIONAL ONLY . . . The SQLite Amalgamation . . . http://www.sqlite.org/amalgamation.html INFORMATIONAL ONLY, USED TO GET CLUES ABOUT HOW TO DO THIS . . . Instructions for Linux and Mac . . . https://www.sitepoint.com/getting-started-sqlite3-basic-commands/ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - EXAMPLE FROM FIRST WEB PAGE ABOVE, AND THE "VIEW / QUERY / CODE" I'VE BEEN USING . . . MAYBE GOING FROM TWO ROWS, TO ONE, IS PREVENTING IT FROM WORKING? PUT A COMMA/SPACE THERE? NO, STILL DOESN'T WORK. sqlite3.exe -csv file_name "SELECT totalWatts, tempr, timestamp FROM currentcostdata WHERE DATE(timestamp) = DATE(CURRENT_DATE,'-1 day')" > output_%mydate%_%mytime%.csv SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,"unixepoch","localtime") AS Date FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - IT LOOKS LIKE I CAN BREAK IT DOWN LIKE THIS . . . IF THERE ARE SPACES IN THE PATH, PUTTING IT IN QUOTES MUST WORK . . . sqlite3.exe -csv file_name "SELECT totalWatts, tempr, timestamp FROM currentcostdata WHERE DATE(timestamp) = DATE(CURRENT_DATE,'-1 day')" > output_%mydate%_%mytime%.csv - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - WHAT I'M TRYING TO GET, BROKEN DOWN: sqlite3.exe -csv "C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite" "SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,"unixepoch","localtime") AS Date FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id" > "C:\Users\SLUDGE\Desktop\output.csv" - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - COMBINED, AND MADE INTO A ".BAT" FILE . . . IT RUNS WITH NO ERRORS, AND CREATES "output.csv" . . . BUT THERE IS NOTHING IN THE FILE, NO BOOKMARKS, NO DATA AT ALL: sqlite3.exe -csv "C:\Users\SLUDGE\AppData\Roaming\Mozilla\Firefox\Profiles\u689l7dh.default\places.sqlite" "SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,"unixepoch","localtime") AS Date FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id" > "C:\Users\SLUDGE\Desktop\output.csv"

Chosen solution

Here's how to do this . . . this is a PDF I made, and have put on my Google Docs page:

bookmarks-to-CSV.pdf . . . This first page provides all you need to know, to get your Bookmarks into a .CSV file (This won't do anything to your actual Bookmarks, it just extracts a copy of them.) . . . Then, the next few pages provide the details of what's going on . . . https://drive.google.com/open?id=1xYWPQtijqCzk-1nzTsTb0ZUVKYJNFokR

Read this answer in context 👍 0

All Replies (5)

Shadow110
Shadow110
more options

Just a simple question why would you not just Export them as HTML (like you can for years mine are from 95) Can then upload it to a website that does HTML to CVS conversion,

Please let us know if this solved your issue or if need further assistance.

sludge7051-x
sludge7051-x Question owner
more options

I created an Excel macro that imports them, deletes out garbage characters, and pastes them to the bottom of my main Bookmarks.xlsx, where I categorize them on up to 18 fields. I can do Ctrl +F by keyword to find things, keep hard to figure out things in sequential order, so I don't have to re-think them. It's like having augmented reality.

Modified by sludge7051-x

the-edmeister
the-edmeister
  • Moderator
more options

Your query is beyond the scope of this support forum. Most of us are Firefox users who contribute our time to help other Firefox users, and aren't programmer's who can intelligently provide an answer for you.

Your questions on that subject might be better answered over here: https://stackoverflow.com/questions/tagged/firefox+or+firefox-os+or+html5-apps

sludge7051-x
sludge7051-x Question owner
more options

Cor-el told me how to do this before, and I think you did too.

Hard part Cor-el told me. I think this is relatively easier. Just something with the syntax.

sludge7051-x
sludge7051-x Question owner
more options

Chosen Solution

Here's how to do this . . . this is a PDF I made, and have put on my Google Docs page:

bookmarks-to-CSV.pdf . . . This first page provides all you need to know, to get your Bookmarks into a .CSV file (This won't do anything to your actual Bookmarks, it just extracts a copy of them.) . . . Then, the next few pages provide the details of what's going on . . . https://drive.google.com/open?id=1xYWPQtijqCzk-1nzTsTb0ZUVKYJNFokR