importing csv to mysql database - how to read file with Polish characters?

Question

I have a csv file which it has a Polish characters too. The content of this is here:

ID_WORKER;FNAME;LNAME;WORKERS_GROUP;POSITION;
1;Paweł;ĄąĆćĘꣳŃńÓ󌜏źŻż;IT;IT Specialist;
6;Dawid;ĄąĆćĘꣳŃńÓ󌜏źŻż;Technologists;Technologists;
8;Maciej;ĄąĆćĘꣳŃńÓ󌜏źŻż;Storekeepers;Storekeeper;

As you see it has these characters like "Ąą Ćć Ęę Łł Ńń Óó Śś Źź Żż". Then I import csv file with below code:

private DataTable ImportFile()
{
    DataTable imported_data = new DataTable();

    OpenFileDialog ofd = new OpenFileDialog();
    ofd.Title = "Open csv file";
    ofd.DefaultExt = "*.csv";
    ofd.Filter = "Documents (*.csv)|*.csv";
    ofd.ShowDialog();

    FileInfo fi = new FileInfo(ofd.FileName);
    string FileName1 = ofd.FileName;
    string excel = fi.FullName;

    using(StreamReader sr = new StreamReader(excel, Encoding.Default))
    {
        string header = sr.ReadLine();
        if (string.IsNullOrEmpty(header))
        {
            MessageBox.Show("Not found or loaded not correct file.");
            return null;
        }

        string[] header_columns = header.Split(',');
        foreach(string header_column in header_columns)
        {
            imported_data.Columns.Add(header);
        }

        while (!sr.EndOfStream)
        {
            string line = sr.ReadLine();

            if (string.IsNullOrEmpty(linia)) continue;

            string[] fields = line.Split(',');
            DataRow imported_row = imported_data.NewRow();

            for (int i = 0; i < fields.Count(); i++)
            {
                imported_row[i] = fields[i];
            }

            imported_data.Rows.Add(imported_row);
        }
    }
    return imported_data;
}

And when I insert that imported data with all content from csv file to database with that code in below:

private void save_modules(DataTable imported_data)
{
    string connection = "datasource=localhost;port=3306;username=root;password=";
    using (MySqlConnection conn = new MySqlConnection(connection))
    {
        conn.Open();
        foreach (DataRow importRow in imported_data.Rows)
        {
            string query = @"INSERT IGNORE INTO try1.workers (ID_WORKER, FNAME, LNAME,
            WORKERS_GROUP, POSITION) VALUES (@ID_WORKER, @FNAME, @LNAME,
            @WORKERS_GROUP, @POSITION);";

            MySqlCommand cmd = new MySqlCommand(query, conn);

            cmd.Parameters.AddWithValue("@ID_WORKER", importRow["ID_WORKER"]);
            cmd.Parameters.AddWithValue("@FNAME", importRow["FNAME"]);
            cmd.Parameters.AddWithValue("@LNAME", importRow["LNAME"]);
            cmd.Parameters.AddWithValue("@WORKERS_GROUP", importRow["WORKERS_GROUP"]);
            cmd.Parameters.AddWithValue("@POSITION", importRow["POSITION"]);

            cmd.ExecuteNonQuery();
        }
        conn.Close();
    }  
    MessageBox.Show("Imported to database");
}

I see in mysql database in "LNAME" column i see NOT all Polish characters: "Aa Cc Ee Ll Nn Óó Ss Zz Zz". And that's not good enough.

What i've tried?

I've tried to import csv file with other encoding formats:

1) Encoding.Default - then it shows like as i shew in this example: "Aa Cc Ee Ll Nn Óó Ss Zz Zz".

2) Encoding.ASCII - then it shows all '?' characters

3) Encoding.UTF8 - but it shows all '?' characters too.

4) Encoding.GetEncoding(1252) - didn't help too much.

As for as mysql database is concerned i have set utf8_polish_ci method of comparing subtitles.

Now, how can i import csv with the Polish characaters. Maybe is required to change the line of code in first piece of importing csv file code:

using(StreamReader sr = new StreamReader(excel, Encoding.Default)) 

Any ideas?

EDIT

I use phpmyadmin 4.8.4 and mysql database version is 10.1.37-MariaDB

Answer 1

You have (at least) four things to check:

• Is Encoding.Default loading the characters correctly? Use the Visual Studio debugger and check the values of the line and fields variables.
  • Possible fixes: Save the file as UTF-8 and use Encoding.UTF8.
• Can your database store these characters? Use MySQL Workbench to execute INSERT INTO try1.workers(LNAME) VALUES('ĄąĆćĘꣳŃńÓ󌜏źŻż'); SELECT * FROM try1.workers; (add other columns as necessary).
  • Possible fixes: Declare the column type as utf8mb4_unicode520_ci.
• Is cmd.ExecuteNonQuery sending Unicode to the database? Use MySQL Workbench to select values from table after inserting them and check that they're correct.
  • Possible fixes: Add CharSet=utf8mb4 to your connection string, or switch to MySqlConnector which always sends Unicode data over the network.
• Is phpmyadmin corrupting your data when retrieving it? If everything else looks correct, then it may be the problem.
  • Possible fixes: Ask another SO question (or edit this one) to focus purely on that problem, not on C# + MySQL.