none
C# parse file with extra commas in a field RRS feed

  • Question

  • 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


    Friday, June 1, 2018 9:09 PM

All replies

  • You really, really, really need to get them to fix the data. Data that can have commas in it that is delimited by commas without an additional delimiter is wrong. It is very bad data. I assume you did not make that decision, if you did then I apologize but I assume you agree that they are doing it wrong.

    Apparently there are 18 items. You can split the data at all commas, as in s.Split(','). Then if there are 18 items then you can use them without special processing. If there are more than 18 then do the following. Take the first n1 items, whatever are the number of items that precede the field. Correspondingly, take the last n2 items, whatever is the number that follows the field. Then you know that the number of additional items is the number of items in the array minus 18. Do a join starting with the item at the position that the item begins and continuing for the number of extra items. Do you understnand? If there is anything you ned help with then say so.



    Sam Hobbs
    SimpleSamples.Info

    • Proposed as answer by Dolen Zhang Monday, June 4, 2018 8:11 AM
    • Unproposed as answer by Dolen Zhang Monday, June 4, 2018 8:33 AM
    • Proposed as answer by Dolen Zhang Monday, June 4, 2018 8:46 AM
    Friday, June 1, 2018 10:18 PM
  • Hi SQLEnthusiast,

    what's the status of the issue now? Have you followed Sam's suggestion to format the data? In automated processing of data, it is necessary to format data before processing it.

    Regards,

    Dolen


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 27, 2018 8:00 AM
  • Hi,

    >>Hi, I am using C# to parse a file with extra commas in a field and replace it with pipe(|).

    What do you mean about extra commas?

    If you want to replace ',' to '|', just use:

        string str = "some,string";
        str = str.Replace(',', '|');

    Or maybe you want to do some more complicated replacement?

    Regards,

    Frankie


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 4, 2018 7:52 AM