Storing special characters in database

[sirlink99]

I made a test database to store items in a database, but I keep getting an error when trying to import from a txt file.

Here is my txt file

1,Lánc
2,Fa
3,Csavar
4,Lakat

How could I fix this problem?

Also is there a reason I need to keep the ID column included? Could I not just use a command something along the lines of while database.readNext() != null.

I know this isn't correct code, but it gets the idea across.

Thanks for any help

[smantscheff]

How do you import data? Which error do you get?

[sirlink99]

I have that .txt file, and then I click import, select the file, and then some settings and then it gives me an invalid character error.

[smantscheff]

Where do you "click import"? Which interface do you use? What are the settings, especially regarding character sets?
If you want to control all relevant parameters use the mysql command line utility and the LOAD FILE command.

[sirlink99]

I sign into phpMyAdmin.
Then I select my database "test"
Then in the top bar there is an import option, I click that
it displays this

Importing into the table "Test" 
File to Import:
File may be compressed (gzip) or uncompressed.
A compressed file's name must end in .[format].[compression]. Example: .sql.zip
Browse your computer:(Max: 55MiB) 
Character set of the file:
Partial Import:
 Allow the interruption of an import in case the script detects it is close to the PHP timeout limit. (This might be good way to import large files, however it can break transactions.)
Number of rows to skip, starting from the first row:  
Format:
 
Format-Specific Options:
SQL compatibility mode:
Do not use AUTO_INCREMENT for zero values

[ok button]

so I select my .txt file
select UTF-8 (the text file is also saved in that encoding)
I am not skipping any rows, so that stays the same.
Then for Format it has the options
I select SQL
SQL compatability mode: none
do not use auto_increment for zero values

Then when I click go it gives me this error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,Lánc 2,Fa 3,Csavar 4,Lakat' at line 1

thanks for your help

[smantscheff]

Obviously phpMyAdmin does not recognize the comma as a field delimiter. Switch it to semicolons.

[sirlink99]

I switched to semicolons, and it gives me this error

Error

SQL query: 

1;


MySQL said:  
 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

do I need to include the numbers, or does it automatically go to the next one after a semicolon?

[smantscheff]

I just installed phpMyAdmin to see what your problem might be, and it turns out that you are misunderstanding the format options.
Format SQL is for sql scripts which can be parsed and executed by the database.
What you want is format CSV which shows also the field and line delimiter options for your CSV format.

[sirlink99]

Thanks that worked