Export catalog to Excel

myki · 03-14-2015, 03:28 AM

Greetings

I try to export my catalog to CSV (tried xml too) in order to import it into Excel.
If I export "simple" columns like authors, title, series etc, no problem...
But if I try to export with the "comments" column wich contains full of "", commas and CR/LF, the result can't be imported into Excel !

Can you help me, please ?

Thanx in advance

BetterRed · 03-14-2015, 05:20 AM

@myki - Use the calibredb --list command - it will let you specify a sensible separator character - 'hat', 'bang' or 'snail' should be OK.

BR

myki · 03-15-2015, 03:54 PM

Thanx for your help BetterRed.

I tried this :

Quote:

C:\Program Files\Calibre2>calibredb list -f authors,title,series,series_index,co
mments --separator ¤

And i get this error :

Quote:

id ¤authors ¤title ¤series ¤series_index¤commen
ts �Python function terminated unexpectedly
[Errno 0] Error (Error Code: 1)
Traceback (most recent call last):
File "site.py", line 132, in main
File "site.py", line 109, in run_entry_point
File "site-packages\calibre\library\cli.py", line 1554, in main
File "site-packages\calibre\library\cli.py", line 239, in command_list
File "site-packages\calibre\library\cli.py", line 148, in do_list
IOError: [Errno 0] Error

Am I doing something wrong ??

BetterRed · 03-15-2015, 04:42 PM

@myki

Should be no need to use full path to program, it is in PATH environment Environment Variable (unless you took it out )
Put the separator in rabbits ears
You probably need to specify a width otherwise output will default to width of your console window
You probably want to redirect the output to a csv file otherwise it'll just display on the console

Code:

calibredb list -f authors,title,series,series_index,comments --separator "¤" -w 1000000 > c:\testlist.csv

All of that said you may not like what you see, I just ran that command on a small test library and the comments were - hmmm - not as I would like. I've never wanted to put comments into a CSV list/catalogue so we're on the same learning curve.

Have a go with the suggested changes

BR

eschwartz · 03-15-2015, 04:56 PM

Bugreport against the CSV output?

comma and CRLF in the field means it (field) should be quoted.
literal quote character in a field should be represented by two consecutive quote characters.

Do that and Excel should handle it just fine.

BetterRed · 03-15-2015, 06:30 PM

@eschwartz - Good thinking. But right now that means editing the output file with a text editor - yes? BTW the OP isn't using a comma - is using a '¤'

@myki - you'll probably need to use a regular expression to wrap the text after fifth '¤' up until next '¤' in '"'.

If needed someone like eschwartz can help you with that.

You'll need to import the file into Excel using the Text Import Window so that you can tell Excel to use '¤' as a separator - you'll probably also want to set word wrap on the comments column

BR

eschwartz · 03-15-2015, 06:38 PM

I know what the OP was using as a separator

on your advice for using "calibredb list". Hopefully we can get the standard catalog output to work.

Meantime, we shall see what can be done with "calibredb list", separately from what shoulda-coulda-woulda be with the catalog output.

I will try some tests when I get back to the computer, assuming myki hasn't already reported success.

BetterRed · 03-15-2015, 07:05 PM

The CSV catalog desperately needs to allow the user to specify the separator, I think I may have requested that a long time ago - but the catalog dev never got back to me.

And for CSV at least, an option to not create a Book in the library.

Flashback - CSV catalogs and lost souls

BR

eschwartz · 03-15-2015, 09:21 PM

By the way, regarding calibredb list outputting weird stuff -- that would be because the solution will involve parsing json outputted by calibredb list --for-machine.

PeterT · 03-15-2015, 10:12 PM

Load it into LibreOffice's spreadsheet. Seems to work like a charm there.

I do remember Dalton stressing in one of his plugins that a required CSV file HAD to be made by LO due to Excel having problems with Unicode documents.

kovidgoyal · 03-15-2015, 11:08 PM

Excel's default CSV import is a bit of a joke. For instance, the delimiter it uses depends on the locale!

You need to use the Text Import wizard to get excel to do it properly, see for instance:
http://blogmines.com/blog/how-to-imp...in-excel-2013/

eschwartz · 03-15-2015, 11:38 PM

Huh, so really it works just fine?

@PeterT -- DaltonST only said Excel apparently cannot export proper UTF-8, which apparently his plugin needs. No word on it having fundamentally better understanding of CSV -- though Kovid indicates the issue may just be the import settings?

I know LibreOffice pops up the import wizard all the time...

Well, gosh darn it all!

I knew there must be a reason I preferred LibreOffice (besides the ability to print to PDF without external installed PDF drivers).

