Wednesday, January 31, 2007

Exporting DataTable to CSV File Format

    protected void Button1_Click(object sender, EventArgs e)
    {

        string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

        SqlConnection conn = new SqlConnection(strConn);

        SqlDataAdapter da = new SqlDataAdapter("select * from employees", conn);

        DataSet ds = new DataSet();

        da.Fill(ds, "Emp");

        GridView1.DataSource = ds.Tables["Emp"].DefaultView;

        GridView1.DataBind();

        DataTable dt = ds.Tables["Emp"];

        CreateCSVFile(dt, "c:\\csvData.csv");

    }

 

 

    public void CreateCSVFile(DataTable dt, string strFilePath)

    {

        #region Export Grid to CSV

 

        // Create the CSV file to which grid data will be exported.

        StreamWriter sw = new StreamWriter(strFilePath, false);

        // First we will write the headers.

        //DataTable dt = m_dsProducts.Tables[0];

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

        // Now write all the rows.

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

 

        #endregion

    }

11 comments:

Anonymous said...

Thank you so much!

Ferrier's Web Design said...

This is a pretty decent way convert a DataTable to CSV but as someone pointed out on my blog Blog.RunXc DataTable to CSV you need to really watch for string fields that have "commas , " in them

Stephen said...

Thanks for the code, it has an issue where number begin with 0 - such as telephone numbers. Excel will rmeove the leading 0.

Here is a version which handles this case.

public static void ExportToCSV(DataTable dt, string strFilePath,string fileName)
{
var sw = new StreamWriter(strFilePath + fileName, false);

// Write the headers.
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);

// Write rows.
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
if(dr[i].ToString().StartsWith("0"))
{
sw.Write(@"="""+dr[i]+@"""");
}
else
{
sw.Write(dr[i].ToString());
}
}

if (i < iColCount - 1) sw.Write(",");
}
sw.Write(sw.NewLine);
}

sw.Close();
}

Vijay said...

Thanks Stephen. The leading zero problem solution suggested by you is really very helpful.

Thank you.

spyrax1897 said...

This is a simple way but powerfull. That's great !!

Molto grazia Stephen...

Rocky said...

This is really helpful,i see the leading zero problem solution suggested by you is really nice, but my requirement is to get the exactly same value e.g. 00001122 in excel file..as the soultion you suggested is producing the value like ="00001122" in the excel cell...Our client don't want this format..It should be exactly the similar to original value ..any help is really appreciated.

Khadim said...

Thank you, you saved atleast one hour of my life!

Optimizer said...

May god compensate u for saving our time
he can

Jesse and BegoƱa said...

This doesn't handle any special cases for text columns. Leading 0, line breaks, quotes, commas, etc.

LiJun Tan said...

how come my result is when u click the button then the gridview show.

monu choudhary said...

good one

Most Recent Post

Community Updates

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape