Solved

How to ignore comma if inside Quotes in a csv file

Posted on 2010-09-20
9
1,198 Views
Last Modified: 2012-05-10
I am parsing a csv file and spliting the values in each row by comma( , ) delimiter(ref Code section)


however i want to ignore the comma if it is in double quotes like in case of "AUSTRIA, REPUBLIC OF (GOVERNMENT)"

how can I do this?
string aLine = "";

aLine = 1,abc,RPN,,"AUSTRIA, REPUBLIC OF (GOVERNMENT)",95740,15-01-2014

string[] buffer = aLine.Split(new String[] {","}, StringSplitOptions.None);

Open in new window

0
Comment
Question by:2ooth
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 

Author Comment

by:2ooth
ID: 33717920
Hi,

Thank you for your reply..

However i am reluctant to use a Microsoft.VisualBasic.dll as part of my code as my project is part of a larger application and i may not get approval to use this...

Is there no other way to ignore commas when its within quotes?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 33718007
>>However i am reluctant to use a Microsoft.VisualBasic.dll as
It is part of the .Net framework. Any good reason not to use it?
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 9

Expert Comment

by:rg20
ID: 33718032
I would parse on the quotes first using a regexp.  Then on the fields that have quotes, replace all commas with a | or something.

Join them back together, then you can split on the comma's.

Change | back to comma where needed.
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 33718172
use the textfieldparser with settings of fieldsenclosedinquotes
0
 
LVL 1

Expert Comment

by:amr-it
ID: 33720154
Have a look at filehelpers:

http://filehelpers.sourceforge.net/

Maybe you could use Regex to LINQ or LINQ to Dictonary, I haven't used it myself so If you would try, I would appreciate the outcome of it.

http://omegacoder.com/?p=542

Cheers
0
 

Author Comment

by:2ooth
ID: 33721497
@emoreau and nepaluz

I'm unable to use Microsoft.VisualBasic.dll in my project as part of a policy for which i cannot disclose (believe me, I have genuine reason not to use it )

I was hoping that this could be achieved through a regular expression.. however a quick search proved futile..

I tried to implement a solution mentioned in http://omegacoder.com/?p=542 however it failed if i pass comma in quotes as part of a string.

Is there no simple text parsing function / Regular expression that can give me the desired result?
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 300 total points
ID: 33721515
I have this method (in VB but you can translate it to pure C#):
 

    Public Shared Function ParseLineQuoteComma(ByVal pLine As String) As String()
        If pLine Is Nothing Then
            Throw New ArgumentNullException("pLine")
        End If

        Dim arrFields() As String = {}
        Dim blnPreviousCharIsDelimiter As Boolean
        Dim blnStartsWithQuotes As Boolean
        Dim intIndice As Integer
        Dim strChar As String
        Dim strField As String = ""

        For inti As Integer = 0 To pLine.Length - 1
            'Trace.WriteLine(pLine.Substring(inti, 1) & " - " & Convert.ToInt32(Convert.ToChar(pLine.Substring(inti, 1))))
            strChar = pLine.Substring(inti, 1)
            If strChar = """" Then
                blnPreviousCharIsDelimiter = False
                If blnStartsWithQuotes Then
                    'we have reached the end of the field
                    blnStartsWithQuotes = False
                    ReDim Preserve arrFields(intIndice)
                    arrFields.SetValue(strField, intIndice)
                    intIndice += 1
                    strField = ""
                Else
                    'we are starting a new field
                    blnStartsWithQuotes = True
                End If
            ElseIf (strChar = ",") OrElse Convert.ToInt32(Convert.ToChar(strChar)) = Convert.ToInt32(Microsoft.VisualBasic.ControlChars.Tab) Then
                If blnStartsWithQuotes Then
                    'This space is part of the field
                    blnPreviousCharIsDelimiter = False
                    strField &= strChar
                Else
                    If (strField.Length > 0) OrElse blnPreviousCharIsDelimiter Then
                        'we have reached the end of an unquoted field
                        ReDim Preserve arrFields(intIndice)
                        arrFields.SetValue(strField, intIndice)
                        intIndice += 1
                    End If
                    strField = ""
                    blnPreviousCharIsDelimiter = True
                End If
            Else
                    'Any other character
                    blnPreviousCharIsDelimiter = False
                    strField &= strChar
            End If
        Next

        If strField.Length > 0 Then
            ReDim Preserve arrFields(intIndice)
            arrFields.SetValue(strField, intIndice)
            intIndice += 1
            strField = ""
        End If

        Return arrFields
    End Function

Open in new window

0
 
LVL 1

Assisted Solution

by:amr-it
amr-it earned 200 total points
ID: 33724907
Here's the translated version, added example of calling the method.
(Removed tab-option because of VisualBasic.ControlChars.Tab)

public void CsvParse()
        {

            string[] data = {"1,abc,RPN,,\"AUSTRIA, REPUBLIC OF (GOVERNMENT)\",95740,15-01-2014",
                            "2,42c,PNC,,\"SWEDEN,,,\",60219,15-01-2014",
                            "3,asc,N,,\"GERMANY, (GOVERNMENT)\",5531,15-01-2014"};
            string[] Out = { };

            foreach (string line in data)
            {
                Out = ParseLineQuoteComma(line);
            }
            //return Out;
        }

        public string[] ParseLineQuoteComma(string pLine)
        {
            if (pLine == null)
            {
                throw new ArgumentNullException("pLine");
            }

            string[] arrFields = { };
            bool blnPreviousCharIsDelimiter = false;
            bool blnStartsWithQuotes = false;
            int intIndice = 0;
            string strChar = null;
            string strField = "";

            for (int inti = 0; inti <= pLine.Length - 1; inti++)
            {
                //Trace.WriteLine(pLine.Substring(inti, 1) & " - " & Convert.ToInt32(Convert.ToChar(pLine.Substring(inti, 1))))
                strChar = pLine.Substring(inti, 1);
                if (strChar == "\"")
                {
                    blnPreviousCharIsDelimiter = false;
                    if (blnStartsWithQuotes)
                    {
                        //we have reached the end of the field
                        blnStartsWithQuotes = false;
                        Array.Resize(ref arrFields, intIndice + 1);
                        arrFields.SetValue(strField, intIndice);
                        intIndice += 1;
                        strField = "";
                    }
                    else
                    {
                        //we are starting a new field
                        blnStartsWithQuotes = true;
                    }
                }
                else if ((strChar == ","))
                {
                    if (blnStartsWithQuotes)
                    {
                        //This space is part of the field
                        blnPreviousCharIsDelimiter = false;
                        strField += strChar;
                    }
                    else
                    {
                        if ((strField.Length > 0) || blnPreviousCharIsDelimiter)
                        {
                            //we have reached the end of an unquoted field
                            Array.Resize(ref arrFields, intIndice + 1);
                            arrFields.SetValue(strField, intIndice);
                            intIndice += 1;
                        }
                        strField = "";
                        blnPreviousCharIsDelimiter = true;
                    }
                }
                else
                {
                    //Any other character
                    blnPreviousCharIsDelimiter = false;
                    strField += strChar;
                }
            }

            if (strField.Length > 0)
            {
                Array.Resize(ref arrFields, intIndice + 1);
                arrFields.SetValue(strField, intIndice);
                intIndice += 1;
                strField = "";
            }

            return arrFields;
        }
    }
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question