kovidgoyal · 03-15-2015, 11:42 PM

As far as I know, the csv output works just fine. All fields are quoted and all quotes inside fields are doubled. It is Excel that has issues. See line 155 of csv_xml.py

eschwartz · 03-15-2015, 11:56 PM

Looks good then.

Whoops, all that overreaction for naught.

Can't see the forest for the trees...

myki · 03-16-2015, 02:58 AM

Thank you for your help everybody

It seems like Excel isn't able to deal with the quotes !
So I followed your advice and used LibreOffice, and it worked at first attempt, without doing anything on the csv file directly issued of Calibre !

When the content of the comments field starts with a quote, when a field is empty (resulting with "" in the csv file), Excel is lost !

Just for the fun I tried the calibre-db list command with the ¤ as a separator and with all fields, and it worked directly in Excel, but the result is less clear than in OpenOffice (like all the html tags in the comments field, or the tags in the form [u'tag1',u'tag2'] etc...).

Conclusion :
In my opinion, nothing has to be changed in the way the catalog export is done in CSV format, but a new txt export format could be developped, offering the choice of the separator and the surrounding field character.

Anyway, thank you for this wonderfull program... and its comunity

(sorry for my bad english too)

03-14-2015, 03:28 AM	#1
myki Zealot Posts: 126 Karma: 20236 Join Date: May 2014 Device: Kinde PW v1, Kobo H2O, Onyx Boox T68	Export catalog to Excel Greetings I try to export my catalog to CSV (tried xml too) in order to import it into Excel. If I export "simple" columns like authors, title, series etc, no problem... But if I try to export with the "comments" column wich contains full of "", commas and CR/LF, the result can't be imported into Excel ! Can you help me, please ? Thanx in advance

03-15-2015, 04:42 PM	#4
BetterRed null operator (he/him) Posts: 21,716 Karma: 29711016 Join Date: Mar 2012 Location: Sydney Australia Device: none	@myki Should be no need to use full path to program, it is in PATH environment Environment Variable (unless you took it out ) Put the separator in rabbits ears You probably need to specify a width otherwise output will default to width of your console window You probably want to redirect the output to a csv file otherwise it'll just display on the console Code: calibredb list -f authors,title,series,series_index,comments --separator "¤" -w 1000000 > c:\testlist.csv All of that said you may not like what you see, I just ran that command on a small test library and the comments were - hmmm - not as I would like. I've never wanted to put comments into a CSV list/catalogue so we're on the same learning curve. Have a go with the suggested changes BR

03-15-2015, 04:56 PM	#5
eschwartz Ex-Helpdesk Junkie Posts: 19,421 Karma: 85400180 Join Date: Nov 2012 Location: The Beaten Path, USA, Roundworld, This Side of Infinity Device: Kindle Touch fw5.3.7 (Wifi only)	Bugreport against the CSV output? comma and CRLF in the field means it (field) should be quoted. literal quote character in a field should be represented by two consecutive quote characters. Do that and Excel should handle it just fine. Last edited by eschwartz; 03-15-2015 at 05:04 PM.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calibre - Export/Import Settings and Catalog	joseph.voros	Library Management	4	06-13-2014 11:19 PM
Export list to excel?	Belle2Be	Library Management	7	01-15-2014 06:14 AM
Select books based on info from Conversion > Export Catalog	halfcore	Library Management	2	10-22-2013 06:15 PM
mobi catalog = periodical, ePub catalog = book	trekchick	Library Management	7	03-12-2011 01:11 PM
Export Catalog and the stored cover?	Hitch	Library Management	2	02-24-2011 10:50 PM

03-14-2015, 05:20 AM	#2
BetterRed null operator (he/him) Posts: 21,716 Karma: 29711016 Join Date: Mar 2012 Location: Sydney Australia Device: none	@myki - Use the calibredb --list command - it will let you specify a sensible separator character - 'hat', 'bang' or 'snail' should be OK. BR

03-15-2015, 06:30 PM	#6
BetterRed null operator (he/him) Posts: 21,716 Karma: 29711016 Join Date: Mar 2012 Location: Sydney Australia Device: none	@eschwartz - Good thinking. But right now that means editing the output file with a text editor - yes? BTW the OP isn't using a comma - is using a '¤' @myki - you'll probably need to use a regular expression to wrap the text after fifth '¤' up until next '¤' in '"'. If needed someone like eschwartz can help you with that. You'll need to import the file into Excel using the Text Import Window so that you can tell Excel to use '¤' as a separator - you'll probably also want to set word wrap on the comments column BR

