Re: Find/Replace in csv file - using VB

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Robert,

Thank you so much for replying so quickly. I'm going out of my mind.

Where would I put the code you gave me? Before I was putting the code I was
trying to use in the "On Click" event of a button I added to one of my forms.

I feel like an idiot. Please be patient with me.

--
RJF


"Robert Morley" wrote:

Woops...I really buggered up that code. Please ignore my previous code
sample (which may appear to work if you don't look too closely, but it
really really doesn't!) and use this one instead:

Public Sub FixCSV(ByVal strFileName As String)
Dim strLine As String
Dim strLineBreak As String
Dim strWholeFile As String
Dim varLines As Variant

Dim i As Long
Dim intFileNum As Integer


'Always use FreeFile() instead of a fixed file number.
intFileNum = FreeFile()

'Get entire file in one shot.
Open strFileName For Binary Access Read As #intFileNum
strWholeFile = String$(LOF(intFileNum), 0)
Get #intFileNum, , strWholeFile
Close intFileNum

'Some CSV's use LF only, others use CR/LF; figure out which.
If InStr(1, strWholeFile, vbCrLf, vbBinaryCompare) > 0 Then
strLineBreak = vbCrLf
Else
strLineBreak = vbLf
End If

'Split it into lines.
varLines = Split(strWholeFile, strLineBreak)

'For each physical line, count number of quotes and figure out if it's
inside a field or not.
strWholeFile = vbNullString
strLine = vbNullString
For i = LBound(varLines) To UBound(varLines)
strLine = strLine & varLines(i)
If UBound(Split(strLine, Chr$(34))) Mod 2 = 0 Then
strWholeFile = strWholeFile & strLine & strLineBreak
strLine = vbNullString
End If
Next

Open strFileName For Binary Access Write As #intFileNum
Put #intFileNum, , strWholeFile
Close intFileNum
End Sub

.

Quantcast