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