03-15-2015, 06:38 PM	#7
eschwartz Ex-Helpdesk Junkie Posts: 19,421 Karma: 85400180 Join Date: Nov 2012 Location: The Beaten Path, USA, Roundworld, This Side of Infinity Device: Kindle Touch fw5.3.7 (Wifi only)	I know what the OP was using as a separator on your advice for using "calibredb list". Hopefully we can get the standard catalog output to work. Meantime, we shall see what can be done with "calibredb list", separately from what shoulda-coulda-woulda be with the catalog output. I will try some tests when I get back to the computer, assuming myki hasn't already reported success.

03-15-2015, 07:05 PM	#8
BetterRed null operator (he/him) Posts: 21,716 Karma: 29711016 Join Date: Mar 2012 Location: Sydney Australia Device: none	The CSV catalog desperately needs to allow the user to specify the separator, I think I may have requested that a long time ago - but the catalog dev never got back to me. And for CSV at least, an option to not create a Book in the library. Flashback - CSV catalogs and lost souls BR

03-15-2015, 09:21 PM	#9
eschwartz Ex-Helpdesk Junkie Posts: 19,421 Karma: 85400180 Join Date: Nov 2012 Location: The Beaten Path, USA, Roundworld, This Side of Infinity Device: Kindle Touch fw5.3.7 (Wifi only)	By the way, regarding calibredb list outputting weird stuff -- that would be because the solution will involve parsing json outputted by calibredb list --for-machine.

03-15-2015, 10:12 PM	#10
PeterT Grand Sorcerer Posts: 13,487 Karma: 78910112 Join Date: Nov 2007 Location: Toronto Device: Libra H2O, Libra Colour	Load it into LibreOffice's spreadsheet. Seems to work like a charm there. I do remember Dalton stressing in one of his plugins that a required CSV file HAD to be made by LO due to Excel having problems with Unicode documents.

03-15-2015, 11:08 PM	#11
kovidgoyal creator of calibre Posts: 45,339 Karma: 27182818 Join Date: Oct 2006 Location: Mumbai, India Device: Various	Excel's default CSV import is a bit of a joke. For instance, the delimiter it uses depends on the locale! You need to use the Text Import wizard to get excel to do it properly, see for instance: http://blogmines.com/blog/how-to-imp...in-excel-2013/

03-15-2015, 11:38 PM	#12
eschwartz Ex-Helpdesk Junkie Posts: 19,421 Karma: 85400180 Join Date: Nov 2012 Location: The Beaten Path, USA, Roundworld, This Side of Infinity Device: Kindle Touch fw5.3.7 (Wifi only)	Huh, so really it works just fine? @PeterT -- DaltonST only said Excel apparently cannot export proper UTF-8, which apparently his plugin needs. No word on it having fundamentally better understanding of CSV -- though Kovid indicates the issue may just be the import settings? I know LibreOffice pops up the import wizard all the time... Well, gosh darn it all! I knew there must be a reason I preferred LibreOffice (besides the ability to print to PDF without external installed PDF drivers).

03-15-2015, 11:42 PM	#13
kovidgoyal creator of calibre Posts: 45,339 Karma: 27182818 Join Date: Oct 2006 Location: Mumbai, India Device: Various	As far as I know, the csv output works just fine. All fields are quoted and all quotes inside fields are doubled. It is Excel that has issues. See line 155 of csv_xml.py

03-15-2015, 11:56 PM	#14
eschwartz Ex-Helpdesk Junkie Posts: 19,421 Karma: 85400180 Join Date: Nov 2012 Location: The Beaten Path, USA, Roundworld, This Side of Infinity Device: Kindle Touch fw5.3.7 (Wifi only)	Looks good then. Whoops, all that overreaction for naught. Can't see the forest for the trees...

Advert

Advert

03-16-2015, 02:58 AM	#15
myki Zealot Posts: 126 Karma: 20236 Join Date: May 2014 Device: Kinde PW v1, Kobo H2O, Onyx Boox T68	Thank you for your help everybody It seems like Excel isn't able to deal with the quotes ! So I followed your advice and used LibreOffice, and it worked at first attempt, without doing anything on the csv file directly issued of Calibre ! When the content of the comments field starts with a quote, when a field is empty (resulting with "" in the csv file), Excel is lost ! Just for the fun I tried the calibre-db list command with the ¤ as a separator and with all fields, and it worked directly in Excel, but the result is less clear than in OpenOffice (like all the html tags in the comments field, or the tags in the form [u'tag1',u'tag2'] etc...). Conclusion : In my opinion, nothing has to be changed in the way the catalog export is done in CSV format, but a new txt export format could be developped, offering the choice of the separator and the surrounding field character. Anyway, thank you for this wonderfull program... and its comunity (sorry for my bad english too)