Import CSV file to DataTable using C# and VB.Net in ASP.Net

Last Reply 3 months ago By pandeyism

Posted 3 months ago

how to convert csv file to datatable best way in chsap web application

You are viewing reply posted by: pandeyism 3 months ago.
Posted 3 months ago

Hi Saiansh,

Refer below sample.

HTML

<asp:Button ID="Button1" Text="Import" runat="server" OnClick="Import" />
<br />
<asp:GridView ID="gvData" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.IO;

VB.Net

Imports System.Data
Imports System.IO

Code

C#

protected void Import(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    string path = @"C:\Users\anand\Desktop\Book1.csv";
    gvData.DataSource = CSVToDataTable(path);
    gvData.DataBind();
}

private DataTable CSVToDataTable(string path)
{
    DataTable dt = new DataTable();
    string csvData;
    using (StreamReader sr = new StreamReader(path))
    {
        csvData = sr.ReadToEnd().ToString();
        string[] row = csvData.Split('\n');
        for (int i = 0; i < row.Count() - 1; i++)
        {
            string[] rowData = row[i].Split(',');
            {
                if (i == 0)
                {
                    for (int j = 0; j < rowData.Count(); j++)
                    {
                        dt.Columns.Add(rowData[j].Trim());
                    }
                }
                else
                {
                    DataRow dr = dt.NewRow();
                    for (int k = 0; k < rowData.Count(); k++)
                    {
                        dr[k] = rowData[k].ToString();
                    }
                    dt.Rows.Add(dr);
                }
            }
        }

        return dt;
    }
}

VB.Net

Protected Sub Import(ByVal sender As Object, ByVal e As EventArgs)
    Dim path As String = "C:\Users\anand\Desktop\Book1.csv"
    gvData.DataSource = CSVToDataTable(path)
    gvData.DataBind()
End Sub

Private Function CSVToDataTable(path As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Dim csvData As String
    Using sr As StreamReader = New StreamReader(path)
        csvData = sr.ReadToEnd().ToString()
        Dim row As String() = csvData.Split(vbLf)
        For i As Integer = 0 To row.Count() - 1 - 1
            Dim rowData As String() = row(i).Split(","c)
            If True Then
                If i = 0 Then
                    For j As Integer = 0 To rowData.Count() - 1
                        dt.Columns.Add(rowData(j).Trim())
                    Next
                Else
                    Dim dr As DataRow = dt.NewRow()
                    For k As Integer = 0 To rowData.Count() - 1
                        dr(k) = rowData(k).ToString()
                    Next

                    dt.Rows.Add(dr)
                End If
            End If
        Next

        Return dt
    End Using
End Function

Screenshot