CSV to hash. Problem with delimiter inside double-quotes

[webu]

Hi,

if i have to import a comma-seperatet csv-file, i use to separtate the cols with code something like this:


Dim a$, b$
Dim ha As Hash String

Open "test.csv" for Input As # 1
 While EOF(# 1) = 0
   Line Input # 1, b$
   Split ha[] = b$, ","
   For Each a$ In ha[]
   ' do something with every item in the line
   Next
 Wend
Close 1
This works good with csv-lines like that:

Val1,Val2,Val3

But not with this:

Val1,Val2,"Oh,no"
Val1,"Oh,no",Val3
"Oh,no",Val2,Val3

Here are the commas inside in datas and the Split with comma would not do what we need.

What is the correct Split-Command? Sometimes, you have double quotes and in other lines you have not.

In this case we need the four possible delimter in one Split-command

1 ,
2 ,"
3 ",
4 ","

Has anybody a code-snipplet as a solution?

[scalion]

Hi,
Effectively it's a little problem... i write something and come back.

[webu]

I am seaching for a solution with split and delimter. Otherwiese you can read single bytes and parse for , and " and collect all bytes from " to " without checking for ,

This is the most seen solution.

[scalion]

Try it and tell me if that's work :

Dim a$, b$
Dim ha As Hash String
Dim cf%, i%
FullW 1
Open "test.csv" for Input As # 1
While EOF(# 1) = 0
 Line Input # 1, b$
 Print #13#10"Source : " & b
 Print #13#10"Hash method :"
 Split ha[] = b$, ","
 For Each a$ In ha[]
   Print "Hash : "; a
   ' rdo something with every item in the line
 Next
 cf = CountFieds(b, ",")
 Print #13#10"Code solution :"
 Print "CountFields=" & cf
 For i = 1 To cf
   Print GetField(b, ",", i)
 Next i
 Print
 Print "Press Key to continue...(Escape to stop)"
 KeyGet i
 If And(255, i) = 27
   Me.Close
   Close
   End
 EndIf
Wend
Do
 Sleep
Loop Until Me Is Nothing
CloseW 1

Function CountFieds(Source$, Separator$) As Long
 Local Long Scan = 1, Wsep, Wquote, WOC, WF
 Local Counter = 1
 Do
   PeekEvent
   Wsep = InStr(Source, Separator, Scan)
   Wquote = InStr(Source, #34, Scan)
   If Wsep = Scan
     Inc Scan
     If Scan > Len(Source) Return Counter
     Inc Counter
   Else If Wsep = 0
     Return Counter
   Else
     If Wquote > 0 And Wquote < Wsep
       WOC = 1
       For WF = Wquote + 1 To Len(Source)
         If Mid(Source, WF, 1) = #34 WOC = 1 - WOC
         If WOC = 0 And Mid(Source, WF, 1) = Separator
           Scan = WF + 1
           If Scan > Len(Source)
             Return Counter
           EndIf
           Inc Counter
           Exit For
         Else If WF = Len(Source)
           Return Counter
         EndIf
       Next WF
     Else
       Scan = Wsep + 1
       If Scan > Len(Source) Return Counter
       Inc Counter
     EndIf
   EndIf
 Loop
 
EndFunc

Function GetField(Source$, Separator$, Number%) As String
 
 Local Long Scan = 1, Wsep, Wquote, WOC, WF
 Local Counter = 1
 Do
   
   Wsep = InStr(Source, Separator, Scan)
   Wquote = InStr(Source, #34, Scan)
   If Wsep = Scan
     If Counter = Number
       Return ""
     Else
       Inc Scan
       If Scan > Len(Source) Return ""
       Inc Counter
     EndIf
   Else If Wsep = 0
     If Counter = Number
       Return Mid(Source, Scan)
     Else
       Return ""
     EndIf
   Else
     If Wquote > 0 And Wquote < Wsep
       WOC = 1
       For WF = Wquote + 1 To Len(Source)
         If Mid(Source, WF, 1) = #34 WOC = 1 - WOC
         If WOC = 0 And Mid(Source, WF, 1) = Separator
           If Counter = Number
             Return Mid(Source, Scan, WF - Scan)
           Else
             Inc Counter
             Scan = WF + 1
             If Scan > Len(Source)
               Return ""
             EndIf
             Exit For
           EndIf
         Else If WF = Len(Source)
           If Counter = Number
             Return Mid(Source, Scan)
           Else
             Return ""
           EndIf
         EndIf
       Next WF
     Else
       If Counter = Number
         Return Mid(Source, Scan, Wsep - Scan)
       EndIf
       Scan = Wsep + 1
       If Scan > Len(Source) Return ""
       Inc Counter
     EndIf
   EndIf
 Loop
 
EndFunc