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 =newOleDbConnection(conString);

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

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

OleDbDataReader objDataReader;

try

{

objConnection.Open();

objCmd =

newOleDbCommand(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

It is not practical to use ExecuteNonQuery to import a CVS file because the data needs to be cleaned before insert so try the link below for a step by step guide to import the data into SQL Server. The reason it is failing is you are sending ADO.NET a data access component to perform ETL(extraction transformation and loading) task. Hope this helps.

http://www.databasejournal.com/features/mssql/article.php/3580216

|||

the code works but only for csv that mirrors the table in structure, I want to create a query that picks certain columns from the csv parses them and pastes them into a table

regards

Simon

|||

If the columns are the same that is a standard insert, picking columns is again ETL, try the CVS parser from the link below and ADO.NET is not used. Hope this helps.

http://www.heikniemi.net/hc/archives/000152.html

|||

I'll give it a go and get back to you seems a good way around it. Can I use it to iterate over each seperate line and load the values i need into a temporary dataset I.e containing a pseudo table mirroring that in sql server

Simon