We help IT Professionals succeed at work.

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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Top Expert 2014

Commented:
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.
CERTIFIED EXPERT

Commented:
Open in notepad and do a find replace on quotes.

Author

Commented:
Not sure where to place your code. No vba expert by any means.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

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

Author

Commented:
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
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
Why not just remove the quotes before writing the text, using this adjustment:

strText = strText & Replace(aryVals(x, y), """", "") & ";"

Open in new window


»bp

Author

Commented:
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 PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

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


»bp

Author

Commented:
Bill,

Let me double check and make sure I'm providing you with the proper info.

Author

Commented:
Thanks Bill,

I'm good to go!
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Great!


»bp