Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Software > Calibre

Notices

Reply
 
Thread Tools Search this Thread
Old 01-17-2011, 02:15 PM   #1
unboggling
Wizard
unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.
 
Posts: 1,065
Karma: 858115
Join Date: Jan 2011
Device: Kobo Clara, Kindle Paperwhite 10
Can Excel SQL query get metadata out of calibredb?

This question needs some explaining.

Context:

1.0 Metadata going from Excel into calibre:

1.1 A couple weeks ago we discussed how to get author and title metadata out of Excel and into automatically created empty books in calibre. For each record in Excel Mac the formula to do that, when pasted into terminal, generated the empty book with title and author fields populated, then user could fill in the missing metadata using calibre metadata fetch. The formula that worked in Excel Mac is:

CONCATENATE("calibredb add -e -a """&Table1[@Author]&""" -t """&Table1[@Title]&"""")

2.0 Metadata going from calibre into Excel:

2.1. Discussed last week, one method is using a macro to grab Calibre catalog.csv data to populate cells in a worksheet. The csv to macro worksheet method works ok.

Question:

2.2. Is there another way using Excel SQL queries sent directly to calibredb to retrieve metadata back to Excel? All of a sudden I vaguely remembered some years ago doing queries from Excel to grab raw stock market data. And wondered if similar method might work using Excel-sent SQL queries to calibredb, bypassing the need for generating a catalog.csv. Does anyone have experience doing something like this?


--------------------------------------------------
FYI here is relevant excerpt from Excel Help:

To import data from a database, such as Microsoft SQL Server, you must have an ODBC driver that is compatible with Microsoft Query installed on your computer. Compatible ODBC drivers are available from third-party vendors. For more information, see ODBC drivers that are compatible with Excel for Mac. For more information about installing ODBC drivers, see Microsoft Query Help.
1. On the Data menu, point to Get External Data, and then click New Database Query.
2. Use Microsoft Query to connect to a data source and to create a query. When you have finished, click Return Data to import the data into Excel.
For more information about connecting to a data source and using Microsoft Query, see Microsoft Query Help.
3. In the Returning External Data to Microsoft Excel dialog box, do any of the following:
4. To
5. Do this
6. Choose query definition, refresh control, and data layout options
7. Click Properties.
8. Change the settings for a parameter query
9. Click Parameters.
10. Return the external data to the active sheet
11. Click Existing sheet. On your sheet, click the cell where you want to place the upper-left corner of the external data range, and then click OK.
12. Return the external data to a new sheet
13. Click New sheet, and then click OK.
Excel adds a new sheet to your workbook, and automatically pastes the external data range at the upper-left corner of the new sheet.
14. Create a PivotTable report by using the external data
15. Click PivotTable, and then click OK.

Notes*
*
• Excel 2011 for Mac cannot import data from OLE DB or OLAP data sources.
• By default, Excel for Mac uses tables to import data. To turn off tables when importing data, in the Returning External Data to Microsoft Excel dialog box, click Properties, and then clear the Use Table check box.
• If the external data source you want to access is not on your local computer, you might need to contact the administrator of the database for a password, user permission, or other connection information.

END POST
unboggling is offline   Reply With Quote
Old 01-17-2011, 03:37 PM   #2
Manichean
Wizard
Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.
 
Manichean's Avatar
 
Posts: 3,130
Karma: 91256
Join Date: Feb 2008
Location: Germany
Device: Cybook Gen3
You could, I believe, hack something together in VBScript (if I remember correctly, that's what the Office suite's internal scripting language is called) that calls calibredb to obtain a list of the books in the database. See the documentation of calibredb, I believe you ought to be able to use the --list command.
Manichean is offline   Reply With Quote
Advert
Old 01-17-2011, 04:53 PM   #3
chaley
Grand Sorcerer
chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.
 
Posts: 11,726
Karma: 6690881
Join Date: Jan 2010
Location: Notts, England
Device: Kobo Libra 2
You will need an OBDC data source for SQLite and the calibre DB. I have no idea of there is an ODBC driver for SQLite, but google will be your friend.
chaley is offline   Reply With Quote
Old 01-17-2011, 05:04 PM   #4
Maggie-Me
Zealot
Maggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with others
 
Maggie-Me's Avatar
 
Posts: 146
Karma: 2594
Join Date: May 2009
Device: Kindle PW,Sony 700, Sony 950 (Daily Edition),Nook Color,iPad
Just for point of info, I haven't looked at but google points to...
http://www.patthoyts.tk/sqlite3odbc.html
Maggie-Me is offline   Reply With Quote
Old 01-18-2011, 04:47 AM   #5
unboggling
Wizard
unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.
 
Posts: 1,065
Karma: 858115
Join Date: Jan 2011
Device: Kobo Clara, Kindle Paperwhite 10
Thanks for the responses.

@manichean: re calibredb list, the command line calibredb list options provide more flexibility than the gui catalog csv export options. With calibredb list I'd still need to send the output to a file then import it into excel. As you said, maybe a VB script could grab output of calibredb list automatically. I'm looking at the ODBC possibilites because that seems much more flexible if I can put it together and make it work.

@chaley & Maggie-Me, google is indeed my friend. The link M-M provided was good, it pointed further. Apparently there is only one ODBC Driver developer for SQLite, Christian Werner (http://www.ch-werner.de/sqliteodbc/). He hosts versions for Windows, Linux, and OS X. I installed to OS X and found a vital configuration app was missing from the package. Sent email to him explaining problem, just got response. Said he didn't have a Mac so couldn't help, "Can you build the driver from the source code?" I don't know, I haven't built from source code in 20 years. I guess that's the next step.

Putting together pieces of this SQL-query-to-calibredb-to-spreadsheet puzzle:

1. I have MS Excel with VB scripting and macro recording (if necessary).

2. I have MS Query which can front-end the outgoing SQL queries, and put returning data into a spreadsheet, which seems like a better way than using a zillion formulas in spreadsheet cells to grab the data.

3. I don't yet have a functioning ODBC driver (the intermediary between front-end query and back-end data handled by SQLite in calibre).

4. All the pieces will need configuration to work together, except maybe SQLite in calibre which I hope would just provide data sedately when asked politely.

For now, Item 3 appears to be the major piece of the puzzle that needs work.
unboggling is offline   Reply With Quote
Advert
Old 01-18-2011, 05:41 AM   #6
Manichean
Wizard
Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.Manichean is the 'tall, dark, handsome stranger' all the fortune-tellers are referring to.
 
Manichean's Avatar
 
Posts: 3,130
Karma: 91256
Join Date: Feb 2008
Location: Germany
Device: Cybook Gen3
Quote:
Originally Posted by unboggling View Post
@manichean: re calibredb list, the command line calibredb list options provide more flexibility than the gui catalog csv export options. With calibredb list I'd still need to send the output to a file then import it into excel. As you said, maybe a VB script could grab output of calibredb list automatically. I'm looking at the ODBC possibilites because that seems much more flexible if I can put it together and make it work.
ODBC may be more flexible, but i know squat about that, so I tried to figure out how I would approach the problem I was thinking along the lines of parsing the output of calibredb list in VBScript and building the spreadsheet internally. I believe you can call calibredb with the ID of a single book as well to obtain necessary details.
Manichean is offline   Reply With Quote
Old 01-18-2011, 05:58 AM   #7
unboggling
Wizard
unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.
 
Posts: 1,065
Karma: 858115
Join Date: Jan 2011
Device: Kobo Clara, Kindle Paperwhite 10
Manichean, the VB script method is probably what I'll end up doing since I have a feeling there will be problems with the ODBC method which I don't know much about either since I haven't used it in 10 years and my SQL is rusty, dim and cobwebby. Right now those are the paths that look promising.

And why (many of you may wonder) am I going through all this? Because calibre won't let me print or export to file it's pretty, formatted, gui metadata display.
unboggling is offline   Reply With Quote
Old 01-18-2011, 06:23 AM   #8
DoctorOhh
US Navy, Retired
DoctorOhh ought to be getting tired of karma fortunes by now.DoctorOhh ought to be getting tired of karma fortunes by now.DoctorOhh ought to be getting tired of karma fortunes by now.DoctorOhh ought to be getting tired of karma fortunes by now.DoctorOhh ought to be getting tired of karma fortunes by now.DoctorOhh ought to be getting tired of karma fortunes by now.DoctorOhh ought to be getting tired of karma fortunes by now.DoctorOhh ought to be getting tired of karma fortunes by now.DoctorOhh ought to be getting tired of karma fortunes by now.DoctorOhh ought to be getting tired of karma fortunes by now.DoctorOhh ought to be getting tired of karma fortunes by now.
 
DoctorOhh's Avatar
 
Posts: 9,864
Karma: 13806776
Join Date: Feb 2009
Location: North Carolina
Device: Icarus Illumina XL HD, Nexus 7
Quote:
Originally Posted by unboggling View Post
Because calibre won't let me print or export to file it's pretty, formatted, gui metadata display.
So exporting the catalog as a CSV file won't do what you're looking for?
DoctorOhh is offline   Reply With Quote
Old 01-18-2011, 07:03 AM   #9
unboggling
Wizard
unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.
 
Posts: 1,065
Karma: 858115
Join Date: Jan 2011
Device: Kobo Clara, Kindle Paperwhite 10
Quote:
Originally Posted by dwanthny View Post
So exporting the catalog as a CSV file won't do what you're looking for?
That works, particularly with a macro grabbing the csv data and putting it on a spreadsheet. I have the latter set up nicely for printing. The problem is that the easy way I did the macro and formulas, it's fine as is but clunky and time consuming to reset the sheet to a different column order or new custom columns. I'm not certain scripting can handle those changes, at least with my rusty scripting skills. So I started looking for a better way to deal with changes like that.

(The original catalog.csv file has unformatted data with the columns in alpha order. So the spreadsheet is set to switch columns around.)

Last edited by unboggling; 01-18-2011 at 07:23 AM. Reason: added comment re csv catalog being unformatted.
unboggling is offline   Reply With Quote
Old 01-18-2011, 07:13 AM   #10
Maggie-Me
Zealot
Maggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with othersMaggie-Me plays well with others
 
Maggie-Me's Avatar
 
Posts: 146
Karma: 2594
Join Date: May 2009
Device: Kindle PW,Sony 700, Sony 950 (Daily Edition),Nook Color,iPad
Quote:
Originally Posted by unboggling View Post
That works, particularly with a macro grabbing the csv data and putting it on a spreadsheet. I have the latter set up nicely for printing. The problem is that the easy way I did the macro and formulas, it's fine as is but clunky and time consuming to reset the sheet to a different column order or new custom columns. I'm not certain scripting can handle those changes, at least with my rusty scripting skills. So I started looking for a better way to deal with changes like that.
If and when you get the ODBC working you really don't have to worry about VB or scripting, once you have an external connection to a database Excel has the language built in to query the data anyway you want. Including order fields, parts of fields etc.. you will have a fair amount of the sql language available to you (not sure how close sqllite is to standard so you will have to see).

If you get that far let me know and I may be able to help more.
Maggie-Me is offline   Reply With Quote
Old 01-18-2011, 07:27 AM   #11
unboggling
Wizard
unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.
 
Posts: 1,065
Karma: 858115
Join Date: Jan 2011
Device: Kobo Clara, Kindle Paperwhite 10
Quote:
Originally Posted by Maggie-Me View Post
If and when you get the ODBC working you really don't have to worry about VB or scripting, once you have an external connection to a database Excel has the language built in to query the data anyway you want. Including order fields, parts of fields etc.. you will have a fair amount of the sql language available to you (not sure how close sqllite is to standard so you will have to see).

If you get that far let me know and I may be able to help more.
Great, thanks. Now I'm off to try a build from the ODBCdriver source code.
unboggling is offline   Reply With Quote
Old 01-18-2011, 08:16 AM   #12
unboggling
Wizard
unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.
 
Posts: 1,065
Karma: 858115
Join Date: Jan 2011
Device: Kobo Clara, Kindle Paperwhite 10
Quote:
Originally Posted by unboggling View Post
Now I'm off to try a build from the ODBCdriver source code.
Well, that's a lot more complex than I'd hoped. I'm going to start a new thread asking for help on this ODBC driver build on OS X. Any suggestions on where to put the new thread?
unboggling is offline   Reply With Quote
Old 01-18-2011, 08:20 AM   #13
itimpi
Wizard
itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.
 
Posts: 4,552
Karma: 950151
Join Date: Nov 2008
Device: Sony PRS-950, iphone/ipad (Marvin/iBooks/QuickReader)
if the output of the calibredb command has the information you want, then I would have thought it was much easier to load the csv file into the spreadsheet and manipulate it from there? A bit of VBA (rather than a recorded macro) could trivially re-arrange columns, apply formatting etc. That would avoid the need to construct complex ODBC queries (as well as the problem of getting the ODBC driver to work in the first place).
itimpi is offline   Reply With Quote
Old 01-18-2011, 08:41 AM   #14
unboggling
Wizard
unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.
 
Posts: 1,065
Karma: 858115
Join Date: Jan 2011
Device: Kobo Clara, Kindle Paperwhite 10
Quote:
Originally Posted by itimpi View Post
if the output of the calibredb command has the information you want, then I would have thought it was much easier to load the csv file into the spreadsheet and manipulate it from there? A bit of VBA (rather than a recorded macro) could trivially re-arrange columns, apply formatting etc. That would avoid the need to construct complex ODBC queries (as well as the problem of getting the ODBC driver to work in the first place).
Yes, calibredb list command does have the info I want and VB would certainly be easier for me to work with. Earlier I hadn't realized how complex it would be to get the right ODBC driver. Or that I'd have to build the driver from source - since I'm new to development in OS X and rusty in unix, I'd need a lot of help. So i'll probably crash and burn on the ODBC driver build. But wouldn't it be whimp-ish to to give up on ODBC/SQL method at this point? Wouldn't it be nice to say to calibre users (particularly power users working with huge datasets) who might want or need it: there is this capabilty to work the SQL thing, and here is how to do it?
unboggling is offline   Reply With Quote
Old 01-18-2011, 10:09 AM   #15
unboggling
Wizard
unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.unboggling ought to be getting tired of karma fortunes by now.
 
Posts: 1,065
Karma: 858115
Join Date: Jan 2011
Device: Kobo Clara, Kindle Paperwhite 10
Here is link to the thread asking for help with the build:
https://www.mobileread.com/forums/sho...d.php?t=117188
unboggling is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Defining Metadata with "calibredb add" command line function. macr0t0r Calibre 3 03-01-2012 07:36 PM
metadata query turbotoes Calibre 2 12-27-2010 06:19 AM
Bash Script/SQL Query for ISBN in filename? zenrhino Calibre 1 09-26-2010 08:55 AM
Editing the date in metadata query Cyclops Calibre 11 09-22-2010 11:58 AM
SQL Queries iain_benson Calibre 9 06-04-2009 06:08 PM


All times are GMT -4. The time now is 05:13 PM.


MobileRead.com is a privately owned, operated and funded community.