Solved

How to ignore comma if inside Quotes in a csv file

Posted on 2010-09-20
9
1,168 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 33717828
0
 

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 69

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 69

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now