C# Application - Method To Import Delimeted CSV To Sql Server Table

Hi,

I'm writing a telecom billing system where it is necessary to import csv delimited files into a sql server table.

1) The csv files to import differ in column arrangement i.e SQL server table arrangement = A, B, C, D whilst CDR = - , A, -, C, D

2) The csv files are delimited via different characters i.e some are del.. by commas some by semi colons

 I can import a CSV file from my application using a BULK INSERT query but only using CSV that resembles the structure of the table exactly

How can I pass a CSV to the table and state where I want each CSV column to fill a column in the SQL SERVER table

Below is how I am managing to pass a CDR to the SQL table that I have pre manufactured to resemble the structure of the SQL table Is there a way I can state which values from the CSV I want to pass to corresponding columns in the SQL table:

 

string conString = @"Provider=SQLOLEDB;Server=(local);Database=Billing;Uid=sa;Pwd=sa;";

for (int i = listBox1.Items.Count - 1; i >= 0; i--)

{

string strSQL;

OleDbConnection objConnection = null;

OleDbCommand objCmd = null;

objConnection = new OleDbConnection(conString);

 

string cdr = listBox1.GetItemText(listBox1.Items[i]);

 

strSQL = "BULK INSERT Campaign FROM '"+ cdr +"' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '
')";

OleDbDataReader objDataReader;

 

try

{

objConnection.Open();

objCmd = new OleDbCommand(strSQL, objConnection);

objCmd.ExecuteNonQuery();

objCmd = null;

objDataReader = null;

objConnection.Close();

listBox1.Items.Remove(listBox1.Items[i]);

}

catch (Exception ex)

{

objConnection.Close();

}

objCmd = null;

objConnection = null;

}

 

Regards

 Simon