How to import csv file into mysql database table

Divya Sindhu

when i click choose file button i get file dialog box upper with only csv file .and i select some csv file the file path display in my textbox1 and when i click submit button i need to insert the file data into MySql database table .

i had a problem in bulkcopy .how i insert the csv file data into MySql database table. my table name is "aster_user"

here my code:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.IO;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Configuration;

using System.Data.OleDb;

using System.Data.SqlClient;

using MySql.Data.MySqlClient;

namespace SampleFileOpen

{

public partial class Form1 : Form

{

String MyCon = "SERVER=******;" +

"DATABASE=*****;" +

"UID=root;" +

"PASSWORD=*****;" + "Convert Zero Datetime = True";

public Form1()

{

InitializeComponent();

}

private void btnChooseFile_Click(object sender, EventArgs e)

{

OpenFileDialog dlg = new OpenFileDialog();

dlg.Filter = "Text files | *.csv";

if (dlg.ShowDialog() == DialogResult.OK)

{

string fileName;

fileName = dlg.FileName;

textBox1.Text = fileName;

}

private void btnSubmit_Click(object sender, EventArgs e)

{

string CSVpath = textBox1.Text;

var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.csv");

string File_Name = "aster_user";

string MyCon = ConfigurationManager.ConnectionStrings["MyCon"].ConnectionString;

for (int i = 0; i < AllFiles.Length; i++)

{

File_Name = AllFiles[i].Name;

DataTable dt = new DataTable();

using (MySqlConnection con = new MySqlConnection(string.Format(MyCon, CSVpath)))

{

using (MySqlDataAdapter da = new MySqlDataAdapter("select * from [" + File_Name + "]", con))

{

da.Fill(dt);

}

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MyCon))

{

bulkCopy.ColumnMappings.Add(1, "Column1");

bulkCopy.ColumnMappings.Add(2, "Column2");

bulkCopy.ColumnMappings.Add(4, "Column3");

bulkCopy.ColumnMappings.Add(5, "Column4");

bulkCopy.ColumnMappings.Add(8, "Column5");

bulkCopy.DestinationTableName = "aster_user";

bulkCopy.BatchSize = dt.Rows.Count;

bulkCopy.WriteToServer(dt);

bulkCopy.Close();

}

i had a problem in submit button i did some mistake in there ...

iam searching lot more please help me please.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Divya Sindhu10y ago
private void btnchoose_Click(object sender, EventArgs e)
{
OpenFileDialog openfiledialog1 = new OpenFileDialog();
openfiledialog1.ShowDialog();
openfiledialog1.Filter = "allfiles|*.xls";
txtfile.Text = openfiledialog1.FileName;
}
private void btnImport_Click(object sender, EventArgs e)
{
if(txtfile.Text != "")
{
string path = txtfile.Text;
string userid = "";
string password = "";
string first_name = "";
string last_name = "";
string user_group = "";
OleDbConnection my_con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
my_con.Open();
OleDbCommand cmd = new OleDbCommand("select*from [dataGridView1_Data$]", my_con);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
userid = dr[0].ToString();
password = dr[1].ToString();
first_name = dr[2].ToString();
last_name = dr[3].ToString();
user_group = dr[4].ToString();
MySqlConnection con = new MySqlConnection("SERVER=******;" +
"DATABASE=db;" +
"UID=uid;" +
"PASSWORD=pws;");
con.Open();
MySqlCommand cmmd = new MySqlCommand("insert into emp2(userid,password,first_name,last_name,user_group) values(@a,@b,@c,@d,@e)", con);
cmmd.Parameters.AddWithValue("a", userid);
cmmd.Parameters.AddWithValue("b", password);
cmmd.Parameters.AddWithValue("c", first_name);
cmmd.Parameters.AddWithValue("d", last_name);
cmmd.Parameters.AddWithValue("e", user_group);
cmmd.ExecuteNonQuery();
con.Close();
}
MessageBox.Show("Data Imported Successfully!","IMPORT DATA",MessageBoxButtons.OK,MessageBoxIcon.Asterisk);
txtfile.Text = "";
}
else if (txtfile.Text == "" )
{
MessageBox.Show("Empty Path. Please Select The File And Import The Data!", "WARNING",MessageBoxButtons.YesNo,MessageBoxIcon.Warning);
}

}
Rajeesh Menoth10y ago
Hi,

Check the below links :

http://theonetechnologies.com/outsourcing/post/mysql-bulk-data-import-using-net-connector-mysqlbulkloader-class.aspx

http://www.mysqltutorial.org/import-csv-file-mysql-table/

The answer is helpful.Then please accept it..!!
Raja T10y ago
Hi, Checkout below links

http://www.c-sharpcorner.com/Forums/Thread/47618/C-Sharp-importing-csv-files-into-datatables.aspx

http://www.codeproject.com/Articles/11435/Importing-CSV-Data-and-saving-it-in-database

http://www.mysqltutorial.org/import-csv-file-mysql-table/

http://stackoverflow.com/questions/11077801/import-csv-to-mysql-table

http://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table