how to import data from CSV to sql server

Question

CSV file having multiple columns how to split between respective multiple tables

Eg: Sample1.csv file having :Id,Department,Name,Address,Qualification -Read from these csv file column to write 2 different tables and column names varies like Id in Sample1 file but in table1 it is EmpId

Table 1 : EmpId,EmpDepartment

Table2 :Name,Address

Edwin Klesman · Answer

Assuming the tables for the CSV columns already exist in your database and you only need to insert the records from the CSV file into the respective tables, you can modify the previous code snippet as follows: using System.IO; using System.Data; using System.Linq; using System.Web.UI.WebControls; using System.Data.SqlClient; protected void Page_Load(object sender, EventArgs e) { // read the CSV file into a DataTable DataTable dt = new DataTable(); string filePath = Server.MapPath("~/Sample1.csv"); StreamReader sr = new StreamReader(filePath); string[] headers = sr.ReadLine().Split(','); foreach (string header in headers) { dt.Columns.Add(header); } while (!sr.EndOfStream) { string[] rows = sr.ReadLine().Split(','); DataRow dr = dt.NewRow(); for (int i = 0; i "; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); foreach (DataRow row in dt.Rows) { string query = "INSERT INTO Table1 (EmpId, EmpDepartment) VALUES (@EmpId, @EmpDepartment)"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@EmpId", row["Id"]); command.Parameters.AddWithValue("@EmpDepartment", row["Department"]); command.ExecuteNonQuery(); } connection.Close(); } // insert the records into table 2 using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); foreach (DataRow row in dt.Rows) { string query = "INSERT INTO Table2 (Name, Address) VALUES (@Name, @Address)"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@Name", row["Name"]); command.Parameters.AddWithValue("@Address", row["Address"]); command.ExecuteNonQuery(); } connection.Close(); } } This code snippet reads the "Sample1.csv" file into a DataTable and then inserts the records into the respective tables using parameterized SQL queries. You will need to replace with your actual database connection string. Note that this code assumes that the column names in the CSV file match the column names in the database tables.