Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    333

    Unanswered: Find and replace character

    I haven't played with VBA for a while and am wasting a lot of time trying to do something simple.

    I have CSV file that is generated by a third party that I have no control over. I'm importing this file into my database and this works most of the time. Every now and then someone that is generating the file uses a comma in a description field. Of course, this throws a wrench in things when I try to import.

    I'm trying to create a function that will search the CSV for extra commas and replace them with a "-". The CSV should have 17 columns which means that there should be 16 commas per line.

    This is where I what I have but instr keeps returning a 0 at the last comma. Any ideas on what I'm missing OR is there a better way to do this?
    Code:
    Sub VerifyFormat()
    
        Const ForReading = 1
        Dim fs, f
        Dim str As String
        Dim idx As Integer
        Dim lidx As Integer
        Dim cnt As Integer
        
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.OpenTextFile("C:\myFile.csv", ForReading)
        
        str = f.readline
        
        Do While Not f.AtEndOfStream
        
            idx = 1
            cnt = 0
            idx = InStr(idx, str, ",", vbTextCompare)
            
            While idx <> 0
                idx = InStr(idx + 1, str, ",", vbTextCompare)
                cnt = cnt + 1
            Wend
            
            If cnt = 15 Then
                lidx = idx
            End If
            
            If cnt > 16 Then
                MsgBox str
                MsgBox "Comma Found in Description"
                Replace str, ",", " - ", lidx - 1, -1
            End If
            
            str = f.readline
        Loop
        
        f.Close
                
    End Sub

  2. #2
    Join Date
    Feb 2005
    Posts
    333
    OK now that my head is out of my @$$ I've figured out my indexing problem. Now the question is once I remove the comma from the string, how do I replace the existing string in the file. My goal is to repair the corrupt file and the I'll import it later.

    Code:
    Sub VerifyFormat()
    
        Const ForReading = 1
        Dim fs, f
        Dim str As String
        Dim idx As Integer
        Dim lidx As Integer
        Dim cnt As Integer
        
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.OpenTextFile("C:\myFile.csv")
        
        str = f.readline
        lidx = 1
        Do While Not f.AtEndOfStream
        
            idx = 1
            
            idx = InStr(idx, str, ",", vbTextCompare)
            cnt = 1
            
            While idx <> 0
                idx = InStr(idx + 1, str, ",", vbTextCompare)
                If idx <> 0 Then
                    cnt = cnt + 1
                End If
                
                If cnt = 16 And idx <> 0 Then
                    lidx = idx
                End If
            Wend
            
            If cnt > 16 Then
                MsgBox str
                MsgBox "Comma Found in Description"
                str = Replace(str, ",", " ", lidx + 1, -1)
                MsgBox str
            End If
            
            str = f.readline
        Loop
        
        f.Close
                
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •