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.
i have an error message in the file path help me, please.
System.Data.OleDb.OleDbException: ”C:\Users\Maram\source\repos\WebApplication23 – Copy\WebApplication23\excelfolder\b490a1f2-b132-4168-a591-2bbedc65264b.csv’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.’
Efficient way to do it. Great work
Thank You , stay tuned much more coming 🙂