Can Excel SQL query get metadata out of calibredb?

[unboggling]

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

[Manichean]

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.

[chaley]

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.

[Maggie-Me]

Just for point of info, I haven't looked at but google points to...
http://www.patthoyts.tk/sqlite3odbc.html

[unboggling]

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.

[Manichean]

Originally Posted by unboggling

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

[unboggling]

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.

[DoctorOhh]

Originally Posted by unboggling

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?

[unboggling]

Originally Posted by dwanthny

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

[Maggie-Me]

Originally Posted by unboggling

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.

[unboggling]

Originally Posted by Maggie-Me

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]

Originally Posted by unboggling

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?

[itimpi]

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

[unboggling]

Originally Posted by itimpi

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]

Here is link to the thread asking for help with the build:
https://www.mobileread.com/forums/sho...d.php?t=117188