unable to write datatable values to csv file in windows app c#

Question

I am trying to insert datatable contents into csv file and the below code doesn't work.

CODE:

System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Property", typeof(string));
System.Data.DataRow newRow = dt.NewRow();
newRow["Name"] = str1;
newRow["Property"] = str2;
dt.Rows.Add(newRow);
dt.WriteToCsvFile("../../Data.csv");    

public static void WriteToCsvFile(this DataTable dataTable, string filePath)
    {
        StringBuilder fileContent = new StringBuilder();

        foreach (var col in dataTable.Columns)
        {
            fileContent.Append(col.ToString() + ",");
        }
        fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1);

        foreach (DataRow dr in dataTable.Rows)
        {
            foreach (var column in dr.ItemArray)
            {
                fileContent.Append("\"" + column.ToString() + "\",");
            }
            fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1);
        }
        System.IO.File.WriteAllText(filePath, fileContent.ToString());
    }

Data.csv file is placed inside a project folder and the values are not getting added.

Really appreciate any suggestions on this.

Edit1:

dt.CreateCSVFile("../../Data.csv");

public static void CreateCSVFile(this DataTable dt, string strFilePath)
    {
        StreamWriter sw = new StreamWriter(strFilePath, true);

        int iColCount = dt.Columns.Count;
        for (int i = 0; i < iColCount; i++)
        {
            sw.Write(dt.Columns[i]);
            if (i < iColCount - 1)
            {
                sw.Write(",");
            }
        }
        sw.Write(sw.NewLine);

        foreach (DataRow dr in dt.Rows)
        {
            for (int i = 0; i < iColCount; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                {
                    sw.Write(dr[i].ToString());
                }
                if (i < iColCount - 1)
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);
        }
        sw.Close();
    }

take a look : http://stackoverflow.com/questions/4959722/c-sharp-datatable-to-csv — Amin Mozhgani, Nov 15 '16 at 06:18
its working on my system. Did you place `WriteToCsvFile` in a static class? `WriteToCsvFile` is an extension method. It should be in a static class — Akshey Bhat, Nov 15 '16 at 06:23
@AksheyBhat - yes `WriteToCsvFile` is inside a static class. — vicky, Nov 15 '16 at 06:26

score 1 · Answer 1 · edited May 23 '17 at 12:30

1

I don't know why you can use

dt.WriteToCsvFile("../../Data.csv");

there should be no this method in DataTable, if you want to convert DataTable to CVS, you can try the below:

public static string DataTableToCSV(this DataTable datatable, char seperator)
{
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < datatable.Columns.Count; i++)
    {
        sb.Append(datatable.Columns[i]);
        if (i < datatable.Columns.Count - 1)
            sb.Append(seperator);
    }
    sb.AppendLine();
    foreach (DataRow dr in datatable.Rows)
    {
        for (int i = 0; i < datatable.Columns.Count; i++)
        {
            sb.Append(dr[i].ToString());

            if (i < datatable.Columns.Count - 1)
                sb.Append(seperator);
        }
        sb.AppendLine();
    }
    return sb.ToString();
}

you can find more in here

after call that method, you can save the string to file as below:

var contents = dt.DataTableToCSV(',');
System.IO.File.WriteAllText (@"D:\exportfile.cvs", contents);

edited May 23 '17 at 12:30

Community

1
1

answered Nov 15 '16 at 06:26

Winson

769
6
17

How can I write the contents to csv with this method? – vicky Nov 15 '16 at 06:39
sorry, what's contents? you just need to pass the DataTable object in it should be ok, and the seperator set to a comma – Winson Nov 15 '16 at 06:42
how can I give the filename path. – vicky Nov 15 '16 at 06:49
after run this method, you can get a string of cvs, and you just need to save these into a file is ok, please see my other answer – Winson Nov 15 '16 at 06:52
I tried this and it doesn't write the contents to CSV. I updated my post and that code works fine, but it doesn't align properly. – vicky Nov 15 '16 at 08:07

Sreepathy Sp · Answer 2 · 2016-11-15T09:36:36.360

0

Hope this will help you sorry for not rewriting your code

 using (MemoryStream output = new MemoryStream())
        {
            using (StreamWriter writer = new StreamWriter(Server.MapPath("~/CSV/export.csv")))
            {


                writer.WriteLine(" OVERVIEW");
                writer.WriteLine("");
                writer.WriteLine("date : " + DateTimeOffset.Parse(DateTimeOffset.Now.ToString(), CultureInfo.CurrentCulture).ToString("yyyy-MM-dd"));

                writer.WriteLine("Name");
                writer.WriteLine("Property");

                writer.WriteLine("");
                writer.WriteLine("");





                writer.WriteLine("Name,Property");

                foreach (var item in List)
                {
                    writer.WriteLine(string.Format("{0},{1}",
                                               item.Name,
                                               item.Property


                                               ));
                }
                writer.Flush();
                output.Position = 0;
                // var document=Server.MapPath("~/Content/ExportedFiles/Exported_Users.csv");
            }


        }

edited Nov 15 '16 at 09:36

answered Nov 15 '16 at 07:22

Sreepathy Sp

408
2
6
21

I updated my post. I am using streamwriter to write the contents and it works just fine. But the problem is with the alignments, since the values are not placed property to particular column, they get breaked and are misplaced widely. FYI, these actions are performed in windows app. – vicky Nov 15 '16 at 08:09
@AlexanderPetrov could you please tell me why this is not a valid csv – Sreepathy Sp Nov 15 '16 at 08:54
Your code writes the string "overview" and empty strings - this breaks the [csv](https://en.wikipedia.org/wiki/Comma-separated_values) format. – Alexander Petrov Nov 15 '16 at 08:58
@AlexanderPetrov will it be a valid one if we removed writer.WriteLine(" OVERVIEW"); writer.WriteLine(""); writer.WriteLine("date : " + DateTimeOffset.Parse(DateTimeOffset.Now.ToString(), CultureInfo.CurrentCulture).ToString("yyyy-MM-dd")); writer.WriteLine("Name"); writer.WriteLine("Property"); writer.WriteLine(""); writer.WriteLine(""); this section – Sreepathy Sp Nov 15 '16 at 09:02
@vicky if your are looking for alignment better go for any library – Sreepathy Sp Nov 15 '16 at 09:03

score 0 · Answer 3 · answered Nov 15 '16 at 09:15

Here i converted the DataTable to CSV list. you can save it to file.

Code :

public static IEnumerable<string> ToCsvList(this DataTable table)
    {
        List<string> returnList = new List<string>();

        string colmn = string.Join(",", table.Columns.OfType<DataColumn>().Select(c => c.ColumnName));
        returnList.Add(colmn);

        foreach (DataRow row in table.Rows)
        {
            returnList.Add(string.Join(",", row.ItemArray));
        }

        return returnList;
    }

unable to write datatable values to csv file in windows app c#

3 Answers3