Hi Dobrica, can you explain the problem a bit? Is this a new problem? We are using the import and had no problems with UTF-8 encoded files so far, but using older versions of Koha.
Created attachment 25616 [details] [review] Bug 11811 - tools/import_borrowers.pl doesn't support utf-8 encoded CSV Decode utf-8 characters in CSV file Test scenario: 1. go to Tools > Import patrons 2. download sample CSV file 3. create few patrons with utf-8 chars 4. import patrons and verify then utf-8 characters are correct
It does work as-is but for wrong reasons. We are really not decoding utf-8 characters and our only hope is that mysql will insert bytes as it gets them (which it does) so it works without this modification. This might change in future if mysql decided to check validity of data we are inserting into it.
I did the following: Home -> Tools -> Import Patrons I downloaded the sample CSV I set the borrower number and card numbers to a high number like 777. I made the first name and surname equal to BogusÁàö I set an email address. I set a branch code and category code to something valid. Set date enrolled to 2014-01-01 Set userid and password to test1 and test1. I saved, and my MS Excel complained about the format. I saved it anyways. I then went back to the staff client, browsed for it, and scrolled down to click Import. It complained the name was Bogus��� And searching for the patron had nothing on the end of Bogus. I have the problem under master and patched.
My guess is, that Excel changed the encoding. I does that for me every time and it's why I switched to LibreOffice for preparing the files for patron import. The CSV export options work nicer there. Check that your CSV is really UTF-8.
Created attachment 25628 [details] My test record. Here is the test record I used/modified.
Created attachment 25629 [details] [review] [SIGNED OFF] Bug 11811 - tools/import_borrowers.pl doesn't support utf-8 encoded CSV Decode utf-8 characters in CSV file Test scenario: 1. go to Tools > Import patrons 2. download sample CSV file 3. create few patrons with utf-8 chars 4. import patrons and verify then utf-8 characters are correct Importing patrons was tested once on master and modified for a test on the patch. The diacritics showed up as expected. Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
Created attachment 26059 [details] [review] Bug 11811 - tools/import_borrowers.pl utf-8 support for CSV Decode utf-8 characters in CSV file and perform canonical decomposition followed by canonical composition. Use case for this is XLS file converted into CSV which includes utf-8 characters which display correctly in interface but are not searchable in Koha's patrons search since browser does same decomposition as this code does. Test scenario: 1. go to Tools > Import patrons 2. create patrons using attached example files which include both variants of my surname. You will have to fill in branchcode and categorycode according to configuration of Koha instance which is used in web form since those values are not included in CSV 3. import patrons and verify then utf-8 characters are correct and that you get just one results when searching for my surname (cardnumber ITI-00000151832). You can just copy/paste my surname from example file (any of them) since browser does similar decomposition as this code. 4. apply this patch and repeat import of patrons, selecting option to overwrite existing records 5. verify that after searching for my surname you get two borrowers (cardnumbers ITI-00000151831 and ITI-00000151832)
Created attachment 26060 [details] CSV file referenced in test scenario I removed sign off from this patch because just decoding utf-8 didn't solve our initial problem completely. Since it might be challenge to create both variants of unicode encoding, I'm attaching this file which includes my surname "Pavlinušić" in both formats.
Created attachment 26102 [details] [review] Bug 11811 - tools/import_borrowers.pl utf-8 support for CSV Decode utf-8 characters in CSV file and perform canonical decomposition followed by canonical composition. Use case for this is XLS file converted into CSV which includes utf-8 characters which display correctly in interface but are not searchable in Koha's patrons search since browser does same decomposition as this code does. Test scenario: 1. go to Tools > Import patrons 2. create patrons using attached example files which include both variants of my surname. You will have to fill in branchcode and categorycode according to configuration of Koha instance which is used in web form since those values are not included in CSV 3. import patrons and verify then utf-8 characters are correct and that you get just one results when searching for my surname (cardnumber ITI-00000151832). You can just copy/paste my surname from example file (any of them) since browser does similar decomposition as this code. 4. apply this patch and repeat import of patrons, selecting option to overwrite existing records 5. verify that after searching for my surname you get two borrowers (cardnumbers ITI-00000151831 and ITI-00000151832) Signed-off-by: A. Sassmannshausen <alex.sassmannshausen@ptfs-europe.com>
Created attachment 26375 [details] [review] [PASSED QA] Bug 11811 - tools/import_borrowers.pl utf-8 support for CSV Decode utf-8 characters in CSV file and perform canonical decomposition followed by canonical composition. Use case for this is XLS file converted into CSV which includes utf-8 characters which display correctly in interface but are not searchable in Koha's patrons search since browser does same decomposition as this code does. Test scenario: 1. go to Tools > Import patrons 2. create patrons using attached example files which include both variants of my surname. You will have to fill in branchcode and categorycode according to configuration of Koha instance which is used in web form since those values are not included in CSV 3. import patrons and verify then utf-8 characters are correct and that you get just one results when searching for my surname (cardnumber ITI-00000151832). You can just copy/paste my surname from example file (any of them) since browser does similar decomposition as this code. 4. apply this patch and repeat import of patrons, selecting option to overwrite existing records 5. verify that after searching for my surname you get two borrowers (cardnumbers ITI-00000151831 and ITI-00000151832) Signed-off-by: A. Sassmannshausen <alex.sassmannshausen@ptfs-europe.com> Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de> Passes all tests and QA script. Tested with my own file, some German umlauts and non-Latin scripts. All works as expected.
(In reply to Katrin Fischer from comment #11) > Use case for this is XLS file converted into CSV which includes utf-8 > characters which display correctly in interface but are not searchable > in Koha's patrons search since browser does same decomposition as this > code does. Not so. There is no guarantee that strings entered via the web browser, or patron searches entered via the web browser, will end up in NFC. Consequently, one can end up with a mixture of NFD and NFC data in the database, meaning that patron searches will not necessarily bring up the same results. One of the root issues is the default collation that is typically used. In my development database, it happens to be utf8_general_ci -- which in general is problematic [1], and in particular doesn't treat the NFD and NFC forms of Dobrica's surname as equivalent. Spot-checking suggests that utf8_general_ci ends up as the default for databases installed from Debian packages, with the exception of a couple columns that kohastructure.sql sets to utf8_unicode_ci. Rather than piecemeal sticking in NFC(), I think a better solution would be to change the collation to utf8_unicode_ci across the board. [1] http://stackoverflow.com/questions/1036454/what-are-the-diffrences-between-utf8-general-ci-and-utf8-unicode-ci
Created attachment 27099 [details] script to change collation of Koha's tables to utf8_unicode_ci This is a script to change the collation of Koha's tables to utf8_unicode_ci. Dobrica, please try it and advise if this fixes this particular bug, and whether it works in general for you. Of course, changing the collation like this could have unintended consequences.
(In reply to Galen Charlton from comment #13) > Created attachment 27099 [details] > script to change collation of Koha's tables to utf8_unicode_ci > > This is a script to change the collation of Koha's tables to > utf8_unicode_ci. Dobrica, please try it and advise if this fixes this > particular bug, and whether it works in general for you. > > Of course, changing the collation like this could have unintended > consequences. All looks good to me. It seems that it is the way to do if the collation was utf8_general_ci but not for others values. Maybe should we add an entry in the updatedb and if the collation is utf8_general_ci, we convert to utf8_unicode_ci, otherwise ask for help on the IRC channel? Or just a note in the release notes.
(In reply to Jonathan Druart from comment #14) > Or just a note in the release notes. -- you are assuming people will read. I'm not sure that is a safe assumption.
I have just tested with the CSV file against master and it works \o/ Please reopen if I am wrong.