How to Bulk Copy from CSV to SQL Server Table

[ryguy72]

Im trying this code below:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
// C:\Users\Ryan\Documents\Visual Studio 2010\Projects\WindowsFormsApplication1\WindowsFormsApplication1\bin\
const string CSV_CONNECTIONSTRING = "Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\";Extended Properties=\"text;HDR=Yes;FMT=Delimited";
string CSVpath = "C:\\Users\\Ryan\\Documents\\Visual Studio 2010\\Projects\\WindowsFormsApplication1\\WindowsFormsApplication1\\bin\\Orders\\"; // CSV file Path
var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
string File_Name = string.Empty;

for (int i = 0; i < AllFiles.Length; i++)
{
try
{
File_Name = AllFiles.Name;
DataTable dt = new DataTable();
using (OleDbConnection con = new OleDbConnection(string.Format(CSV_CONNECTIONSTRING, CSVpath)))
{
using (OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + "Sheet1" + "]", con))
{
da.Fill(dt);
}
}

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(CSV_CONNECTIONSTRING))
{
bulkCopy.ColumnMappings.Add(0, "Group");
bulkCopy.ColumnMappings.Add(1, "ID");
bulkCopy.ColumnMappings.Add(2, "Name");
bulkCopy.ColumnMappings.Add(3, "Address");
bulkCopy.ColumnMappings.Add(4, "Country");
bulkCopy.DestinationTableName = "AllEmployees";
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}

}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Message");
}

}
}

}

}


The code fails on this line:

using (OleDbConnection con = new OleDbConnection(string.Format(CSV_CONNECTIONSTRING, CSVpath)))


Heres the error message:

If there is an easier way to do this, please advice. Im just doing this as a learning exercise.


Thanks everyone!




Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Continue reading...