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

Last Reply one year ago By pandeyism

Posted one year ago

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

Posted one year 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