X
Tap here to go to the mobile version of the site.

Support Forum

Bookmarks - Convert from places.sqlite to .xlsx (Excel) - How?

Posted

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.

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.

Modified by sludge7051-x

Chosen solution

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.

Read this answer in context 1

Additional System Details

Installed Plug-ins

  • Shockwave Flash 13.0 r0
  • The QuickTime Plugin allows you to view a wide variety of multimedia content in Web pages. For more information, visit the QuickTime Web site.
  • Picasa plugin
  • 5.1.30214.0
  • DivX Web Player version 3.1.0.13
  • NVIDIA 3D Vision plugin for Mozilla browsers
  • NVIDIA 3D Vision Streaming plugin for Mozilla browsers
  • GEPlugin
  • DivX VOD Helper Plug-in
  • Garmin Communicator Plug-In 4.0.3.0
  • BrowserPlus -- Improve your browser! -- http://browserplus.yahoo.com/
  • The plug-in allows you to open and edit files using Microsoft Office applications
  • Office Authorization plug-in for NPAPI browsers

Application

  • User Agent: Mozilla/5.0 (Windows NT 6.2; rv:28.0) Gecko/20100101 Firefox/28.0

More Information

John99 971 solutions 13138 answers

Helpful Reply

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.

  1. .json The backup is in.json format and may be used to backup and overwrite bookmark files in the database
  2. .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.

TL;DR<br /> 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, '' <u>Formats</u> <br /> 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 #*[[Restore bookmarks from backup or move them to another computer]] # '''.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. #* [[Export Firefox bookmarks to an HTML file to back up or transfer bookmarks]] 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. <u>Firefox Sync</u><br /> 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) * [[How do I set up Firefox Sync?]] * [[How to update to the new Firefox Sync]] 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. * [[Use the Profile Manager to create and remove Firefox profiles]]

Question owner

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.

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.

Helpful Reply

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 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
cor-el
  • Top 10 Contributor
  • Moderator
17578 solutions 159013 answers

You will have to do an SQL query and export the result as CSV.

SELECT b.title AS Bookmark, f.url AS URL
FROM moz_bookmarks AS b JOIN moz_places AS f ON f.id = b.fk
You will have to do an SQL query and export the result as CSV. <pre><nowiki>SELECT b.title AS Bookmark, f.url AS URL FROM moz_bookmarks AS b JOIN moz_places AS f ON f.id = b.fk</nowiki></pre>

Question owner

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 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: “ . . . ” = “ . . . †’ = ’
cor-el
  • Top 10 Contributor
  • Moderator
17578 solutions 159013 answers

"b" and "f" are aliases that are set via the FROM line: "b" stands for the moz_bookmarks table (moz_bookmarks AS b) and "f" for the moz_places table (moz_places AS f) ON f.id = b.fk links the two tables via the id and the fk columns.

  • FROM moz_bookmarks AS b JOIN moz_places AS f ON f.id = b.fk
"b" and "f" are aliases that are set via the FROM line: "b" stands for the moz_bookmarks table (moz_bookmarks AS b) and "f" for the moz_places table (moz_places AS f) ON f.id = b.fk links the two tables via the id and the fk columns. *FROM moz_bookmarks AS b JOIN moz_places AS f ON f.id = b.fk *http://www.sqlite.org/lang_select.html#fromclause

Question owner

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]

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]
cor-el
  • Top 10 Contributor
  • Moderator
17578 solutions 159013 answers

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
That would be f.id because the ID is in the moz_places table. <pre><nowiki>SELECT b.title AS Bookmark, f.url AS URL FROM moz_bookmarks AS b JOIN moz_places AS f ON f.id = b.fk</nowiki></pre> Without aliases this becomes: <pre><nowiki>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</nowiki></pre>

Question owner

. . . 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

. . . 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
cor-el
  • Top 10 Contributor
  • Moderator
17578 solutions 159013 answers

Chosen Solution

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.

You can easily get dates in a readable format by converting the Unix Epoch format to a date/time format: <pre><nowiki>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</nowiki></pre> 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.<br /> 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 &lt;br> to make the forum start on a new line or place text in &lt;pre> and &lt;/pre> tags for larger block. A line that starts with a space is automatically placed between PRE tags.

Question owner

http://stackoverflow.com/questions/381371/sqlite-current-timestamp-is-in-gmt-not-the-timezone-of-the-machine

