Link to home
Start Free TrialLog in
Avatar of DJ P
DJ PFlag 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

Avatar of Arana (G.P.)
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
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.
Open in notepad and do a find replace on quotes.
Avatar of DJ P

ASKER

Not sure where to place your code. No vba expert by any means.
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
Avatar of 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
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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?
Can you share the code where you are adding the commas?


»bp
Avatar of DJ P

ASKER

Bill,

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

ASKER

Thanks Bill,

I'm good to go!
Great!


»bp