0

I am parsing a file for data import which is comma delimited, but there are commas within quoted strings. All strings are in double quotes: Example: 123456, "Firstname","LastName","User, Website", 123456,,,, (notice embedded comma in last string) and numbers do not have double quotes.

I have replaced the 3 characters "," with "|". (A line or pipe delimiter with double quotes around it.)

Now I need to replace all of the commas (outside quoted strings) with the | (line) delimiter. I am new to Regex in C#, and tried the following:

                string pattern ="\",\"";
                char charpipe;
                charpipe = '|';
                string charpipestring = "\"" + charpipe + "\"";

                Regex commapattern = new Regex("(,)[2,]");
                int i = 0;
                StringBuilder line2save = new StringBuilder();

                //Replace "," with "|" THIS WORKS
                string newline = line.Replace(pattern, charpipestring);
                Console.WriteLine(newline);

                Regex regresult = new Regex(newline);
                
                //Replace commas outside strings with |s THIS DOES NOT WORK
                line2save = line2save.Append(regresult.Replace(newline, commapattern.ToString()));

The output from the Console.Writeline(newline) is: "0014905"|"24"|"10/14/2011"|"22290030C9",,,,

When attempting to replace remaining commas with line delimiter, the output from line2save after my attempt at Regex is: {(,)[2,]|(,)[2,]|(,)[2,]|(,)[2,]|(,)[2,]|(,)[2,]|(,)

I expect: "0014905"|"24"|"10/14/2011"|"22290030C9"||||

How do I replace the remaining commas with |s using Regex?

StevenJe
  • 82
  • 2
  • 14
  • Additionally, I use the ,,,, as just an example. There could be any number of commas to replace with | (pipe or line) – StevenJe Sep 21 '22 at 19:30
  • 1
    In the first line you say: _I am parsing a file for data import which is comma delimited_ then use a library that can handle this task and don't waste time to do it yourself. – Steve Sep 21 '22 at 19:31
  • Steve - I use BCP (command line and through C#), SSIS, and various other import tools. Please tell me which of these or what tool will ignore embedded commas in strings when a comma is the delimiter? This is inherited code that I am trying to fix from a previous developer. – StevenJe Sep 21 '22 at 19:34
  • https://www.filehelpers.net/ and a question similar https://stackoverflow.com/questions/42974657/filehelpers-quote-and-comma-in-fields – Steve Sep 21 '22 at 19:51

0 Answers0