We help IT Professionals succeed at work.
Get Started

Removing quotes in a csv file

Last Modified: 2020-02-28
Hello experts. I have an excel (.csv file) that when opening in note pad gives me this (please note these are only a couple of sample records as the file is pretty large.

"Mod.;""Materialtext"";""MM"";""MM-Text de"";""MM-Text en"";""MM-Text fr"";""MMW"";""MMW-Text de"";""MMW-Text en"";""MMW-Text fr"";""SAP"";""EKP""",,,,,,,,,,,,
"01001;""EuroLine Tisch 70x70cm"";""MD_207"";""Tischhöhe"";""table height"";""Hauteur de table"";""064"";""64cm"";""64cm"";""64cm"";""000000000399000540"";-0.57",,,,,,,,,,,,
"01001;""EuroLine Tisch 70x70cm"";""MD_207"";""Tischhöhe"";""table height"";""Hauteur de table"";""070"";""70cm"";""70cm"";""70cm"";""000000000399000540"";0.04",,,,,,,,,,,,

Open in new window

The first record would represent the column headings. What I would like to do is remove all the quotation marks a simple leave the semi colon (;) as my delimiter and export as a text file. Below is my code that produces a text file but the quotes still remain. What am I missing? The code is part of a module in excel if that helps.

Dim aryVals As Variant
Dim x As Long
Dim y As Long
Dim strText As String

Set wks = ThisWorkbook.Worksheets("Test")
With wks
Set rngLastCellCol = FindRange(RangeArg:=.Cells, ByColOrRow:=xlByColumns)
If rngLastCellCol Is Nothing Then Exit Sub
Set rngLastRowCell = FindRange(.Cells)

aryVals = .Range(.Cells(1), .Cells(rngLastRowCell.Row, rngLastCellCol.Column))
End With

Set FSO = CreateObject("Scripting.FileSystemObject")
Set TStream = FSO.CreateTextFile(ThisWorkbook.Path & "\Test.txt", True)

For x = 1 To UBound(aryVals, 1)
strText = vbNullString
For y = 1 To UBound(aryVals, 2)
'strText = strText & Replace(aryVals(x, y), ",", """,""") & ";"
strText = strText & aryVals(x, y) & ";"

TStream.WriteLine strText ' Left(strText, Len(strText) - 1)
' Debug.Print Left(strText, Len(strText) - 1)
End Sub

Function FindRange(RangeArg As Range, _
Optional ByVal FindWhat As Variant = "*", _
Optional FindAfter As Range, _
Optional LookWhere As XlFindLookIn = xlValues, _
Optional WholeOrPart As XlLookAt = xlPart, _
Optional ByColOrRow As XlSearchOrder = xlByRows, _
Optional NextOrPrevious As XlSearchDirection = xlPrevious, _
Optional MatchCaseBool As Boolean = False, _
Optional MatchFound As Boolean = False) As Range

If FindAfter Is Nothing Then
Set FindAfter = RangeArg(1, 1)
End If

Set FindRange = RangeArg.Find(What:=FindWhat, _
After:=FindAfter, _
LookIn:=LookWhere, _
LookAt:=WholeOrPart, _
SearchOrder:=ByColOrRow, _
SearchDirection:=NextOrPrevious, _
MatchFound = Not FindRange Is Nothing
End Function

Open in new window

Watch Question
Test your restores, not your backups...
Expert of the Year 2019
Distinguished Expert 2020
This problem has been solved!
Unlock 1 Answer and 12 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE