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

Last Reply 8 months ago By dharmendr

Posted 8 months 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 8 months ago Modified on 8 months 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