import from CSV - different charsets in the database - my thoughts

26 views

Skip to first unread message

Daniel Calcoen

unread,

Feb 26, 2021, 8:28:13 AM2/26/21

to PartKeepr Users

Hello,

Note: all what is mentioned in this message was executed in my own installation (Install PartKeepr on Ubuntu 20.10) so may be your setup is different an does not applies to you.

I was trying to use import a CSV file, like I explain here Import Part information from a file in the user manual, to update some fields in my table and didn't succeed.

I'm not familiar with PartKeepr source code, my first exploration didn't give me an hint on what is wrong.

To speed I decided to us a SQL script and do it by hand and left the deep navigation on the source code for later.

script to update the category

In the first version of my script I was getting errors blaming different charsets...

The problem was that the PartKeepr tables were created using

CHARSET=utf8 COLLATE=utf8_unicode_ci;

you can see that in /partkeepr/docker/development/initdb/dump.sql

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `Part` (

....

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*!40101 SET character_set_client = @saved_cs_client */;

while the table I imported from a CSV file using https://dbeaver.io/ (Eclipse) was created with the defaults

COLLATE=utf8_general_ci;

I changed my script to cast the charset, adding the "COLLATE utf8_unicode_ci =" when assigning the imported columns and then worked like a charm...

Could be this the problem that makes non functional some pieces of the import in my PartKeepr sever ?

and the different problems reported when trying to import?

I'll try to investigate further (when I'll found some time...) going deep in the PartKeepr source code.