Avatar of DJ P
DJ P
Flag for United States of America asked on

Removing quotes in a csv file

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) & ";"

Next
TStream.WriteLine strText ' Left(strText, Len(strText) - 1)
' Debug.Print Left(strText, Len(strText) - 1)
Next
TStream.Close
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, _
MatchCase:=MatchCaseBool)
MatchFound = Not FindRange Is Nothing
End Function

Open in new window

VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
Arana (G.P.)

You can avoid the quotes if you get the value directly from the range (not the array)
xSep=","
    For Each xRow In xRg.Rows
        xStr = ""
        For Each xCell In xRow.Cells
            xStr = xStr & xCell.Value & ","
        Next
        While Right(xStr, 1) = xSep
            xStr = Left(xStr, Len(xStr) - 1)
        Wend
    debug.print xStr
    Next
aikimark

The CSV is malformed.

"Mod.;""Materialtext""
should be

"Mod.";""Materialtext""

Aside from this, you will need to replace consecutive quote character sequences into single quote character sequences.  I would do that clean-up with VBA code and write the results back to a file before you read it into Excel.
therealmongoose

Open in notepad and do a find replace on quotes.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
DJ P

ASKER
Not sure where to place your code. No vba expert by any means.
Bill Prew

Can you upload your test workbook?  I ran your VBA code on a test sheet I created here (with just the data values from your question, not the extra quotes or semi colons, and it produced the output below in the TXT file, which I think is what you wanted.

So I'm wondering if your actual data in the work sheet has double quotes in the cells?  If not it seems like your code does what you want already?

Mod.;Materialtext;MM;MM-Text de;MM-Text en;MM-Text fr;MMW;MMW-Text de;MMW-Text en;MMW-Text fr;SAP;EKP;
1001;EuroLine Tisch 70x70cm;MD_207;Tischhöhe;table height;Hauteur de table;64;64cm;64cm;64cm;399000540;-0.57;
1001;EuroLine Tisch 70x70cm;MD_207;Tischhöhe;table height;Hauteur de table;70;70cm;70cm;70cm;399000540;0.04;

Open in new window


»bp
DJ P

ASKER
Sure,

Attached is my test file (excel sheet) and results of the file that gets created using same three test records. As you can see quotes are stil lthere.
Test1.xlsm
Test.txt
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Bill Prew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
DJ P

ASKER
Bill,

Thanks! With the update line you provided it also looks like there are some commas down further in my file that I don't need either. What is the proper syntax to include in the code you provided to remove both quotes and commas?
Bill Prew

Can you share the code where you are adding the commas?


»bp
DJ P

ASKER
Bill,

Let me double check and make sure I'm providing you with the proper info.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
DJ P

ASKER
Thanks Bill,

I'm good to go!
Bill Prew

Great!


»bp