Importing polish characters into mysql with phpmyadmin

swordsinfo

Hi guys quick question im trying to use the import function in phpmyadmin to import csv file into mysql. However I cant get the following characters in ł ż ś ń

I have tried changing the encoding of the excel file I have tried changing the encoding of the import but no dice. If i put them in manually its ok which means the encoding of the db is ok as is the encoding with the field but i get ?? instead of the characters. Any tips on how to avoid these problems

heres some info on platform being used:


Server version: 5.5.21
Protocol version: 10

MySQL charset: UTF-8 Unicode (utf8)
Web server

Microsoft-IIS/7.5
MySQL client version: mysqlnd 5.0.8-dev - 20102224 - $Revision: 321634 $
PHP extension: mysqli

as always thanks again for your help

PrzemoF

Is the original csv file encoded in UTF8 as well? You can use "file" command under linux to find it out - I'm not sure about windows. You may also find "iconv" command useful - same as before about windows.

swordsinfo

PrzemoF wrote: »

Is the original csv file encoded in UTF8 as well? You can use "file" command under linux to find it out - I'm not sure about windows. You may also find "iconv" command useful - same as before about windows.

im using windows i have set the option in excel to save csv in utf 8 - i have read up on it and have found a few things regarding latin 2 but I think the utf 8 should work. The database itself is fine because I can do it manually with the inset window of phpmyadmin - drivging me bananas at this stage as i will have to insert 20k or so record in the coming months!!

PrzemoF

Can you upload a small sample somewhere? I don't trust soft like excel what it comes to character encoding (MS is not famous for adhering to standards)

[a linux live cd is another option if you're running out of ideas with windows]

Evil Phil

Is it xls/xlsx or csv? If it's csv you could open it in something like notepad++ and save it using the desired encoding. As PrzemoF said, Excel isn't always trust worthy as it sometimes does things it's own way.

If its xls/xlsx could you save it as a csv?

swordsinfo

Evil Phil wrote: »

Is it xls/xlsx or csv? If it's csv you could open it in something like notepad++ and save it using the desired encoding. As PrzemoF said, Excel isn't always trust worthy as it sometimes does things it's own way.

If its xls/xlsx could you save it as a csv?

Hay guys thanks for your help - with bits and piece you gave me I was able to do it - basically using notepad++ to sort out the encoding issue. I was able to replace the polish characters with the correct values. But then I had a different issue on the import I was getting a invalid field count error because on of the text fields had a comma in it I tried to get the Columns enclosed with: " to work but it still picked up the comma in the field (the field is a title of a medical abstract so had to be carefully not to change anything)

Finally work around was to replace every comma with a ; it was then easier to identify these in the abstract titles and replace them with commas!

It seemed like a very long winded way of going about it - would anyone else recommend an alternative - some of our other imports would have a couple of k records so cant imagine doing that for each record ??

Thanks again for your help

PrzemoF

[linux point of view advice] Get familiar with cygwin & vim/sed/awk.

swordsinfo

PrzemoF wrote: »

[linux point of view advice] Get familiar with cygwin & vim/sed/awk.

Im coming from a windows environment unfortunately - I think open office was a lot better at handling an import on random characters rather than comma so might try and go back down that route but I wonder will it have the same encoding problem as excel. I have read up more on this and seemingly there is a prob with excel actually saving the file as utf-8