a bug with csv catalog

Noughty · 10-15-2011, 12:32 PM

I tried many combinations with only simple columns, with complicates ones. The result is always unusable since the first category isn't in quotes. I always get results like this:

Code:

Author,Author2,"title"

or

Code:

epub,pdf,txt,"title","Author,Author2"

while it should be

Code:

"epub,pdf,txt","title","Author,Author2"

So splitting to columns is impossible. The best result I get using " as a separator but sometimes " can be in fields and it messes it up too, just not as much.

There are no settings for creating csv files so I didn't mess it up. What could cause this?

Because of this I created unnecessary complicated columns in calibre (so I could export one already modified column without needing to split into columns) which increases workload for calibre a lot.

kovidgoyal · 10-15-2011, 08:14 PM

What difference do quotes make to splitting columns? As long as the comma does not appear unescaped in the data fields the actual charaacters in the fields are irrelevant.

Noughty · 10-16-2011, 04:12 AM

I don't know what unescaped means.

Commas are in almost all fields I have. And I cannot choose a specific order so that the first would be a field which definitely doesn't have any commas. Being able to choose the order would solve the issue as I could choose field which has no commas.

What field can I use to make it correct?
If first field is title it has commas in it, so it gets split incorrectly for some entries. And I end up with different amount of columns for some entries.
If first field is formats, it definitively gets split if multiple formats.
I even tried cover as first field but it has title name in it so again has a comma sometimes.
Authors are useless if there are more than one author, since they are separated with comma.

If first field is in quotes it ignores commas inside that field. Since there are no quotes it splits the field itself, since it splits at every comma not in quotes.

I can't even make a list with csv for Author-Title.
I end up with up to 5 columns for some entries if there are multiple authors. For anthologies I might end up with 20 columns, when I need 2.

I made over 40 lists now and none got split correctly.

I use excel and txt for splitting. Save text in txt file and open it with excel. Separate using comma.

As I wrote above for this entry

Code:

epub,pdf,txt,"title","Author,Author2"

I get 5 columns instead of 3 columns:

Code:

epub
pdf
txt
title
author,author2

How to get 3 columns?

Code:

epub,pdf,txt
title
Author,author2

kovidgoyal · 10-16-2011, 07:58 AM

I do not see that behavior. Here is what entries look like when I generate a csv catalog in calibre

"Kindle","txt","My Clippings"
"calibre","epub","xx,xxx"
"Colleen McCullough","epub, mobi, zip","Caesar"

Not the comma in the last entry is in between quotes. As I said before, there are no unescaped commas.

Noughty · 10-16-2011, 08:01 AM

That's why I am saying I have a bug. I should get the result as you posted, but I in your case would get:

Code:

Colleen McCullough,"epub, mobi, zip","Caesar"

For this entry it is ok, since only one author, but if two authors - they will get separated.

There are no settings for csv catalog what I know of, so I didn't mess it up. I don't get an error message. I just get wrong csv file. My calibre is up to date.

Microsoft office 2007.

I'd really like to fix this somehow

SoulStyle · 10-16-2011, 09:15 AM

I notice the same behaviour with current Calibre 0.8.22. Regardless which fields I choose to export, the first column is always unescaped and all subsequent are escaped with "".

Example 1:

Code:

formats,size,title
epub,"334116","Feuermagie"

Example 2:

Code:

authors,formats,rating,series_index
Shana Abé, Marianne Schmidt,"epub","7","1.0"

Strange enough, this seems to be an Excel (2010) problem. When opening the csv with notepad, all columns are escaped.

Kind regards

kovidgoyal · 10-16-2011, 09:25 AM

If you want to test the output of calibre you should open it with notepad or another text editor, that will show you exactly what calibre is producing. As for Excel, I have no idea, I don't use it.

SoulStyle · 10-16-2011, 09:28 AM

As a workaround try renaming the mybooks.csv to mybooks.txt and in Excel use File --> Open

Kind regards

Noughty · 10-16-2011, 09:45 AM

Quote:

Originally Posted by SoulStyle

As a workaround try renaming the mybooks.csv to mybooks.txt and in Excel use File --> Open

Kind regards

Thank you a lot. It worked. Now I get it all in quotes as it should be. I wonder why in original csv file it's incorrect, but I don't care. I find it even easier to just change file extension since I used to copy from csv file to new txt file.

So for me problem solved

CWatkinsNash · 10-16-2011, 06:07 PM

I just wanted to add that I've had this problem with Excel 2010 too. The same csv file opened in OpenOffice was just fine. What's even worse is, if you then try to save the csv file after making changes in Excel, it does something weird to the commas (or other delimiters in any given file) and the result upon reopening is everything in ONE column.

So, if I need to keep the file as a csv for whatever reason (import into another app or web site) then I only make changes in OpenOffice. If I just need to use it as a spreadsheet, I save it as an Excel 2010 workbook right after opening it and work in that file format. In OOo, I can edit and save a csv file repeatedly and it never alters whatever settings I specify upon opening the file.

I'm sure there's some setting in Excel upon saving that would stop this but I haven't had time to get back to it or to look into it further.

10-15-2011, 12:32 PM	#1
Noughty Addict Posts: 352 Karma: 103850 Join Date: Apr 2011 Device: Kindle NT	a bug with csv catalog (solution found) I tried many combinations with only simple columns, with complicates ones. The result is always unusable since the first category isn't in quotes. I always get results like this: Code: Author,Author2,"title" or Code: epub,pdf,txt,"title","Author,Author2" while it should be Code: "epub,pdf,txt","title","Author,Author2" So splitting to columns is impossible. The best result I get using " as a separator but sometimes " can be in fields and it messes it up too, just not as much. There are no settings for creating csv files so I didn't mess it up. What could cause this? Because of this I created unnecessary complicated columns in calibre (so I could export one already modified column without needing to split into columns) which increases workload for calibre a lot. Last edited by Noughty; 10-16-2011 at 10:28 AM.

