C# parse file with extra commas in a field

[CSharp Enthusiast]

Hi, I am using C# to parse a file with extra commas in a field and replace it with pipe(|). Please find sample data and the code I tried. This data does not have any text qualifier in such a case what would be my best bet to replace the column delimiter.

Sample Data:

Invoice Number,Invoice Status,Invoice Date of Service,Invoice Date Opened,Invoice Hold from printing/submission,Invoice Branch,Invoice Balances Adjustments,Invoice Balances Payments,Invoice Balances Balance,Patient Last Name,Patient First Name,Patient ID,Policy Payor Name,Invoice Detail Item ID,Invoice Detail Item Name,Invoice Detail Balance,Invoice Detail Qty,Invoice Detail Item Group
12345,Open,3/31/2009 12:00:00 AM,5/26/2009 8:09:02 AM,False,Test,($346.23),$0.00,$346.23,Smith,John,12344,[Patient],Test-1234,5""x8"" Multi-Axis Stump Support, Therafin, 31552,$51.12,1,Wheelchair Accessories


The below data is all in one field.

'5""x8"" Multi-Axis Stump Support, Therafin, 31552'

Code:

public void Main()
{
System.IO.StreamReader sr = new System.IO.StreamReader(@"Z:\file.csv");
string str1=(char)34+","+(char)34;
string str2=(char)34+"|"+(char)34;
string strContent = sr.ReadToEnd().Replace(str1, str2);
sr.Close();
sr.Dispose();

System.IO.StreamWriter sw = new System.IO.StreamWriter(@"Z:\file.csv",false);
sw.Write(strContent);
sw.Flush();
sw.Close();
sw.Dispose();
}



Thanks


SQLEnthusiast

Continue reading...