• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1273
  • Last Modified:

How to ignore comma if inside Quotes in a csv file

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
2ooth
Asked:
2ooth
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
2oothAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
>>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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
rg20Commented:
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
 
nepaluzCommented:
use the textfieldparser with settings of fieldsenclosedinquotes
0
 
amr-itCommented:
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
 
2oothAuthor Commented:
@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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
amr-itCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now