Replacing Delimiters in a csv file 1

[cosfreelance]

Hi All,

i have CSV files being created from a system. these files are stored in a folder on the network. What i am trying to do is pick up each CSV file and convert it to a text. The problem is some of the field values contain commas, and the complete field is enclosed in quotes. if i replace the quotes the comma contained in the field splits the existing field into two. and this causes a problem when printing

eg :

"No.20, My Address1",My Address 2,My Address 3,,,,

"Dear Sir,",,,,,,

i am trying to remove the quotes and any commas that are contained within the quotes, leaving the trailing commas intact.

how do i do this? your advice is appreciated.

regards

 

[EBGreen]

My first question would be why do you need to remove the quotes in the first place?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[cosfreelance]

this is due to the application that handles the printing, it looks for comma seperated fields. if the quotes existed it would appear on the printout.

thanks

 

[EBGreen]

Sounds like a pain. Either way, here is an example that removes ,s that are within "s

Option Explicit

Dim oFSO : Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim strLine
Dim i
Dim strLetter
Dim strLineAfter
Dim bInQuotes

bInQuotes = False
strLineAfter = ""
For Each strLine In Split(oFSO.OpenTextFile("C:\Temp\Foo.csv").ReadAll(), VbCrLf)
For i = 1 To Len(strLine)
strLetter = Mid(strLine, i, 1)
If strLetter = Chr(34) Then
If bInQuotes Then
bInQuotes = False
Else
bInQuotes = True
End If
End If
WScript.Echo bInQuotes & vbTab & strLetter
If bInQuotes And strLetter = "," Then
Else
strLineAfter = strLineAfter & strLetter
End If
Next
WScript.Echo strLineAfter
bInQuotes = False
strLineAfter = ""
Next

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[MakeItSo]

Easiest way:

Import the CSV into MS Access, re-export it as TAB text without text qualifier.
;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[cosfreelance]

Hi EBGreen,

thanks for the sample code.

I have made the modifications to the script that now works with the printing. however, as i have tried to change the code that replaces the comma with a space and i cant seem to get it working. what do i need to change to get it working:

eg:
"No.20,My Address1",,,,,,,,,,

becomes
"No.20My Address1",,,,,,,,,,

thanks in advance


MakeItSo: thanks for the advise. but due to the number of files being processed, importing into accesss and exporting is not possible.

 

[PHV]

If bInQuotes And strLetter = "," Then
strLineAfter = strLineAfter & " "
Else
strLineAfter = strLineAfter & strLetter
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886