Bug 27164

Summary: Fix item search CSV export
Product: Koha Reporter: Fridolin Somers <fridolin.somers>
Component: CatalogingAssignee: Fridolin Somers <fridolin.somers>
Status: CLOSED FIXED QA Contact: Victor Grousset/tuxayo <victor>
Severity: normal    
Priority: P5 - low CC: 1joynelson, andrewfh, caroline.cyr-la-rose, m.de.rooy, maura.stephens, victor
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
21.05.00,20.11.02,20.05.08
Attachments: Bug 27164: Double quotes everywhere in item search CSV export
Bug 27164: Fix item search CSV export
Bug 27164: Fix item search CSV export
item search export
Bug 27164: Fix item search CSV export
Bug 27164: Fix item search CSV export
Bug 27164: Fix item search CSV export
Bug 27164: Fix item search CSV export

Description Fridolin Somers 2020-12-07 15:59:29 UTC
In item search CSV export, data cells are exported with double quotes.
There should also be double quotes in header cells.
Also some spaces can be removed.
Comment 1 Fridolin Somers 2020-12-07 16:07:41 UTC
Created attachment 114233 [details] [review]
Bug 27164: Double quotes everywhere in item search CSV export

In item search CSV export, data cells are exported with double quotes.
There should also be double quotes in header cells.
Also some spaces can be removed.

This may help to open the CSV with any table/calc software.

Test plan :
1.0) Without patch
1.1) Perform an item search
1.2) Export as a CSV file and save the file locally
1.3) Open the file with a table/calc software
2.0) Apply patch
2.1) Perform same item search
2.2) Export as a CSV file and save the file locally
2.3) Open the file with a table/calc software
3.0) Compare files with diff/meld
=> you see only double quotes arround header cells
   and removal of space after coma
3.1) In table/calc software they should look the same
Comment 2 Fridolin Somers 2020-12-08 11:12:00 UTC
Oh there are more problems
Comment 3 Fridolin Somers 2020-12-08 15:36:08 UTC
Created attachment 114261 [details] [review]
Bug 27164: Fix item search CSV export

In item search CSV export, data cells are exported with double quotes.
There should also be double quotes in header cells.
Also some spaces can be removed.
Delimiter must be from system preference 'delimiter'.
TT filter must be $raw not html, only double quotes must be espaped by
duplication of it.

This may help to open the CSV with any table/calc software.

Test plan :
1.0) Create a record with title : Laurel & "Hardy"
1.2) Create an item on this record
2.0) Without patch
2.1) Perform an item search on title 'Laurel'
2.2) Export as a CSV file and save the file locally
2.3) Open the file with a table/calc software
3.0) Apply patch
3.1) Perform same item search
3.2) Export as a CSV file and save the file locally
3.3) Open the file with a table/calc software
4.0) Compare files with diff/meld
=> you see only double quotes arround header cells
   and removal of space after coma
4.1) Check that delimiter is from system preference 'delimiter'
4.2) In table/calc software they should look the same
Comment 4 Fridolin Somers 2020-12-08 15:36:36 UTC
Created attachment 114262 [details] [review]
Bug 27164: Fix item search CSV export

In item search CSV export, data cells are exported with double quotes.
There should also be double quotes in header cells.
Also some spaces can be removed.
Delimiter must be from system preference 'delimiter'.
TT filter must be $raw not html, only double quotes must be espaped by
duplication of it.

This may help to open the CSV with any table/calc software.

Test plan :
1.0) Create a record with title : Laurel & "Hardy"
1.2) Create an item on this record
2.0) Without patch
2.1) Perform an item search on title 'Laurel'
2.2) Export as a CSV file and save the file locally
2.3) Open the file with a table/calc software
3.0) Apply patch
3.1) Perform same item search
3.2) Export as a CSV file and save the file locally
3.3) Open the file with a table/calc software
4.0) Compare files with diff/meld
=> you see only double quotes arround header cells
   and removal of space after coma
4.1) Check that delimiter is from system preference 'delimiter'
4.2) In table/calc software they should look the same
Comment 5 Andrew Fuerste-Henry 2020-12-08 20:00:16 UTC
Created attachment 114268 [details]
item search export

