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



*** Sent via Developersdex http://www.developersdex.com ***

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?


--
- Nicholas Paldino [.NET/C# MVP]
- (E-Mail Removed)

"Mike P" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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();
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

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



--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation




"Mike P" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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();
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Nicholas,

Do you have an example of using this method?


Thanks,

Mike



*** Sent via Developersdex http://www.developersdex.com ***