dealing with commas in fields of csv file

M

Mike P

I am trying to write a csv file to a database table, but the problem is
that several fields have commas within them, so the code is reading the
commas as the end of fields. My code is below. How do I get around
this?

Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();

StreamReader sr = new
StreamReader("\\\\devext02\\Xerox_Upload\\archive\\" + strFileName +
".csv");
string fullFileStr = sr.ReadToEnd();
sr.Close();
sr.Dispose();

string[] lines = fullFileStr.Split('\n');
DataTable dt = new DataTable();

string[] sArr = lines[0].Split(',');

foreach (string s in sArr)
{
dt.Columns.Add(new DataColumn());
}

DataRow row;
string finalLine = "";

foreach (string line in lines)
{
row = dt.NewRow();
finalLine = line.Replace(Convert.ToString('\r'), "");
row.ItemArray = finalLine.Split(',');
dt.Rows.Add(row);
}

SqlConnection objConnection = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"]
ConnectionString);

System.Data.SqlClient.SqlBulkCopy bc = new
System.Data.SqlClient.SqlBulkCopy(objConnection,
SqlBulkCopyOptions.TableLock, null);

bc.BatchSize = dt.Rows.Count;
objConnection.Open();
bc.DestinationTableName = "UploadDataStaging";
bc.WriteToServer(dt);
objConnection.Close();
bc.Close();
TimeSpan ts = stopWatch.Elapsed;
stopWatch.Stop();
 
N

Nicholas Paldino [.NET/C# MVP]

Mike,

If it is a CSV file, instead of trying to parse it yourself, why not use
the text provider for OLEDB and use the classes in the System.Data.OleDb
namespace to access the contents of the file as a data set?
 
I

Ignacio Machin \( .NET/ C# MVP \)

Hi,

A " character is used to enclose a field that contains a comma as part of
hte values.

The easiest solution can be or either use OleDb data provider or go to
opennetcf.org and download theirs, you will have the source code that you
can browse/modify at will
 
Top