Removing Quotes

[Bruce Peaslee]

I'm trying to parse a CSV file that looks something like this:

8/12/2010,"Egg, Whole", 1 small, 54.4, ...

If I just search for the commas, I get messed up by the commas within quotes. So, I would like to ditch the quotes and turn the "internal" commas into something like "~".

Here is the code. It does not work. The quotes are passed on.

The first two "fields" show as:   8/12/2010    "Egg

Code Select Expand


For i = 1 to len(sDataLine)
   c = Mid$(sDataLine, i, 1)
   Select c
      Case "/""
         If bGotParen = true
bGotParen = false
         Else
bGotParen = true
         EndIf
      Case ","
         If bGotParen
sParsed += "~"
         Else
sParsed += ","
         EndIf
      Default
         sParsed += c
   EndSelect
Next i


[LarryMc]

Change this

Code Select Expand

Case "/""
to this

Code Select Expand

Case "\""

[billhsln]

Here is a program I wrote to correctly parse CSV files with Quotes (").

This will handle the Quotes correctly, even with a Comma inside of the Quotes.

Bill

[billhsln]

Problem with changing " to ~, when you have some thing like "egg, whole", will become ~egg, whole~, and end up as 2 fields instead of the one it should be.

So:  8/12/2010,"Egg, Whole", 1 small, 54.4, ...

would be:

8/12/2010
~Egg
Whole~
1 small
54.4

where I would think you would want:

8/12/2010
Egg, Whole
1 small
54.4

Bill

[Bruce Peaslee]

@Bill:  the "~" was just to get things moving. I'm using "-" which shows OK. The final result goes into a Listview, so I might put the commas back!  

@Larry: resetting sParsed = "" (and changing to "\"") worked. I still didn't get a good result until I realized I, after removing the quotes, went back to working on the original string, not the new one.  

BTW: this won't compile: bGotParen ?    sParsed += "~" : sParsed += ","

Thanks to all.

[LarryMc]

BTW: this won't compile: bGotParen ?    sParsed += "~" : sParsed += ","

Found that out after I posted.
Seems the IIF statement doesn't like the +=.
I deleted my post and I'll look into this more later so I can add some more info in the help file about the IIF statement.

[aurelCB]

Here is the quick one with Tally function.
Just type into editbox your coma delimited string.

Code Select Expand

'Menu by API
WINDOW w1
STRING mline,t$
int y ,dy,cc
REM open the main window
OPENWINDOW w1,0,0,550,350,@MINBOX|@MAXBOX|@SIZE,0,"String functions",&main
Setwindowcolor w1,rgb(210,210,220):BACKPEN w1,RGB(210,210,220)
SETFONT w1,"Verdana",10, 400,0
'------------------------------------------
CONTROL w1,@Edit,"",200,40,320,22,0x50800000,1
SETFONT w1,"Verdana",10, 400,0,1
CONTROL w1,@sysButton,"Comma Count",200,120,200,26,0x50000000,2
CONTROL w1,@sysButton,"Del$.Pos + Show.Arguments",200,150,200,26,0x50000000,3
'test Tally(m$,d$)
'Move w1,10,10
'PRINT w1,"Test Tally: ",Tally(t$, ",")
DEF S1$ :STRING
DEF dl$,sRet$ :STRING
DEF dpos[16]:int
DEF pStr[16]:STRING





'sRet$ = MID$(S1$,j,(i-j))
'y=y+30
'Move w1,10,y:PRINT w1,sRet$
'-----------------------------------------

WAITUNTIL w1 = 0
END
'---

SUB main


IF @message = @IDCLOSEWINDOW
   CLOSEWINDOW w1
ENDIF
'for menu hendling
IF @message = @IDCONTROL

IF @CONTROLID = 2
IF @NOTIFYCODE=0
t$=GetControlText(w1,1)
cc=Tally(t$, ",")
Move w1,200,80
PRINT w1,"Test Tally: ",cc
ENDIF
ENDIF

IF @CONTROLID = 3
IF @NOTIFYCODE=0
Setwindowcolor w1,rgb(210,210,220):BACKPEN w1,RGB(210,210,220)
For i=1 to cc
dy=dy+30
Move w1,20,dy:print w1,dpos[i]
Next i
showArgs()
ENDIF
ENDIF


ENDIF

RETURN
ENDSUB
'------------------------------------------------------------
SUB showArgs
int k,i,j
't$= one ,
i = 0
j = 1
k = 1
y=0
FOR i = 1 TO LEN(t$)

           IF i=dpos[k]
k=k+1
                pStr[k] = MID$(t$,j,(i-j))
'remove quote -----------------------
IF LEFT$(pStr[k],1) = CHR$(34)
pStr[k] = MID$(pStr[k],2,LEN(pStr[k])-3)
ENDIF
'---------------------------------
y=y+30
Move w1,50,y:PRINT w1,pStr[k]           
            j = i + 1
        ENDIF
    NEXT i
'+extract last argument
k=k+1
pStr[k] = MID$(t$,j,LEN(t$))
y=y+30
Move w1,50,y:PRINT w1,pStr[k]

RETURN
ENDSUB
'-------------------------------------------------------------
'trim func
SUB Trim(inString:string),string
STRING res
res= LTRIM$(Rtrim$(inString))
RETURN res
ENDSUB



SUB Tally(STRING Main$,STRING Match$),INT
    DEF i,j,q,mlen,matchlen :INT
DEF t$:STRING
    mlen = LEN(Main$)
    matchlen = LEN(Match$)
    i = 1
    j = 0
    q = 0
    IF (mlen = 0) OR (matchlen = 0)
        RETURN j
    ENDIF
   
    WHILE 1

t$=MID$(Main$,i,matchlen)
IF t$ = CHR$(34) THEN q = q + 1
IF q=2 THEN q = 0
        IF t$ = Match$ AND q=0
           j = j + 1
'mem del$ position
dpos[j]=i
        ENDIF

           i = i + 1
        IF i > mlen
           Goto tbreak
        ENDIF
    WEND
Label tbreak
    RETURN j
ENDSUB