I'm still getting double quotes around everything in my export. The delimiter changed, though!
Comment 6 Didier Gautheron 2020-12-09 08:49:14 UTC
*** Bug 14169 has been marked as a duplicate of this bug. ***
Comment 7 Fridolin Somers 2020-12-09 14:20:00 UTC
You have :
"Laurel & ""Hardy""";"";"";"";"q35y";"";"Centerville";...

What is wrong ?
Comment 8 Fridolin Somers 2020-12-09 14:21:48 UTC
delimiter was hardcoded "," now its from system preference named "delimiter".
Looks like you have ";" in this preference.
Comment 9 Fridolin Somers 2020-12-09 14:22:08 UTC
Thanks a lot for testing BTW :D
Comment 10 Andrew Fuerste-Henry 2020-12-09 14:59:19 UTC
> 4.0) Compare files with diff/meld
> => you see only double quotes arround header cells
>    and removal of space after coma

I thought from your test plan that I should not have double quotes around each data field after the patch. I was expecting:
"Title";"Publication date";"Publisher"; ...
Laurel & "Hardy";;;...

Did I just misunderstand the plan?
Comment 11 Fridolin Somers 2020-12-10 16:33:04 UTC
Created attachment 114315 [details] [review]
Bug 27164: Fix item search CSV export

In item search CSV export, data cells are exported with double quotes.
There should also be double quotes in header cells.
Also some spaces can be removed.
Delimiter must be from system preference 'delimiter'.
TT filter must be $raw not html, only double quotes must be espaped by
duplication of it.

This may help to open the CSV with any table/calc software.

Test plan :
1.0) Create a record with title : Laurel & "Hardy"
1.2) Create an item on this record
2.0) Without patch
2.1) Perform an item search on title 'Laurel'
2.2) Export as a CSV file and save the file locally
2.3) Open the file with a table/calc software
3.0) Apply patch
3.1) Perform same item search
3.2) Export as a CSV file and save the file locally
3.3) Open the file with a table/calc software
4.0) Compare files with diff/meld
=> you see double quotes arround header cells
   and removal of space after coma
4.1) Check that delimiter is from system preference 'delimiter'
4.2) In table/calc software they should look the same
Comment 12 Fridolin Somers 2020-12-10 16:34:37 UTC
My bad ;)

Test plan fixed :

4.0) Compare files with diff/meld
=> you see double quotes arround header cells
Comment 13 Andrew Fuerste-Henry 2020-12-23 18:59:21 UTC
Created attachment 114687 [details] [review]
Bug 27164: Fix item search CSV export

In item search CSV export, data cells are exported with double quotes.
There should also be double quotes in header cells.
Also some spaces can be removed.
Delimiter must be from system preference 'delimiter'.
TT filter must be $raw not html, only double quotes must be espaped by
duplication of it.

This may help to open the CSV with any table/calc software.

Test plan :
1.0) Create a record with title : Laurel & "Hardy"
1.2) Create an item on this record
2.0) Without patch
2.1) Perform an item search on title 'Laurel'
2.2) Export as a CSV file and save the file locally
2.3) Open the file with a table/calc software
3.0) Apply patch
3.1) Perform same item search
3.2) Export as a CSV file and save the file locally
3.3) Open the file with a table/calc software
4.0) Compare files with diff/meld
=> you see double quotes arround header cells
   and removal of space after coma
4.1) Check that delimiter is from system preference 'delimiter'
4.2) In table/calc software they should look the same

Signed-off-by: Andrew Fuerste-Henry <andrew@bywatersolutions.com>
Comment 14 Andrew Fuerste-Henry 2020-12-23 18:59:42 UTC
Thanks for clarifying!
Comment 15 Victor Grousset/tuxayo 2021-01-03 17:19:16 UTC
Hi :)

Much needed improvements and I get better results by default on LibreOffice.

The only issue found is about removing the space between title and author.

Here is an example:
"Laurel & ""Hardy""Hammarberg, Marcus."
without quotes to Hardy, I would get
"Laurel & HardyHammarberg, Marcus."

Other examples

