Parsing CSV file

[Henc]

Hello,

I have a strange CSV file which I am trying to parse using the code below. However, it only works on one line files. The file I really want to parse is 8 to 10Mb. The reason behind this is that there are instances in which a quotation mark is used to denote inches and the text delimiter is also a quotation mark. For instance, the following:

"ITM",0255,"DIG DOWN & PROOF "LIVE FIBER""

should turn out to be:

"ITM",0255,"DIG DOWN & PROOF ''LINE FIBER''"

Is there a better way to do this?

Dim filesys, text, readfile
Dim contents As String
Dim character1, character2, character3 As String
Dim a As Integer
Set filesys = CreateObject("Scripting.FileSystemObject&quot

Set readfile = filesys.OpenTextFile("c:\InputFile.csv", 1, False)
contents = readfile.ReadAll
readfile.Close

Set text = filesys.CreateTextFile("c:\OutputFile.csv&quot
For a = 2 To Len(contents) - 1
character1 = Mid(contents, a - 1, 1)
character2 = Mid(contents, a, 1)
character3 = Mid(contents, a + 1, 1)
If character2 = Chr(34) Then
If (character1 <> Chr(44) And character3 <> Chr(44)) Then
text.write Chr(39) & Chr(39)
Else
text.write character2
End If
Else
If a = 2 Then text.write character1
text.write character2
End If
Next

text.write character3

text.Close


Thanks in advance,
Henc

 

[MichaelRed]

This has been recently discussed in these forums. I would suggest using search to find related threads.

One approach would be to determine if the quotes are in pairs (an even # of them), it not, find the one dollowing a numeric type and convert (e,g, REPLACE) is with either nothing or perhaps Chr(39).

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over