![Firefox logo](https://assets-prod.sumo.prod.webservices.mozgcp.net/media/uploads/products/2020-04-14-08-36-13-8dda6f.png)
Bookmarks - Convert from places.sqlite to .xlsx (Excel) - How?
I have installed SQLite Manager 0.8.1
https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/
Go to File Explorer / Show Hidden items (so you can see App Data, next)
Put a copy of places.sqlite in C:\temp (work from a copy)
C: \ Users \ Username \ App Data \ Roaming \ Mozilla \ Firefox \ Profiles \ long string \ places.sqlite
Go to the FF drop down menu in the top left / Web Developer / SQLite Manager
. . . it opens the program in a new window
Within the program, go to:
Database / Connect Database / C:/temp/places.sqlite
- - - - - - - - - - - - - - - - - - - -
You will see many Tables. The two pertinent ones I think are:
moz_bookmarks / Browse & Search tab
all the new bookmark Titles are in here at the bottom, even ones not in the Unsorted folder, but the URLs are not here!
the Titles appear to be added in sequential order, according to the id
moz_places / Browse & Search tab
all the new bookmark Titles, and URLs, are in here, they are not readily identifiable, you have to use Ctrl+F
- - - - - - - - - - - - - - - - - - - -
To export the tables:
right-click on the table / Export Table /
CSV tab / [check] First row contains column names
Comma, Double quotes, Save CSV Export Settings
Ok / Save it to desktop as a CSV
Open the CSV, and then save it as an xlsx
- - - - - - - - - - - - - - - - - - - -
Question: How can I get my Title and URLs?
There are over 17,000 rows in moz_places.
I would be putting all new bookmark in Unsorted though, so the question is, how do you identify them?
This seems possible.
Alternatively, if I could add the URL column to moz_bookmarks, then I could just export that. I would work from a copy . . . but it doesn't look like SQLite Manager will let me do this.
- - - - - - - - - - - - - - - - - - - -
I have also installed: SQLite Database Browser, Version 2.0b1
http://sourceforge.net/projects/sqlitebrowser/
I try to open places.sqlite with it, and nothing happens. Any idea why? Thx.
Novain'i sludge7051-x t@
Vahaolana nofidina
You can easily get dates in a readable format by converting the Unix Epoch format to a date/time format:
SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,'unixepoch') AS DateAdded FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id
I don't think that it matters which table is open when you paste the code in the "Execute SQL" text area to run the code.
What you see there by default in only some sample code that you can modify to suit what you want.
Current Firefox versions may be using an SQLite version that is newer and isn't supported by SQLite Database Browser.
You need to add a <br> to make the forum start on a new line or place text in <pre> and </pre> tags for larger block. A line that starts with a space is automatically placed between PRE tags.
Hamaky an'ity valiny ity @ sehatra 👍 1All Replies (13)
TL;DR
It may be easier / better to use sync or export and import as .html
- What are you trying to achieve
- and and what part of the data is important, is it just the title and location ?
- Is the end use just using in Firerfox again ?
I would be putting all new bookmark in Unsorted though,
Formats
You will realise Firefox uses places.sqlite as the working database of Bookmarks & History. Are you also aware Firefox itself has two options for export and backup.
- .json The backup is in.json format and may be used to backup and overwrite bookmark files in the database
- .html The Export option are compatible with other browsers, and easily edited in Word Processors. These will tack on to the end (append) when imported into Firefox's bookmarks library.
If you are not using all of the places.sqlite data it may be easier to work with the HTML copies of bookmarks. If you need to import additional data and add it to Firefox's existing bookmarks Library, again .html is the way to go.
Firefox Sync
Assuming the other bookmarks are on another working device use Firefox Sync. (From Tuesday the service is improved and actually Cloud based on Firefox 29)
You mentioned 17000 rows. I am not sure if Firefox will easily handle that number of bookmarks. If it does you may suffer performance hits and reduction of History data. Test it out in an additional profile first of all. It may make sense instead to maintain that data in an external xls database.
In my bookmarks, I have some that stay there in folders, on the Bookmarks Toolbar:
Frequently Used: 12 Search: 6 Weather: 11 Maps: 4 News: 12
I accumulate new bookmarks in the Unsorted.
- - - - - - - - - - - - - - - - - - - - -
On a weekly basis, I've been exporting my FF bookmarks to html, and using this Excel macro I did to process them:
Bookmarks - A better Export / Show folder quantity in Status Bar https://support.mozilla.org/en-US/questions/996531
In this post, I was asking if it would be possible if the html data could get the Titles consistently in the same column (like it does for the URLs). This would then require less editing time, moving them from one column to another.
- - - - - - - - - - - - - - - - - - - - -
Next . . .
1.) I copy and paste the Titles and URLs from the macro data, to the bottom of my bookmarks worksheet (please see screenshot)
2.) I edit the Titles for garbage characters - characters that got messed up in the export.
3.) I then categorize them on up to 18 fields, and sort
4.) I then fine-tune the categorization
- - - - - - - - - - - - - - - - - - - - -
I use the Concatenate function in Excel a lot.
I use this formula to share links with other (copy and paste, it's the second column from the far right, "C & P"):
=CONCATENATE(S3050 &" . . . "& T3050)
I use this formula to sort the 18 columns (it's last column on the far right, "Sort"):
=CONCATENATE(CONCATENATE(R3050,"...",TEXT(A3050,"000000"),"...",B3050,"...",C3050,"..."),D3050,"...",E3050,"...",F3050,"...",G3050,"...",H3050,"...",I3050,"...",J3050,"...",K3050,"...",L3050,"...",M3050,"...",N3050,"...",O3050,"...",P3050,"...",Q3050,"...",S3050)
- - - - - - - - - - - - - - - - - - - - -
In the above post, edmeister mentioned how I could just use the places.sqlite file to get my bookmarks.
I took a look at this, and yes, if I could get the bookmarks directly as a CSV, that would be excellent - there would be no macro needed, and no editing necessary. I could just copy and paste Title and URL from there.
- - - - - - - - - - - - - - - - - - - - -
. . . which brings me to where I am . . .
The places.sqlite database doesn't seem to be set up so that you can figure out where your new bookmarks are.
If I export moz_places (as far as I can tell, the only table that has both the Title and URL in it), how can I tell which URLs were in the Unsorted folder? I know that there in there - if you go to the Library, you can get the URL, and use Ctrl+F in moz_places, and find them.
I never saw this before: TL;DR
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
tl;dr
Literally, "Too long; didn't read"
Said whenever a nerd makes a post that is too long to bother reading.
http://www.urbandictionary.com/define.php?term=tl%3Bdr
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
What I do:
Natural Reader - Free text to speech software with Natural Voices ... I use this to get through a lot of text . . . http://www.naturalreaders.com/download.php
Microsoft text-to-speech voices - Wikipedia . . . http://en.wikipedia.org/wiki/Microsoft_text-to-speech_voices
YouTube - The 6 best Text to Speech engines . . . http://www.youtube.com/watch?v=-mWQaFTlk5I
I went to moz_bookmarks / Execute SQL tab / put this code in below / Run SQL
. . . and it worked! Wow! Amazing!! TY!!!
SELECT b.title AS Bookmark, f.url AS URL FROM moz_bookmarks AS b JOIN moz_places AS f ON f.id = b.fk
- - - - - - - - - - - - - - - -
. . . a few questions . . .
b.title . . . where does b come from?
f.url . . . where does f come from?
I've used Access before, and done update and append queries, but never used SQLite Manager
- - - - - - - - - - - - - - - -
I would re-write it in this order, I see it still works:
SELECT b.title AS Bookmark, f.url AS URL FROM moz_bookmarks AS b JOIN moz_places AS f ON b.fk = f.id
- - - - - - - - - - - - - - - -
Is there a way to get the ID there also? Then I'd know the new URLs are after a certain ID every time.
. . . I don't know where the letters come from, but see that this didn't work:
SELECT a.id AS ID, b.title AS Title, f.url AS URL FROM moz_bookmarks AS b JOIN moz_places AS f ON b.fk = f.id
The error is: "no such column: a.id"
- - - - - - - - - - - - - - - -
I see that garbage characters are still exported:
“ . . . ” = “ . . . â€
’ = ’
I see. Cool. (I don't know why it takes away my line breaks)
This works:
SELECT b.title AS Title, c.url AS URL
FROM moz_bookmarks AS b JOIN moz_places AS c ON b.fk = c.id
- - - - -
This doesn't work. Any idea how I can get the ID there for the CSV export?
SELECT a.id AS ID, b.title AS Title, c.url AS URL
FROM moz_bookmarks AS b JOIN moz_places AS c ON b.fk = c.id
SQLiteManager: Likely SQL syntax error: SELECT a.id AS ID, b.title AS Title, c.url AS URL
FROM moz_bookmarks AS b JOIN moz_places AS c ON b.fk = c.id [ no such column: a.id ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]
That would be f.id because the ID is in the moz_places table.
SELECT b.title AS Bookmark, f.url AS URL FROM moz_bookmarks AS b JOIN moz_places AS f ON f.id = b.fk
Without aliases this becomes:
SELECT moz_places.id, moz_bookmarks.title AS Bookmark, moz_places.url AS URL FROM moz_bookmarks JOIN moz_places ON moz_places.id = moz_bookmarks.fk
. . . I'm seeing how to get what I want, this really helps a lot, thank you:
SELECT a.id AS ID, a.title AS Title, b.url AS URL, a.dateAdded
FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id
- - - - -
. . . but I think I'll be leaving off the dateAdded, because it's not very easy to get the human readable date:
https://support.mozilla.org/en-US/questions/972178#answer-482336
http://www.esqsoft.com/javascript_examples/date-to-epoch.htm
I'll just add the date by hand, as to when I add the bookmarks.
But we see the human readable date in the Library. Isn't there a way to convert it, and export that to the CSV?
- - - - -
I go to moz_bookmarks / Execute SQL tab / and paste the query there
Is that the best place to paste the code and run it? Does it matter?
It deletes what was there: SELECT * FROM tablename
- - - - -
Other questions - I'm wondering:
1.) Why can't I open places.sqlite in this program:
SQLite Database Browser . . . http://sourceforge.net/projects/sqlitebrowser/
2.) Why a CSV export with the fields you want isn't a current option
3.) Is there a way to make sure garbage characters don't get exported, like the apostrophe, and quotes above?
4.) How do you do Courier and line breaks in this forum
Vahaolana Nofidina
You can easily get dates in a readable format by converting the Unix Epoch format to a date/time format:
SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,'unixepoch') AS DateAdded FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id
I don't think that it matters which table is open when you paste the code in the "Execute SQL" text area to run the code.
What you see there by default in only some sample code that you can modify to suit what you want.
Current Firefox versions may be using an SQLite version that is newer and isn't supported by SQLite Database Browser.
You need to add a <br> to make the forum start on a new line or place text in <pre> and </pre> tags for larger block. A line that starts with a space is automatically placed between PRE tags.
. . . it's giving me GMT, I'm in EST . . . This fixes it:
- - - - - - - - - - - - - - - - - -
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
- - - - - - - - - - - - - - - - - -
This is great! Now I'll have the actual date I saved the URL, and not just the date I put it in my spreadsheet.
It would be good if when you saved a bookmark, there was the option to enter the date of the actual article itself. I like to have that for some web pages, for sorting purposes. I currently do this manually, by adding it to the Title, and later putting it in the correct column . . . but I don't always remember . . . when categorizing, and I want the date, sometimes I have to go back to the page and get it.
I think there's a benefit, also, to exporting your bookmarks from the browser. I think too many can make it slower, right?
I like having them in a spreadsheet, also, because then I can use Ctrl+F to find anything by keyword. You can do this with Tags in FF, but you can have multiple columns in Excel.
Is there anyway to write a macro for this in Firefox?
FF / Top left / Web Developer / SQLite Manager
Select a Query / Export Bookmarks to CSV / Run SQL
Actions / Save Result (CSV) to File / output.csv to Desktop / Save
Then I would run my Excel macro (please see screenshot)
http://code.google.com/p/sqlite-manager/wiki/ExecuteSQL
Save [and delete] Query By Name
SELECT Queries and other SQL statements can be stored in a SQLite database and rerun at a later time from the Execute SQL tab of SQLite Manager.
To enable this functionality, go to the Tools menu in the Menu bar and check the Use Table for Extension Data item.
To save a SQL statement, click on the Save Query icon in the Execute SQL tab, which will prompt you to enter a query name.
To retrieve the SQL query, select it by name from the drop down menu below the Enter SQL text box.
The text of the statement will be written to the Enter SQL text box where it can be modified if necessary before being run.
To delete a saved query statement from the menu run the following sql statement containing the query name in the Enter SQL text box.
DELETE FROM __sm_ext_mgmt WHERE type = "NamedQuery: query_name"
- - - - - - - - - - - - - - - -
I don't understand this syntax. Maybe I'm not entering it in the right place. None of these worked. I tried to run these like the query:
Syntax:
DELETE FROM __sm_ext_mgmt WHERE type = "NamedQuery: query_name"
My attempts:
DELETE FROM __sm_ext_mgmt WHERE type = "NamedQuery: Export Bookmarks to CSV"
DELETE FROM __sm_ext_mgmt WHERE type = "NamedQuery: Export Bookmarks to CSV with Date-Time"
DELETE FROM __sm_ext_mgmt WHERE type = "Export Bookmarks to CSV"
DELETE FROM __sm_ext_mgmt WHERE type = "Export Bookmarks to CSV with Date-Time"
- - - - - - - - - - - - - - - -
I went to this table / Browse & Search
__sm_ext_mgmt
I right-clicked on the two in there that were my queries / Delete / and they're gone from the list now
A lot of other stuff was in there, previous entries I did.
I think I can delete this type:
. . . QueryHistory
But I need to keep these 5 types:
. . . StructTree:ExpandedObjects
. . . StructTree:ExpandedCategories
. . . StructTree:AttachedDb
. . . Enabled
. . . BrowseTree:ColState:table:_sm_ext_mgmt
Everything still seems to be working properly
Would be good if Firefox could do simple, intuitive Macros, like in Excel, for use in running SQLite Manager queries. I can process the bookmarks in Excel in one-click (please see screenshot):
- - - - - - - - - - - - - - - -
Add a button and assign a macro to it in a worksheet (summarized)
Developer tab / Controls group / click Insert, and then under ActiveX Controls, click Command Button
Click the worksheet where you want the command button to appear
Controls group / View Code - this starts the Visual Basic Editor
In the left dropdown, change it from Worksheet to Commandbutton1. Make sure that Click is selected in the dropdown list on the right.
- - - - - - - - - - - - - - - -
Get the correct name of the Macro:
Tools / Macros / highlight the Macro - Edit - copy the name / click on the X in the top right to close
It's called: Process_Bookmarks_SQLite
- - - - - - - - - - - - - - - -
Put the name of the Macro in between:
Private Sub CommandButton1_Click()
Process_Bookmarks_SQLite
End Sub
- - - - - - - - - - - - - - - -
Save the VBA
File / Close and return to Microsoft Excel
- - - - - - - - - - - - - - - -
Modify the button:
Developer tab / Controls / Design Mode - right-click the Macro button / Properties / Categorized tab
BackColor - change it to something else, like green
Caption - Re-name it from CommandButton1 to Process Bookmarks SQLite - ok to use spaces in the name here
Close with X in the top right, that saves it
Re-size the button
Developer tab / Controls / Design Mode - click that to toggle it off
- - - - - - - - - - - - - - - -