Import CSV file to database without using oledb in ASP.Net

Last Reply one year ago By dharmendr

Posted one year ago

Hello All,

How to import csv to database without oledb in my web application using asp.net c#

I need a help to insert CSV file data into database.

Here is my problem.

I have a CSV file which have some data including header. I have a database table which have same or different header name table.

Now i wanna insert those csv file data into database with maping each column.

like abc.csv have  a,b,D column and tableA have A,B,D column

now a=A ,b=B and d=D

in my input csv file not contain all time fixed column. The number of CSV column can be add or reduce.

I don't know is it possible or not thats I check if database column not match with input column then that column value null.

like if a!=A then insert A=null.

Sorry for my bad english.

thanks for help.

Posted one year ago Modified on one year ago

Hi gpiyush152,

Check this example. Now please take its reference and correct your code.

Rerering the below article i have created the example.

Import (Upload) CSV file data to SQL Server database in ASP.Net using C# and VB.Net

HTML

<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportCSV" />

Namespaces

C#

using System.IO;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void ImportCSV(object sender, EventArgs e)
{
    string csvPath = Server.MapPath("~/Files/Sample.csv");
    DataTable dt = new DataTable();
    string csvData = File.ReadAllText(csvPath);
    int rowCount = 0;
    foreach (string row in csvData.Split('\n'))
    {
        if (!string.IsNullOrEmpty(row))
        {
            if (rowCount == 0)
            {
                foreach (string column in row.Split(','))
                {
                    dt.Columns.Add(column);
                }
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (string cell in row.Split(','))
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell;
                    i++;
                }
            }
            rowCount++;
        }
    }

    if (dt.Rows.Count > 0)
    {
        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.CustomerTest";
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (!string.IsNullOrEmpty(ColumnExist(dt.Columns[i].ToString().Trim())))
                    {
                        sqlBulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName.Trim());
                    }
                }
                con.Open();
                sqlBulkCopy.WriteToServer(dt);
                con.Close();
            }
        }
    }
}

private string ColumnExist(string columnName)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = "SELECT COL_LENGTH('Customers','" + columnName.Trim() + "') Length";
    SqlCommand cmd = new SqlCommand(query);
    SqlConnection con = new SqlConnection(conString);
    con.Open();
    cmd.Connection = con;
    string length = Convert.ToString(cmd.ExecuteScalar());
    con.Close();
    return length;
}

VB.Net

Protected Sub ImportCSV(ByVal sender As Object, ByVal e As EventArgs)
    Dim csvPath As String = Server.MapPath("~/Files/Sample.csv")
    Dim dt As DataTable = New DataTable()
    Dim csvData As String = File.ReadAllText(csvPath)
    Dim rowCount As Integer = 0
    For Each row As String In csvData.Split(vbLf)
        If Not String.IsNullOrEmpty(row) Then
            If rowCount = 0 Then
                For Each column As String In row.Split(","c)
                    dt.Columns.Add(column)
                Next
            Else
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As String In row.Split(","c)
                    dt.Rows(dt.Rows.Count - 1)(i) = cell
                    i += 1
                Next
            End If

            rowCount += 1
        End If
    Next

    If dt.Rows.Count > 0 Then
        Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(consString)
            Using sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con)
                sqlBulkCopy.DestinationTableName = "dbo.CustomerTest"
                For i As Integer = 0 To dt.Columns.Count - 1
                    If Not String.IsNullOrEmpty(ColumnExist(dt.Columns(i).ToString().Trim())) Then
                        sqlBulkCopy.ColumnMappings.Add(dt.Columns(i).ColumnName, dt.Columns(i).ColumnName.Trim())
                    End If
                Next

                con.Open()
                sqlBulkCopy.WriteToServer(dt)
                con.Close()
            End Using
        End Using
    End If
End Sub

Private Function ColumnExist(ByVal columnName As String) As String
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT COL_LENGTH('CustomerTest','" & columnName.Trim() & "') Length"
    Dim cmd As SqlCommand = New SqlCommand(query)
    Dim con As SqlConnection = New SqlConnection(conString)
    con.Open()
    cmd.Connection = con
    Dim length As String = Convert.ToString(cmd.ExecuteScalar())
    con.Close()
    Return length
End Function

Screenshot