. . . 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://stackoverflow.com/questions/381371/sqlite-current-timestamp-is-in-gmt-not-the-timezone-of-the-machine<BR><BR> . . . it's giving me GMT, I'm in EST . . . This fixes it:<BR><BR> - - - - - - - - - - - - - - - - - -<BR> SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,'unixepoch','localtime') AS Date<BR><BR> FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id<BR> - - - - - - - - - - - - - - - - - -<BR><BR> 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.<BR><BR> 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.<BR><BR> I think there's a benefit, also, to exporting your bookmarks from the browser. I think too many can make it slower, right?<BR><BR> 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.<BR><BR> Is there anyway to write a macro for this in Firefox?<BR> FF / Top left / Web Developer / SQLite Manager<BR> Select a Query / Export Bookmarks to CSV / Run SQL<BR> Actions / Save Result (CSV) to File / output.csv to Desktop / Save<BR><BR> Then I would run my Excel macro (please see screenshot)

Question owner

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

http://code.google.com/p/sqlite-manager/wiki/ExecuteSQL<BR> Save [and delete] Query By Name<BR><BR> 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.<BR><BR> To enable this functionality, go to the Tools menu in the Menu bar and check the Use Table for Extension Data item.<BR><BR> 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.<BR><BR> To retrieve the SQL query, select it by name from the drop down menu below the Enter SQL text box.<BR><BR> The text of the statement will be written to the Enter SQL text box where it can be modified if necessary before being run.<BR><BR> To delete a saved query statement from the menu run the following sql statement containing the query name in the Enter SQL text box.<BR><BR> DELETE FROM __sm_ext_mgmt WHERE type = "NamedQuery: query_name"<BR><BR> - - - - - - - - - - - - - - - -<BR><BR> 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:<BR><BR> Syntax:<BR> DELETE FROM __sm_ext_mgmt WHERE type = "NamedQuery: query_name"<BR><BR> My attempts:<BR> DELETE FROM __sm_ext_mgmt WHERE type = "NamedQuery: Export Bookmarks to CSV"<BR> DELETE FROM __sm_ext_mgmt WHERE type = "NamedQuery: Export Bookmarks to CSV with Date-Time"<BR><BR> DELETE FROM __sm_ext_mgmt WHERE type = "Export Bookmarks to CSV"<BR> DELETE FROM __sm_ext_mgmt WHERE type = "Export Bookmarks to CSV with Date-Time"<BR><BR> - - - - - - - - - - - - - - - -<BR><BR> I went to this table / Browse & Search<BR> __sm_ext_mgmt<BR><BR> I right-clicked on the two in there that were my queries / Delete / and they're gone from the list now<BR><BR> A lot of other stuff was in there, previous entries I did.<BR><BR> I think I can delete this type:<BR> . . . QueryHistory<BR><BR> But I need to keep these 5 types:<BR> . . . StructTree:ExpandedObjects<BR> . . . StructTree:ExpandedCategories<BR> . . . StructTree:AttachedDb<BR> . . . Enabled<BR> . . . BrowseTree:ColState:table:_sm_ext_mgmt<BR><BR> Everything still seems to be working properly

Question owner



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)

http://office.microsoft.com/en-us/excel-help/add-a-button-and-assign-a-macro-to-it-in-a-worksheet-HP010236676.aspx

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

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

<BR><BR> 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):<BR><BR> - - - - - - - - - - - - - - - -<BR><BR> Add a button and assign a macro to it in a worksheet (summarized)<BR><BR> http://office.microsoft.com/en-us/excel-help/add-a-button-and-assign-a-macro-to-it-in-a-worksheet-HP010236676.aspx<BR><BR> Developer tab / Controls group / click Insert, and then under ActiveX Controls, click Command Button<BR><BR> Click the worksheet where you want the command button to appear<BR><BR> Controls group / View Code - this starts the Visual Basic Editor<BR><BR> In the left dropdown, change it from Worksheet to Commandbutton1. Make sure that Click is selected in the dropdown list on the right.<BR><BR> - - - - - - - - - - - - - - - -<BR><BR> Get the correct name of the Macro:<BR><BR> Tools / Macros / highlight the Macro - Edit - copy the name / click on the X in the top right to close<BR><BR> It's called: Process_Bookmarks_SQLite<BR><BR> - - - - - - - - - - - - - - - -<BR><BR> Put the name of the Macro in between:<BR><BR> Private Sub CommandButton1_Click()<BR> Process_Bookmarks_SQLite<BR> End Sub<BR><BR> - - - - - - - - - - - - - - - -<BR><BR> Save the VBA<BR> File / Close and return to Microsoft Excel<BR><BR> - - - - - - - - - - - - - - - -<BR><BR> Modify the button:<BR> Developer tab / Controls / Design Mode - right-click the Macro button / Properties / Categorized tab<BR><BR> BackColor - change it to something else, like green<BR> Caption - Re-name it from CommandButton1 to Process Bookmarks SQLite - ok to use spaces in the name here<BR> Close with X in the top right, that saves it<BR> Re-size the button<BR> Developer tab / Controls / Design Mode - click that to toggle it off<BR><BR> - - - - - - - - - - - - - - - -