Dynamically generate CSV file from Database by retaining delimited character (comma) using C# and VB.Net in ASP.Net

Hi seriverma,

You need to do workaround for getting the single value with comma as in single column in csv by creating a method which will return the same value by adding the quotes with escape sequence.

Refer below sample code and implement according to your need.

HTML

<asp:Button Text="Export" OnClick="ExportCSV" runat="server" />

VB.Net

Protected Sub ExportCSV(sender As Object, e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("SELECT 'a,b,c,v' column1,'ass,ff,fff ,kkkl,f,' column2 FROM Customers")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    sda.Fill(dt)

                    'Build the CSV file data as a Comma separated string.
                    Dim csv As String = String.Empty

                    For Each column As DataColumn In dt.Columns
                        'Add the Header row for CSV file.
                        csv += column.ColumnName + ","c
                    Next

                    'Add new line.
                    csv += vbCr & vbLf

                    For Each row As DataRow In dt.Rows
                        For Each column As DataColumn In dt.Columns
                            'Add the Data rows.
                            csv += AddEscapeSequenceInCsvField(row(column.ColumnName).ToString()) + ","
                        Next

                        'Add new line.
                        csv += vbCr & vbLf
                    Next

                    'Download the CSV file.
                    Response.Clear()
                    Response.Buffer = True
                    Response.AddHeader("content-disposition", "attachment;filename=SqlExport.csv")
                    Response.Charset = ""
                    Response.ContentType = "application/text"
                    Response.Output.Write(csv)
                    Response.Flush()
                    Response.End()
                End Using
            End Using
        End Using
    End Using
End Sub

Private Function AddEscapeSequenceInCsvField(ValueToEscape As String) As String
    If ValueToEscape.Contains(",") Then
        Return (Convert.ToString("""") & ValueToEscape) + """"
    Else
        Return ValueToEscape
    End If
End Function

C#

protected void ExportCSV(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT 'a,b,c,v' column1,'ass,ff,fff ,kkkl,f,' column2 FROM Customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);

                    //Build the CSV file data as a Comma separated string.
                    string csv = string.Empty;

                    foreach (DataColumn column in dt.Columns)
                    {
                        //Add the Header row for CSV file.
                        csv += column.ColumnName + ',';
                    }

                    //Add new line.
                    csv += "\r\n";

                    foreach (DataRow row in dt.Rows)
                    {
                        foreach (DataColumn column in dt.Columns)
                        {
                            //Add the Data rows.
                            csv += AddEscapeSequenceInCsvField(row[column.ColumnName].ToString()) + ",";
                        }

                        //Add new line.
                        csv += "\r\n";
                    }

                    //Download the CSV file.
                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition", "attachment;filename=SqlExport.csv");
                    Response.Charset = "";
                    Response.ContentType = "application/text";
                    Response.Output.Write(csv);
                    Response.Flush();
                    Response.End();
                }
            }
        }
    }
}

private string AddEscapeSequenceInCsvField(string ValueToEscape)
{
    if (ValueToEscape.Contains(","))
    {
        return "\"" + ValueToEscape + "\"";
    }
    else
    {
        return ValueToEscape;
    }
}

Output