Bug 14169 - Item Search csv export
Summary: Item Search csv export
Status: RESOLVED DUPLICATE of bug 27164
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching (show other bugs)
Version: 3.22
Hardware: All All
: P5 - low minor (vote)
Assignee: Galen Charlton
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-05-07 17:35 UTC by Joy Nelson
Modified: 2020-12-09 08:49 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
What is seen in Excel when opening the csv (126.79 KB, image/png)
2016-06-21 14:48 UTC, Joy Nelson
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Joy Nelson 2015-05-07 17:35:46 UTC
Files exported from Item Search do not open correctly in Microsoft Excel.  However .csv files exported from Reports Module do open correctly in Microsoft Excel.    (Note: all files open correctly in LibreOffice)

Steps to reproduce:
1. Go to Item Search and select criteria
2. Output to .csv file
3. Select open with Excel
4. Columns are not lined up correctly and extra "" appear
5. Go to Reports
6. Run a report
7. Download to csv
8. Open csv in Microsoft Excel - it opens cleanly and data in right columns.

Can Item Search csv export work as Reports csv export?
Comment 1 Magnus Enger 2015-05-07 17:45:28 UTC
Maybe related to bug 10117?
Comment 2 Owen Leonard 2016-06-21 12:41:38 UTC
I don't have Excel, but I can't see any problem with the contents of the file based on eyeballing it in a text editor.

Does this problem exist in more recent versions? If so, please update this bug's version information.
Comment 3 Joy Nelson 2016-06-21 14:48:20 UTC
Created attachment 52653 [details]
What is seen in Excel when opening the csv
Comment 4 Joy Nelson 2016-06-21 14:54:01 UTC
And here is the corresponding data from the .csv file.  Note the stray quotes.

"The new atlas of the stars : Mellinger, Axel.", "2005", "Firefly Books,", "", "31951001323440", "QB65 .M4513 2005", "WESSELS LIBRARY", "WESSELS LIBRARY", "ATLAS", "", "", "0"
"Atlas of the world / ", "2005", "Published in North America by Oxford University Press,", "", "31951001324620", "G1021 .G4125 2005 MAP", "WESSELS LIBRARY", "WESSELS LIBRARY", "ATLAS", "", "", "0"
"Atlas of the world / ", "2005", "Published in North America by Oxford University Press,", "", "31951001324638", "G1021 .G4125 2005", "WESSELS LIBRARY", "WESSELS LIBRARY", "ATLAS", "", "", "0"
"HarperCollins atlas of the Bible / ", "1997", "HarperCollins,", "", "31951001398350", "G2230 .H35 1997", "WESSELS LIBRARY", "WESSELS LIBRARY", "ATLAS", "", "", "0"



(In reply to Joy Nelson from comment #3)
> Created attachment 52653 [details]
> What is seen in Excel when opening the csv
Comment 5 Owen Leonard 2016-06-21 15:20:24 UTC
I don't see any stray quotes. All quotes look like they are matched correctly. However, I do see that publisher data is getting split incorrectly. I would think that Excel would ignore a common within double quotes, but it looks like it doesn't.

I thought I would try exporting a report containing title, author, and publisher to see if the resulting CSV file had the same structure and found that the CSV file saved doesn't use commas at all, it uses semicolons. I wonder if that's why those exports work for you.

Does this problem happen for you in versions > 3.18.x? If so, please update this bug's version information.
Comment 6 Joy Nelson 2016-06-21 15:33:01 UTC
Yes, sorry, I meant stray commas.  It is the publisher that is getting out of whack and Excel is not smart enough to ignore the comma inside double quotes.  grrr

And Excel is rarely my spreadsheet of choice.  But for some libraries it is what they are required to use.  ;-)  


(In reply to Owen Leonard from comment #5)
> I don't see any stray quotes. All quotes look like they are matched
> correctly. However, I do see that publisher data is getting split
> incorrectly. I would think that Excel would ignore a common within double
> quotes, but it looks like it doesn't.
> 
> I thought I would try exporting a report containing title, author, and
> publisher to see if the resulting CSV file had the same structure and found
> that the CSV file saved doesn't use commas at all, it uses semicolons. I
> wonder if that's why those exports work for you.
> 
> Does this problem happen for you in versions > 3.18.x? If so, please update
> this bug's version information.
Comment 7 Josef Moravec 2016-11-03 09:16:33 UTC
For Excel its's probably better to set the CSV separator to semicolons - set system preference  delimiter and choose the semicolons also in CSV profiles, if you are using them...

I am closing as invalid, as it's rather on Excel side than Koha side...

Feel free to reopen it, if you have an idea what we could do on Koha side.
Comment 8 Peggy Thrasher 2019-03-29 14:51:35 UTC
The issue is the spaces before the double quotes.


Original from Koha:

"Starry night : Macomber, Debbie.", "2013", "Ballantine Books,", "", "34505003358304"

produces these fields (each field in a subsequent column):
Starry night : Macomber, Debbie.	
 "2013"	
 "Ballantine Books	
, ", 34505003358304"

Removing spaces before double quotes results in:

"Starry night : Macomber, Debbie.","2013","Ballantine Books,","","34505003358304"

which produces these fields:

Starry night : Macomber, Debbie.	
2013	
Ballantine Books,		
34505003358304

It seems that there isn't a universal standard for CSV files, but https://en.wikipedia.org/wiki/Comma-separated_values says:

According to RFC 4180, spaces outside quotes in a field are not allowed; however, the RFC also says that "Spaces are considered part of a field and should not be ignored." and "Implementors should 'be conservative in what you do, be liberal in what you accept from others' (RFC 793, section 2.10) when processing CSV files."
1997, "Ford" ,E350

Reports that are downloaded from Koha in CSV format do not have the extra space.  Can this code use the same functions as reports?
Comment 9 Didier Gautheron 2020-12-09 08:49:14 UTC

*** This bug has been marked as a duplicate of bug 27164 ***