How to fix the problem of extracting an Excel table that has an Arabic language

Question

i export a large table with big data to excel/.csv sheet with arabic letters.

there is a problem i get when i open the sheet you can see it in the Image.

is there any problem with my code or i should use another code?

public void DumpTableToFile(SqlConnection connection, string tableName, string destinationFile)
    {
        using (var command = new SqlCommand("select * from table " , connection))
        using (var reader = command.ExecuteReader())
        using (var outFile = File.CreateText(destinationFile))
        {
            string[] columnNames = GetColumnNames(reader).ToArray();
            int numFields = columnNames.Length;
            outFile.WriteLine(string.Join(",", columnNames));
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    string[] columnValues =
                        Enumerable.Range(0, numFields)
                                  .Select(i => reader.GetValue(i).ToString())
                                  .Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
                                  .ToArray();
                    outFile.WriteLine(string.Join(",", columnValues));
                }
            }
        }
    }

    private IEnumerable<string> GetColumnNames(IDataReader reader)
    {
        foreach (DataRow row in reader.GetSchemaTable().Rows)
        {
            yield return (string)row["ColumnName"];
        }
    }

    private void Button5_Click(object sender, EventArgs e)
    {
        SqlConnection _connection = new SqlConnection();
        SqlDataAdapter _dataAdapter = new SqlDataAdapter();
        SqlCommand _command = new SqlCommand();
        DataTable _dataTable = new DataTable();

        _connection = new SqlConnection();
        _dataAdapter = new SqlDataAdapter();
        _command = new SqlCommand();
        _dataTable = new DataTable();

        //dbk is my database name that you can change it to your database name
        _connection.ConnectionString = "connaction";
        _connection.Open();

        SaveFileDialog saveFileDialogCSV = new SaveFileDialog();
        saveFileDialogCSV.InitialDirectory = Application.ExecutablePath.ToString();

        saveFileDialogCSV.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*";
        saveFileDialogCSV.FilterIndex = 1;
        saveFileDialogCSV.RestoreDirectory = true;

        string path_csv = "";
        if (saveFileDialogCSV.ShowDialog() == DialogResult.OK)
        {
            // Runs the export operation if the given filenam is valid.
            path_csv = saveFileDialogCSV.FileName.ToString();
        }


        DumpTableToFile(_connection, "tbl_trmc", path_csv);

    }
}

You could use a streamwriter instead: https://stackoverflow.com/a/373372/169714 there you can specify the encoding. — JP Hellemons, Dec 20 '18 at 09:11

shA.t · Answer 1 · 2018-12-20T09:20:52.873

0

I think you need to use encoding, something like this - for each data-:

var utf8 = new UTF8Encoding();
...

string[] columnValues =
    Enumerable.Range(0, numFields)
        .Select(i => {
            if (reader[i].GetType() == typeof(SqlString))
            {
              SqlString sqlString = reader.GetSqlString(i);
              Byte[] encodedBytes = sqlString.GetNonUnicodeBytes();
              return "\"" + utf8.GetString(encodedBytes) + "\"";
            }
            else
            {
              return "\"" + reader[i].ToString() + "\"";
            }
         }).ToArray();
 ...

or

string[] columnValues =
    Enumerable.Range(0, numFields)
        .Select(i => reader[i].GetType() == typeof(SqlString) 
            ? utf8.GetString(reader.GetSqlString(i).GetNonUnicodeBytes()) 
            : reader[i].ToString())
        .Select(field => string.Concat("\"", field, "\""))
        .ToArray();

For more details: msdn: utf8 varchar to byte[]

edited Dec 20 '18 at 09:20

answered Dec 20 '18 at 06:02

shA.t

16,580
5
54
111

i try but i didn't get where i can bout it correct in my code – Dec 20 '18 at 06:15
I updated my answer to show where you should put it - HTH ;). – shA.t Dec 20 '18 at 06:19
thx for your help bout it is not working it has become more complex – Dec 20 '18 at 06:29
what do you mean by -not working- is there any error or ... ? – shA.t Dec 20 '18 at 07:01

JP Hellemons · Accepted Answer · 2018-12-20T10:36:19.713

Use a streamwriter and specify the encoding:

public void DumpTableToFile(SqlConnection connection, string tableName, string destinationFile)
{
    using (var command = new SqlCommand("select * from table " , connection))
    {
        using (var reader = command.ExecuteReader())
        {
            using (StreamWriter sw = new StreamWriter(File.Open(destinationFile, FileMode.Create), Encoding.Utf8))
            {    
                string[] columnNames = GetColumnNames(reader).ToArray();
                int numFields = columnNames.Length;
                sw.WriteLine(string.Join(",", columnNames)); // writing headers  

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        string[] columnValues = Enumerable.Range(0, numFields)
                              .Select(i => reader.GetValue(i).ToString())
                              .Select(field => string.Concat("\"", field.Replace("\"", "\"\""), "\""))
                              .ToArray();
                        sw.WriteLine(string.Join(",", columnValues));
                    }
                }
            }
        }
    } 
}

should be `destinationFile`. ps. you are not using `tableName` variable? — JP Hellemons, Dec 20 '18 at 10:36
if i want to separate the table to many sheet what should i do i have 2,000,000 record do you have any idea for that ?? — , Dec 20 '18 at 12:01
I prefer to use EPPlus when using with Excel https://github.com/JanKallman/EPPlus/wiki/Getting-Started — JP Hellemons, Dec 20 '18 at 12:50

How to fix the problem of extracting an Excel table that has an Arabic language

2 Answers2