CODEWITHSUNDEEP
excelvba
Rohit
Rohit

Reputation: 21

Remove double quotations using vba

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

Upvotes: 1

Views: 12651

Answers (4)

Rohit
Rohit

Reputation: 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

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

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

Upvotes: 0

GothicAnatomist
GothicAnatomist

Reputation: 156

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), "")

Upvotes: 4

Liaoo
Liaoo

Reputation: 425

Just do a REPLACE()

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

Upvotes: 0

Related Questions