'More fruitful than the soil' :Mackillop, Andrew.
"A Hegel dictionary /Inwood, Michael,"
"A tribute to Jack JohnsonDavis, Miles,"

The table HTML view and the detail page of the record insert a space between title and author. So it seems pretty sure we need to have that here.
Which also means the "by" in UNIMARC case don't need a space before and after, otherwise the space will be duplicated.
Comment 16 Fridolin Somers 2021-01-04 08:18:29 UTC
Created attachment 114776 [details] [review]
Bug 27164: Fix item search CSV export

In item search CSV export, data cells are exported with double quotes.
There should also be double quotes in header cells.
Also some spaces can be removed.
Delimiter must be from system preference 'delimiter'.
TT filter must be $raw not html, only double quotes must be espaped by
duplication of it.

This may help to open the CSV with any table/calc software.

Test plan :
1.0) Create a record with title : Laurel & "Hardy"
1.2) Create an item on this record
2.0) Without patch
2.1) Perform an item search on title 'Laurel'
2.2) Export as a CSV file and save the file locally
2.3) Open the file with a table/calc software
3.0) Apply patch
3.1) Perform same item search
3.2) Export as a CSV file and save the file locally
3.3) Open the file with a table/calc software
4.0) Compare files with diff/meld
=> you see double quotes arround header cells
   and removal of space after coma
4.1) Check that delimiter is from system preference 'delimiter'
4.2) In table/calc software they should look the same
Comment 17 Fridolin Somers 2021-01-04 08:19:30 UTC
(In reply to Victor Grousset/tuxayo from comment #15)
> Hi :)
> 
> Much needed improvements and I get better results by default on LibreOffice.
> 
> The only issue found is about removing the space between title and author.
> 
> Here is an example:
> "Laurel & ""Hardy""Hammarberg, Marcus."
> without quotes to Hardy, I would get
> "Laurel & HardyHammarberg, Marcus."
> 
> Other examples
> 
> 'More fruitful than the soil' :Mackillop, Andrew.
> "A Hegel dictionary /Inwood, Michael,"
> "A tribute to Jack JohnsonDavis, Miles,"
> 
> The table HTML view and the detail page of the record insert a space between
> title and author. So it seems pretty sure we need to have that here.
> Which also means the "by" in UNIMARC case don't need a space before and
> after, otherwise the space will be duplicated.

Good catch, I see it in my patch indeed.
Trival change so I changed the main patch and went to "Signed Off"
Comment 18 Victor Grousset/tuxayo 2021-01-07 17:32:40 UTC
Created attachment 114934 [details] [review]
Bug 27164: Fix item search CSV export

In item search CSV export, data cells are exported with double quotes.
There should also be double quotes in header cells.
Also some spaces can be removed.
Delimiter must be from system preference 'delimiter'.
TT filter must be $raw not html, only double quotes must be espaped by
duplication of it.

This may help to open the CSV with any table/calc software.

Test plan :
1.0) Create a record with title : Laurel & "Hardy"
1.2) Create an item on this record
2.0) Without patch
2.1) Perform an item search on title 'Laurel'
2.2) Export as a CSV file and save the file locally
2.3) Open the file with a table/calc software
3.0) Apply patch
3.1) Perform same item search
3.2) Export as a CSV file and save the file locally
3.3) Open the file with a table/calc software
4.0) Compare files with diff/meld
=> you see double quotes arround header cells
   and removal of space after coma
4.1) Check that delimiter is from system preference 'delimiter'
4.2) In table/calc software they should look the same

Signed-off-by: Andrew Fuerste-Henry <andrew@bywatersolutions.com>
Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net>
Comment 19 Jonathan Druart 2021-01-08 14:23:17 UTC
Pushed to master for 21.05, thanks to everybody involved!
Comment 20 Fridolin Somers 2021-01-11 12:28:10 UTC
Pushed to 20.11.x for 20.11.02
Comment 21 Andrew Fuerste-Henry 2021-01-15 22:17:38 UTC
Pushed to 20.05.x for 20.05.08
Comment 22 Victor Grousset/tuxayo 2021-01-22 01:25:48 UTC
Not backported to oldoldstable (19.11.x). Feel free to ask if it's needed.