Monday, September 16, 2019

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

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
1
<asp:Button Text="Export" OnClick="ExportCSV" runat="server" />
VB.Net
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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
column1column2
a,b,c,vass,ff,fff ,kkkl,f,
a,b,c,vass,ff,fff ,kkkl,f,
a,b,c,vass,ff,fff ,kkkl,f,
a,b,c,vass,ff,fff ,kkkl,f,
a,b,c,vass,ff,fff ,kkkl,f,
a,b,c,vass,ff,fff ,kkkl,f,
a,b,c,vass,ff,fff ,kkkl,f,
a,b,c,vass,ff,fff ,kkkl,f,
a,b,c,vass,ff,fff ,kkkl,f,
a,b,c,vass,ff,fff ,kkkl,f,
a,b,c,vass,ff,fff ,kkkl,f,
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

No String Argument Constructor/Factory Method to Deserialize From String Value

  In this short article, we will cover in-depth the   JsonMappingException: no String-argument constructor/factory method to deserialize fro...