Help . . . I want to Export my Firefox Bookmarks to .CSV in one click, using SQLite3 in a .BAT file

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Help . . . I want to Export my Firefox Bookmarks to .CSV in one click, using SQLite3 in a .BAT file

e-mail

This "view" works in "DB Browser for SQLite" to get my Firefox Bookmarks . . . I then export them to Desktop as a .CSV, and run an Excel macro on them . . .

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

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

But then I thought, why not use SQLite3 in a .BAT file, and make it "one-click" ?

The following code is what I have put into a .BAT file - I don't see any errors, and both just create a blank .CSV on my Desktop.1.) Broken out, for examination
2.) Quotes and apostrophes - not sure what's necessary - it seems like quotes are probably necessary, if there's a space in the path
3.) Just has apostrophes - not sure what's necessary
Any idea why this isn't working?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
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"
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
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"
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
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
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help . . . I want to Export my Firefox Bookmarks to .CSV in one click, using SQLite3 in a .BAT file

Clemens Ladisch
You have to do two things to run SQL statements from a batch file:
1. Use quotes so that all parameters to sqlite3.exe are a single parameter; and
2. Use quotes so that SQL strings are delimited correctly.

e-mail wrote:
> sqlite3.exe -csv "C:\...\places.sqlite" "SELECT ... datetime(...,"unixepoch","localtime") AS Date FROM ..."

Double quotes are interpreted by cmd.exe to delimit parameters to sqlite3.exe,
so they will not show up in the actual SQL statement as seen by sqlite3.

> sqlite3.exe -csv C:\...\places.sqlite SELECT ... datetime(...,'unixepoch','localtime') AS Date FROM ...

Without quotes, there are too many parameters.


In SQL, strings should use single quotes (double quotes are just for bug
compatibility with MySQL); and cmd.exe expects double quotes for parameters.
So this is easy:

  sqlite3.exe -csv "C:\...\places.sqlite" "SELECT ... datetime(...,'unixepoch','localtime') AS Date FROM ..."


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users