10-16-2011, 04:12 AM	#3
Noughty Addict Posts: 352 Karma: 103850 Join Date: Apr 2011 Device: Kindle NT	I don't know what unescaped means. Commas are in almost all fields I have. And I cannot choose a specific order so that the first would be a field which definitely doesn't have any commas. Being able to choose the order would solve the issue as I could choose field which has no commas. What field can I use to make it correct? If first field is title it has commas in it, so it gets split incorrectly for some entries. And I end up with different amount of columns for some entries. If first field is formats, it definitively gets split if multiple formats. I even tried cover as first field but it has title name in it so again has a comma sometimes. Authors are useless if there are more than one author, since they are separated with comma. If first field is in quotes it ignores commas inside that field. Since there are no quotes it splits the field itself, since it splits at every comma not in quotes. I can't even make a list with csv for Author-Title. I end up with up to 5 columns for some entries if there are multiple authors. For anthologies I might end up with 20 columns, when I need 2. I made over 40 lists now and none got split correctly. I use excel and txt for splitting. Save text in txt file and open it with excel. Separate using comma. As I wrote above for this entry Code: epub,pdf,txt,"title","Author,Author2" I get 5 columns instead of 3 columns: Code: epub pdf txt title author,author2 How to get 3 columns? Code: epub,pdf,txt title Author,author2 Last edited by Noughty; 10-16-2011 at 04:41 AM.

10-16-2011, 08:01 AM	#5
Noughty Addict Posts: 352 Karma: 103850 Join Date: Apr 2011 Device: Kindle NT	That's why I am saying I have a bug. I should get the result as you posted, but I in your case would get: Code: Colleen McCullough,"epub, mobi, zip","Caesar" For this entry it is ok, since only one author, but if two authors - they will get separated. There are no settings for csv catalog what I know of, so I didn't mess it up. I don't get an error message. I just get wrong csv file. My calibre is up to date. Microsoft office 2007. I'd really like to fix this somehow Last edited by Noughty; 10-16-2011 at 08:04 AM.

10-16-2011, 09:15 AM	#6
SoulStyle Enthusiast Posts: 26 Karma: 22 Join Date: May 2011 Device: Kindle 3	I notice the same behaviour with current Calibre 0.8.22. Regardless which fields I choose to export, the first column is always unescaped and all subsequent are escaped with "". Example 1: Code: formats,size,title epub,"334116","Feuermagie" Example 2: Code: authors,formats,rating,series_index Shana Abé, Marianne Schmidt,"epub","7","1.0" Strange enough, this seems to be an Excel (2010) problem. When opening the csv with notepad, all columns are escaped. Kind regards Last edited by SoulStyle; 10-16-2011 at 09:19 AM.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

10-15-2011, 08:14 PM	#2
kovidgoyal creator of calibre Posts: 43,839 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	What difference do quotes make to splitting columns? As long as the comma does not appear unescaped in the data fields the actual charaacters in the fields are irrelevant.

10-16-2011, 07:58 AM	#4
kovidgoyal creator of calibre Posts: 43,839 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	I do not see that behavior. Here is what entries look like when I generate a csv catalog in calibre "Kindle","txt","My Clippings" "calibre","epub","xx,xxx" "Colleen McCullough","epub, mobi, zip","Caesar" Not the comma in the last entry is in between quotes. As I said before, there are no unescaped commas.

10-16-2011, 09:25 AM	#7
kovidgoyal creator of calibre Posts: 43,839 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	If you want to test the output of calibre you should open it with notepad or another text editor, that will show you exactly what calibre is producing. As for Excel, I have no idea, I don't use it.

10-16-2011, 09:28 AM	#8
SoulStyle Enthusiast Posts: 26 Karma: 22 Join Date: May 2011 Device: Kindle 3	As a workaround try renaming the mybooks.csv to mybooks.txt and in Excel use File --> Open Kind regards

10-16-2011, 06:07 PM	#10
CWatkinsNash IOC Chief Archivist Posts: 3,950 Karma: 53868218 Join Date: Dec 2010 Location: Fruitland Park, FL, USA Device: Meebook M7, Paperwhite 2021, Fire HD 8+, Fire HD 10+, Lenovo Tab P12	I just wanted to add that I've had this problem with Excel 2010 too. The same csv file opened in OpenOffice was just fine. What's even worse is, if you then try to save the csv file after making changes in Excel, it does something weird to the commas (or other delimiters in any given file) and the result upon reopening is everything in ONE column. So, if I need to keep the file as a csv for whatever reason (import into another app or web site) then I only make changes in OpenOffice. If I just need to use it as a spreadsheet, I save it as an Excel 2010 workbook right after opening it and work in that file format. In OOo, I can edit and save a csv file repeatedly and it never alters whatever settings I specify upon opening the file. I'm sure there's some setting in Excel upon saving that would stop this but I haven't had time to get back to it or to look into it further.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Converting csv files to Vcard	syclone	enTourage eDGe	4	11-21-2019 02:10 AM
How to use "catalog CSV/XML" plugin?	katsu	Library Management	4	10-14-2011 11:07 AM
calibre>csv>book crawler?	lmk	Library Management	4	04-16-2011 07:20 PM
mobi catalog = periodical, ePub catalog = book	trekchick	Library Management	7	03-12-2011 01:11 PM
Export to CSV/XLS?	highwaykind	Calibre	19	05-31-2009 02:35 PM