Comma delimeted file - problems - Help

Hello,

I'm trying to inport in a csv file to a vb array, and trying to split the following:

1,CYH7877,Happy,"my house, my home",0928283,RED

what i want to do is split the fields (seperated by , ) but the field in which a comma occurs inside quotes; MUST NOT be split, all the info inside the quotes is pulled into a field and left in quotes.

e.g.

row 0,3 = "my house, my home"
NOT
row 0,3 = my house
row 0,4 = my home

So the program just skips over the quotes and takes the whole quoted text as a field.

Sorry if this was not made clearer in my previous postings.

kind regards

p.s. Thanks fisherman for all your help so far.

One solution: When the application encounters a double-quote take all the information until it hits a closing double quote. Create some sort of exception clause to handle this in your parsing routine.

ok... taking your sample, I made this.

Hi,

We had a similar problem with ‘ (single quotes) “O’Brians”
and creating SQL strings.

If you create the csv yourself this approach may work:

1. Before creating the csv replace all , with something like ^^
2. Create your csv file it should look like below
1,CYH7877,Happy,my house^^ my home,0928283,RED
3. Load it into your array
4. In your array replace all ^^ with ,

Regards,

Edi

good idea. Didn't think about that. Thanks Lueg!

victor - wouldn't you need to read the string character by character for that?

Fisherman, you are correct. That would force a character by character iteration. Not the most elegant method, and definitely not for a huge flat file, but would allow the user a granular customization. If the file was large I would suggest taking the time to import the file into some sort of database and have Visual Basic read that instead of a flat file.

mel- I'm just curious. Did you resolve your problem?

Hi Fisherman, I used a tempory measure, of changing all the commas to ^ prior to using my program, i will keep a look out for a proper solution.

Thanks for your help..

hi,

you can get that done by just usinng the following code,

As you are using the .csv file you can split this line

1,CYH7877,Happy,"my house, my home",0928283,RED

like using the following code:


open "c:\temp.csv" for input as #1

do while not eof(1)

input #1, row, str1, str2, str3, str4,str5,str6,str7


loop



Please let me know wht the result is::

All The Best,

bye

mid - wouldn't that still split the string inside the quotes?

hi,

i am sorry for that, but my code works if the same file has the data stored completely in the double codes like,

"1","xxx","dddd"

if the data is in that format, it works well.

ok bye

Hello,

is somebody in here willing and able to solve my problem as well? I'd like to read info from a csv-file, but the format of the first entry is strange. What I do:

Set csvfile = fso.OpenTextFile(strFilePath & strFileName, 1)
Dim dummy_array() As String
sline = csvfile.ReadLine
dummy_array() = Split(sline, ",")

Now the data looks like:
csv-line: NUMBER,HNUM, ... ,TERMTI

and the variables like:
sline: : ""NUMBER,HNUM, ... ,TERMTI"
CStr(dummy_array(0)): ""NUMBER"

... while I want the latter to look like "Number" (note the number of *"*). Where does this strange starting " come from, and how to get rid of it??? The moment I want to read numbers, the resulting ""1" suddenly is not recognized as numeric anymore (while it should be ).

I appreciate any comment!
Frank

found this code on the net:
Option Explicit

'
'
'
'
' This shows how to parse strings (separate line of text into words)

Private Sub Command1_Click()
Dim linoftext$, i As Integer, delimiter$, k As Integer, B$
delimiter$ = Text2.Text
linoftext$ = Text1.Text
' First, make all the separators the same
For i = 2 To Len(delimiter$)
k = InStr(linoftext$, Mid$(delimiter$, i, 1))
Do While k
Mid$(linoftext$, k, 1) = Left$(delimiter$, 1)
k = InStr(linoftext$, Mid$(delimiter$, i, 1))
Loop
Next
' Now parse it!
Do While Len(linoftext$)
k = InStr(linoftext$, Left$(delimiter$, 1))
If k = 1 Then
linoftext$ = Mid$(linoftext$, 2)
ElseIf k Then
B$ = Left$(linoftext$, k - 1)
' Debug.Print B$
List1.AddItem B$
linoftext$ = Mid$(linoftext$, k + 1)
Else
List1.AddItem linoftext$
linoftext$ = ""
End If
Loop
End Sub

Hi Larryjoe,

thank you for your reply. It answers one of my quesions, but not the problem described above - that one I solved yesterday (I just didn't post about it, sorry!). It appears that the format of the input file was at fault - the first two lines were actually formatted as 'strings of strings', whereas the remainder were just strings. I could not see this because my excell suppressed the format. A simple editing action in an ascii-editor solved the whole problem rather easily.

Thank you for you suggestion!
Frank