none
How to handle comma within string RRS feed

  • Question

  • Hello there,

    I have csv file with the following information


    i used the following code

    using (FileStream fs = new FileStream(s, FileMode.Open, FileAccess.Read, FileShare.Read))
    using (BufferedStream bs = new BufferedStream(fs))
    using (StreamReader sr = new StreamReader(bs))
    {
       do
       {
          string line = sr.ReadLine();
          string[] vals = line.Split(',');
       }while (sr.Peek() != -1);
    }

    Using the above code, i got the following result, (which is this is not what i want)

    vals[0] = Test_1

    vals[1] = Test 2

    vals[2] = t

    vals[3] = est

    vals[4] = 3

    vals[5] = 10

    I want the result as below

    vals[0] = Test_1

    vals[1] = Test 2

    vals[2] = t,est,3

    vals[3] = 10

    The problem is the comma(,) within the string. How to solve this problem?

    Thank you.






    Friday, January 31, 2020 3:00 AM

Answers

  • Hi cahmad,

    Thank you for posting here.

    According to your description, I create a csv file and make a test on my side.

    Content in my csv file:

    I note that ' t,est,3 ' in 'line' is ' "t,est,3" '.

    So I use the following code to get the result you want.

            static string Replace(string input)
            {
                string pattern = "\".*?\"";
                Regex regex = new Regex(pattern);
                var match = regex.Matches(input);
                string result = null;
                foreach (var m in match)
                {
                    string output = Regex.Replace(m.ToString(), ",", "|");
                    result = input.Replace(m.ToString(), output);
                    input = result;
                }
                return input;
            }
            static void Main(string[] args)
            {
                string path = "file path";
                using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read))
                using (BufferedStream bs = new BufferedStream(fs))
                using (StreamReader sr = new StreamReader(bs))
                {
                    do
                    {
                        string line = sr.ReadLine();
                        string line2 = Replace(line);
                        string[] vals = line2.Split(',');
                        string[] vals2 = vals.Select(x => x.Replace("|", ",")).Select(x => x.Replace("\"", "")).ToArray();
                        foreach (string str in vals2)
                        {
                            Console.WriteLine(str);
                        }
                    } while (sr.Peek() != -1);
                }
                Console.ReadLine();
            }

    Result of my test:

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    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.

    • Marked as answer by cahmad Friday, January 31, 2020 7:25 AM
    Friday, January 31, 2020 7:02 AM
    Moderator

All replies

  • This is data issue. The csv file is not well formed. One of the solution is to have different delimeter like | in the csv if the data in it can have comma in the text. Otherwise it will be difficult to identify it's a one item or multiple items comma separated.

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 31, 2020 5:54 AM
  • If possible, reconsider the generation of CSV files. For example, make it:

    "Column A","Column B","Column C","Column D","Column E"

    "Test_1","Test_2","t,est,3",10,"some""text"

    . . .

    I.e. use quotation marks.

    See how other programs (e.g. Excel) export such files.

    Then use TextFieldParser library class to simplify the reading operation.

     


    • Edited by Viorel_MVP Friday, January 31, 2020 6:17 AM
    Friday, January 31, 2020 6:16 AM
  • Hi cahmad,

    Thank you for posting here.

    According to your description, I create a csv file and make a test on my side.

    Content in my csv file:

    I note that ' t,est,3 ' in 'line' is ' "t,est,3" '.

    So I use the following code to get the result you want.

            static string Replace(string input)
            {
                string pattern = "\".*?\"";
                Regex regex = new Regex(pattern);
                var match = regex.Matches(input);
                string result = null;
                foreach (var m in match)
                {
                    string output = Regex.Replace(m.ToString(), ",", "|");
                    result = input.Replace(m.ToString(), output);
                    input = result;
                }
                return input;
            }
            static void Main(string[] args)
            {
                string path = "file path";
                using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read))
                using (BufferedStream bs = new BufferedStream(fs))
                using (StreamReader sr = new StreamReader(bs))
                {
                    do
                    {
                        string line = sr.ReadLine();
                        string line2 = Replace(line);
                        string[] vals = line2.Split(',');
                        string[] vals2 = vals.Select(x => x.Replace("|", ",")).Select(x => x.Replace("\"", "")).ToArray();
                        foreach (string str in vals2)
                        {
                            Console.WriteLine(str);
                        }
                    } while (sr.Peek() != -1);
                }
                Console.ReadLine();
            }

    Result of my test:

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    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.

    • Marked as answer by cahmad Friday, January 31, 2020 7:25 AM
    Friday, January 31, 2020 7:02 AM
    Moderator
  • Hi Xingyu Zhao, Great, it worked exactly... Thank you very much...
    Friday, January 31, 2020 7:27 AM