Library export CSV

[sfboek]

Hello

I did do a search by could not find an answer.

When I export the calibre library database to a CSV file, my columns are separated by the comma character.

When I open this file in an Excel sheet all my data are in one column.
The I use Select Data > Text to Columns.

Unfortunately, most of my books are written by more than 1 writer.
These writers are also separated by the comma character.
When I have 3 writers, these writers are moved to 3 columns, because they are also separated by a comma. So the title of this book will be in column 4
When I have 2 writers, the title of the book will be in column 3.
So in column 3 I have sometimes the third writer and sometimes the title of the book.
And ofcourse the titel of the books contains sometimes a comma.

Is it possible to change the comma for the column into someting else

Thanks Simon

[theducks]

You need to use Comma Quoted import.
Authors (most strings) are Quoted so those are a single cell
OTOH numbers do not have commas, so the use the comma as a column separator

EG

"62","2013-08-18T07:04:21-07:00","The Exotic Enchanter","","L. Sprague deCamp & Christopher Stasheff","Harold Shea","6.0"

Is an empty field

[BetterRed]

Use the --list option in the calibredb command to create the 'csv', it has a 'separator' parameter.

BR

[DNSB]

Originally Posted by sfboek

Unfortunately, most of my books are written by more than 1 writer.
These writers are also separated by the comma character.
When I have 3 writers, these writers are moved to 3 columns, because they are also separated by a comma. So the title of this book will be in column 4
When I have 2 writers, the title of the book will be in column 3.
So in column 3 I have sometimes the third writer and sometimes the title of the book.
And ofcourse the titel of the books contains sometimes a comma.

Is it possible to change the comma for the column into someting else

Thanks Simon

Your results seem rather odd. When I generate a CSV catalog, the author(s) end up in one field with & separating the authors. The author_sort field does have internal commas but since the field is quoted, the author names again end up in the same field. This is using LibreOffice Calc since I don't have Microsoft Excel installed on this computer.

I've attached the contents of the CSV file wrapped in code tags:

title,author_sort,authors,series,series_index
"First Stop","Todd, Michael & Anderle, Michael","Michael Todd & Michael Anderle","Dungeon Rails","1.0"
"Codename: Whistleblower","Cameron, T. R. & Carr, Martha & Anderle, Michael","T. R. Cameron & Martha Carr & Michael Anderle","Spellbound Security","6.0"
"Original-Microsoft-Source-Code","Gates, Bill","Bill Gates","","1.0"
"1638: The Sovereign States","Flint, Eric & Huff, Gorg & Goodlett, Paula","Eric Flint & Gorg Huff & Paula Goodlett","Ring of Fire","36.0"
"1636- the Flight of the Nightingale","Carrico, David","David Carrico","Ring of Fire","34.0"
"1637: The Transylvanian Decision","Flint, Eric & Waters, Robert E.","Eric Flint & Robert E. Waters","Ring of Fire","38.0"

I've attached a screenshot of a few books showing single and multiple authors.