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();
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();