[RESOLVED] Ingoring comma, where comma is the delimiter VBA (access)

[kfcSmitty]

I am having a problem; I have a string as follows:

FirstName,LastName,PostalCode,"some address, with comma", whatever


I am using the split function to break it down into an array. The problem I am having, is the array comes out like this:

FirstName
LastName
PostalCode
"some address
with comma"
whatever



The output I would like, is to have it like this

FirstName
LastName
PostalCode
"some address, with comma"
whatever

All addresses that contain commas will be encased with ".

*edit*
Sorry, It is not just the address that may contain commas, any field may. Although any field wit a comma that is not meant to be used as a delimiter will be encased in "'s.
*end edit*

I am reading the string in 1 line at a time before I actually split by the delimiter.

Sorry I cant post any code, as I am at work atm..but pseudocode is

Do While NOT EOF
  textLine = ReadLine (bleh.txt)
  splitLinex = Split(textline,",")
Loop

Thanks,

Smitty

[salvelinus]

Well, you could first replace any comma between quotes with a semicolon or similar, split, then replace the semicolon. Maybe there's an easier way, but that idea pops up.

[kfcSmitty]

Well, you could first replace any comma between quotes with a semicolon or similar, split, then replace the semicolon. Maybe there's an easier way, but that idea pops up.

Thanks for he fast reply, I was thinking along those lines, but I cant seem to figure out how to remove the comma only between the quotes.

Maybe I am just blanking out

[kfcSmitty]

Figured it out

Option Compare Database
 
Private Sub Command0_Click()
Dim txtSplitter As String
'txtSplitter = "Aditya Vinayak,,Naik,D/8 Jeevan Bahar," & Chr(34) & "21st Road, chembur" & Chr(34) & ",Mumbai,MAH,400071,India,[email protected]"
txtSplitter = Chr(34) & ",,,,,,,,,,,,," & Chr(34) & ",test,test,test"
Dim secSplit() As String
secSplit = Split(txtSplitter, ",")
txtSplitter = ""
Dim i, tmpNum As Integer
Dim tmpString As String
For i = LBound(secSplit) To UBound(secSplit)
 
 
  If InStr(secSplit(i), Chr(34)) Then
  
       tmpNum = tmpNum + 1
    
       tmpString = tmpString + secSplit(i)
       
       If tmpNum = 2 Then
         tmpNum = 0
         txtSplitter = txtSplitter & tmpString & ","
       End If
 
     ElseIf tmpNum < 2 And tmpNum > 0 Then
     
         txtSplitter = txtSplitter & secSplit(i)
     
     Else
 
       txtSplitter = txtSplitter & secSplit(i) & ","
 
  End If
Next
 
secSplit = Split(txtSplitter, ",")
 
ReDim Preserve secSplit(UBound(secSplit) - 1) As String
 
For i = LBound(secSplit) To UBound(secSplit)
   MsgBox secSplit(i)
Next
 
End Sub

[salvelinus]

An easier option, if you can do it, is to use a different delimiter. But that may be out of your hands.

[Ecniv]

I know you have done it but I thought I would post some options anyway.
1) read through the source string.
-- for each char, add to an output array or string variable.
-- if the string starts with speech marks, enable a flag to so the sub knows that there are open quotes
---- keep adding to the output array/string until closing quotes or end of line
-- close string/array item (move to next column/row as applicable)

2) split the line by commas
-- go through each entry and check the first and last chars.
---- if the first is speechmarks but the last is not, merge with the next column
---- as in (1) hold a variable to be set true/false when the speechmarks are found.

I will have to be doing this soon too. as my work place have a couple of files to be imported into a db.

[salvelinus]

Another way is to split along commas like you did originally, then concatenate splitLinex(3) & (4).

[kfinpgh]

go to www.mvps.org/access and search for findandreplace(). this function allows you to replace any character or substring in a string with anything else. I used it to replace the comma with " and", then reversed the process after the user clicked OK on the form.

[kfcSmitty]

Thanks for all the helpful tips. I actually ended up changing the code, as this one would error if there was an odd number of quotations.

The code i finally decided to go with was:

Function FindQuotes (strUserInfo As String) As String
    
    Dim strTemp() As Variant
    Dim numQuotes As Integer
    Dim strFinal As String
    
    
    For i = 1 To Len(strUserInfo)
        Redim Preserve strTemp(i)  As Variant
        strTemp (i) = Mid$(strUserInfo, i, 1)
        If strTemp(i) = Chr(34) Then
            numQuotes = numQuotes + 1
        End If
        If numQuotes > 1 Then
            numQuotes = 0
        Elseif numQuotes > 0 Then
            strTemp(i) = Replace(strTemp(i),",","")
        End If
        strFinal = strFinal + strTemp(i)
    Next
    
    
    FindQuotes = strFinal
    
    
End Function