Remove double quotations using vba

Question

I have a text file which is as below

"asdf","dfgv","asd","sdgs"
"sfgf","xcbvcvb","gsfgf","sdvxc"
"asfdfg","fbgdfg","sdfsd","fdg"
"bdg","sdf","fgfdg","fcg"
"sdf","fbcx","ckvknm","fklf"
"xv","asds","r","cxv"

But I want the output which looks like this

asdf,dfgv,asd,sdgs
sfgf,xcbvcvb,gsfgf,sdvxc
asfdfg,fbgdfg,sdfsd,fdg
bdg,sdf,fgfdg,fcg
sdf,fbcx,ckvknm,fklf
xv,asds,r,cxv

I have gone through the below link and changed the code from Write #1 to Print #1. But the problem is, I want this file to be bulk inserted to sql server. Hence, using a print doesn't help me. Remove double quotation from write statement in vba

In almost any text editor you can Search and replace by pressing Ctrl+H, set it to find " and leave replace empty. This is faster then making a code, unless it's multiple files you need to do it. — T. Nesset, Jan 29 '18 at 08:23

score 3 · Answer 1 · answered Jan 29 '18 at 06:35

3

You can use the Replace function to strip out all the quote characters:

result = Replace(original_string, string_you_want_to_remove, replace_with )

example = Replace(original_string , CHR(34), "")

answered Jan 29 '18 at 06:35

GothicAnatomist

146
4
15

score 1 · Answer 2 · answered Jan 29 '18 at 08:21

Thanks everyone, but I have found another way of removing double quotes by using the batch script which is as below.

set objRe = new RegExp
objRE.Pattern = "\"""
objRE.Global  = True
strFileName = "Source.txt"
set objFS = CreateObject("Scripting.FileSystemObject")
set objTS = objFS.OpenTextFile(strFileName)
strFileContents = objTS.ReadAll
objTS.Close
strNewContents = objRE.replace(strFileContents,"")
set objWS = objFS.CreateTextFile("Results.txt")
objWS.Write StrNewContents
objWS.close

score 0 · Answer 3 · answered Jan 29 '18 at 06:33

0

Just do a REPLACE()

Replace(<SourceString>, """, "")

answered Jan 29 '18 at 06:33

Liaoo

425
6
21

score 0 · Answer 4 · answered Jan 29 '18 at 07:06

Try this code:

Sub CleanFile()
Dim fileName, FSO, rfile, wfile As Variant
Dim line As String, dotAt As Integer, newFileName As String

fileName = Application.GetOpenFilename(, , "Load Excel File", , False)

If fileName = "False" Then
    MsgBox "No file chosen"
    Exit Sub
End If

dotAt = InStrRev(fileName, ".")
newFileName = Left(fileName, dotAt - 1) + " - CLEAN.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set rfile = FSO.OpenTextFile(fileName, 1) 'connection for reading
Set wfile = FSO.OpenTextFile(newFileName, 8, True, -1) 'connection for writing

Do Until rfile.AtEndOfStream
    line = rfile.ReadLine
    wfile.WriteLine Replace(line, """", "")
Loop
End Sub

Remove double quotations using vba

4 Answers4