Wednesday 31 October 2012

How to export Encoded Data into CSV Files?


Exporting data into various file format - i.e. .csv, .tsv is frequently required in our day to day action. Many of us have been written code segment for this also. My purpose to write this article is to demonstrate How export data using various encoding into csv format?

Here I’ve developed a sample application which export a Unicode data stored in database into csv file. Note that if you export data with Unicode encoding delimited by coma (‘,’), it won’t gives you effective result. Let’s start how to overcome this problem.

Inside the code


























In above image you can see that Datagridview is loaded with data. This data contains data in unicode format – Last two row contains data in chainese and russian language. Also we’ve an option for selecting various encoding and delimeters while exporting data. Code for file export is shown as below.


        private void btnExport_Click(object sender, EventArgs e)
        {
            DataTable dt = dg.DataSource as DataTable;

            if (string.IsNullOrEmpty(txtPath.Text.Trim()))
            {
                ShowMessage("Invalid file path. Please enter valid file path");
                return;
            }

            StringBuilder builder = new StringBuilder();

            if (chkHeader.Checked)
            {
                var count = 0;
                foreach (DataColumn column in dt.Columns)
                {
                    count++;
                    string content = column.ColumnName + "";
                    content = content.Replace("\"", "\"\"");
                    builder.Append(string.Format("{0}{1}{0}", Convert.ToChar(34), content));

                    if (count < dt.Columns.Count)
                        builder.Append(GetDelimeter(cmbDelimeter.Text.Trim()));
                }
                builder.Append(Environment.NewLine);
            }

            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < row.ItemArray.Length; i++)
                {
                    if (!Convert.IsDBNull(row[i]))
                    {
                        string content = row[i].ToString() + "";
                        content = content.Replace("\"", "\"\"");
                        builder.Append(string.Format("{0}{1}{0}", Convert.ToChar(34), content));

                        if (i < row.ItemArray.Length - 1)
                            builder.Append(GetDelimeter(cmbDelimeter.Text.Trim()));
                    }
                }
                builder.Append(Environment.NewLine);
            }

            using (var streamWriter = new StreamWriter(txtPath.Text.Trim(), chkAppend.Checked, GetEncoding(cmbEncoding.Text.Trim())))
            {
                streamWriter.Write(builder.ToString());
                streamWriter.Flush();
                streamWriter.Close();
            }
            ShowMessage("Data successfully exported");
        }

In above code you can see that we are iterating over each row in data table and appending each cell data into string builder. Delimiter that we choose while exporting is appended after each cell data through GetDelimeter() function. Code of GetDelimeter() function is shown below:

        private string GetDelimeter(string delimeter)
        {
            string retval = "";
            switch (delimeter.ToLower())
            {
                case "coma":
                    retval = ",";
                    break;
                case "tab":
                    retval = "\t";
                    break;
                case "space":
                    retval = "\b";
                    break;
            }
            return retval;
        }

After appending all data into string builder, initialize StreamWriter object with argument – Destination file path, data should appended or not and content Encoding. Encoding is also chosen by us while exporting. Code for selecting encoding is given below:

        private Encoding GetEncoding(string encoding)
        {
            Encoding encod = null;
            switch (encoding.ToLower())
            {
                case "unicode":
                    encod = Encoding.Unicode;
                    break;
                case "utf8":
                    encod = Encoding.UTF8;
                    break;
                case "ansi":
                default:
                    encod = Encoding.Default;
                    break;
            }

            return encod;
        }

This way we can export data in csv file with encoding we choose.