How to fix the problem of extracting an excel table that has an arabic language

Question

1.00/5 (2 votes)

See more:

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 i get the arabic letters like that(/*0%&#@*!*$&@$^#@%*@#4)

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

C#

<pre>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);

    }
}

What I have tried:

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();
 ...

Posted 19-Dec-18 21:51pm

el_tot93

Updated 19-Dec-18 23:57pm

v2

Add a Solution

Comments

Richard MacCutchan 20-Dec-18 4:24am

"there is a problem i get when i open the sheet"
And you want us to guess what that problem is?

el_tot93 20-Dec-18 4:45am

sorry i update that

Richard MacCutchan 20-Dec-18 4:58am

Your code appears to be doing some sort of conversion on the data. Use your debugger to check whether it is doing it correctly.

el_tot93 20-Dec-18 5:33am

i try that there is no problem

Richard MacCutchan 20-Dec-18 6:35am

Then it must be in the displaying of the extracted data. The application needs to set the correct culture to display those characters correctly.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

el_tot93 · Accepted Answer · 2018-12-19T23:58:00

C#

<pre>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));
                    }
                }
            }
        }
    } 
}