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 : stackoverflow.com/questions/4959722/c-sharp-datatable-to-csv — Amin Mozhgani
– Amin Mozhgani, Commented Nov 15, 2016 at 6: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
– Akshey Bhat, Commented Nov 15, 2016 at 6:23
@AksheyBhat - yes WriteToCsvFile is inside a static class. — vicky
– vicky, Commented Nov 15, 2016 at 6:26

Community · Accepted Answer · 2017-05-23 12:30:23Z

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, 2017 at 12:30

CommunityBot

11 silver badge

answered Nov 15, 2016 at 6:26

Winson

7876 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

vicky Over a year ago

How can I write the contents to csv with this method?

Winson Over a year ago

sorry, what's contents? you just need to pass the DataTable object in it should be ok, and the seperator set to a comma

vicky Over a year ago

how can I give the filename path.

Winson Over a year ago

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

vicky Over a year ago

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.

Ram Nivas · Accepted Answer · 2016-11-15 09:15:55Z

0

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

answered Nov 15, 2016 at 9:15

Ram Nivas

1423 silver badges13 bronze badges

Comments

Sreepathy Sp · Accepted Answer · 2016-11-15 09:36:36Z

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, 2016 at 9:36

answered Nov 15, 2016 at 7:22

Sreepathy Sp

4082 gold badges7 silver badges21 bronze badges

5 Comments

vicky Over a year ago

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.

Sreepathy Sp Over a year ago

@AlexanderPetrov could you please tell me why this is not a valid csv

Alexander Petrov Over a year ago

Your code writes the string "overview" and empty strings - this breaks the csv format.

Sreepathy Sp Over a year ago

@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 Over a year ago

@vicky if your are looking for alignment better go for any library

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related