ASP.NET MVC

Import .CSV File Data To SQL Using C#

In this post , We will learn how to Import .csv File data to SQL using C# with the help of OLEDB . The OLEDB provide different connection string for .csv file and Excel(.xlsx) , In This case i am going to use .csv connection string. If you want to read excel you can check out my last Blog over here (Import Excel Data To SQL Using C#)

First thing first , Create a table in SQL-Server database (you can copy paste following code to your SQL-Server to relate flawless with the following article).

CREATE TABLE [dbo].[tbl_registration](
[Sr_no] [int] IDENTITY(1,1) NOT NULL,
[Email] [nvarchar](100) NULL,
[Password] [nvarchar](max) NULL,
[Name] [varchar](max) NULL,
[Address] [nvarchar](max) NULL,
[City] [nvarchar](max) NULL
)

Now, Add connection string to your Web.config file.

<connectionStrings>
   <add name="con" connectionString="Server=YourServerName;Initial Catalog=YourDatabaseName;Persist Security Info=False;User ID=YourUserName;Password=YourUserPassword;MultipleActiveResultSets=True;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient" />
 </connectionStrings>

Note :- Change Server,Initial Catalog,User ID,Password values as per your server properties

Now, Add the following code to your Index.cshtml file

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <div>
        <h2>Excel Import</h2>
        <form method="post" enctype="multipart/form-data" class="form-group">
            <div>
                <input name="file" type="file" class="form-control-file" required />
                <button type="submit" class="btn btn-primary">Import</button>
            </div>
        </form>
    </div>
</body>

Now we will Add the file in our Local system

[HttpPost]
       public ActionResult Index(HttpPostedFileBase file)
       {
           string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
           string filepath = "/excelfolder/" + filename;
           file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
           InsertExceldata(filepath, filename);
           return View();
       }

Next And Most Important thing , As I said  earlier the .csv file required Different Connection String ,So this is The connection part.

private void ExcelConn(string filepath)
{            
    string constr = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""", filepath);
    econ = new OleDbConnection(constr);
}

Now lets read the file and store the data in DB

private void InsertExceldata(string fileepath, string filename)
{
            string fullpath = Server.MapPath("/excelfolder/") + filename;
            ExcelConn(fullpath);
            string query = string.Format("Select * from [{0}]", Path.GetFileName(filename).Split('.')[0]);
            OleDbCommand ecom = new OleDbCommand(query, econ);
            econ.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter oda = new OleDbDataAdapter(query, econ);
            econ.Close();
            oda.Fill(ds);
            DataTable dt = ds.Tables[0];
            SqlBulkCopy objbulk = new SqlBulkCopy(con);
            objbulk.DestinationTableName = "tbl_ReadExcel";
            objbulk.ColumnMappings.Add("Email", "Email");
            objbulk.ColumnMappings.Add("Password", "Password");
            objbulk.ColumnMappings.Add("Name", "Name");
            objbulk.ColumnMappings.Add("Address", "Address");
            objbulk.ColumnMappings.Add("City", "City");
            con.Open();
            objbulk.WriteToServer(dt);
            con.Close();
}

At the end your controller would look like this

public class HomeController : Controller
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        OleDbConnection econ;
        public ActionResult Index()
        {
            return View();
        }
        [HttpPost]
        public ActionResult Index(HttpPostedFileBase file)
        {
            string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
            string filepath = "/excelfolder/" + filename;
            file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
            InsertExceldata(filepath, filename);
            return View();
        }
        private void ExcelConn(string filepath)
        {            
            string constr = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""", filepath);
            econ = new OleDbConnection(constr);
        }
        private void InsertExceldata(string fileepath, string filename)
        {
            string fullpath = Server.MapPath("/excelfolder/") + filename;
            ExcelConn(fullpath);
            string query = string.Format("Select * from [{0}]", Path.GetFileName(filename).Split('.')[0]);
            OleDbCommand ecom = new OleDbCommand(query, econ);
            econ.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter oda = new OleDbDataAdapter(query, econ);
            econ.Close();
            oda.Fill(ds);
            DataTable dt = ds.Tables[0];
            SqlBulkCopy objbulk = new SqlBulkCopy(con);
            objbulk.DestinationTableName = "tbl_ReadExcel";
            objbulk.ColumnMappings.Add("Email", "Email");
            objbulk.ColumnMappings.Add("Password", "Password");
            objbulk.ColumnMappings.Add("Name", "Name");
            objbulk.ColumnMappings.Add("Address", "Address");
            objbulk.ColumnMappings.Add("City", "City");
            con.Open();
            objbulk.WriteToServer(dt);
            con.Close();
        }
    }

And you are ready to go run the project

If you face any problem leave it down in the comment section am ready to help any time.

2 Comments

  1. Jems Dholiya

    Efficient way to do it. Great work

    Reply
    1. Thank You , stay tuned much more coming 🙂

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Footer Logo

Subscribe

Select Categories