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",,,,,,,,,,,,
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 VariantDim x As LongDim y As LongDim strText As StringSet wks = ThisWorkbook.Worksheets("Test")With wksSet rngLastCellCol = FindRange(RangeArg:=.Cells, ByColOrRow:=xlByColumns)If rngLastCellCol Is Nothing Then Exit SubSet rngLastRowCell = FindRange(.Cells)aryVals = .Range(.Cells(1), .Cells(rngLastRowCell.Row, rngLastCellCol.Column))End WithSet FSO = CreateObject("Scripting.FileSystemObject")Set TStream = FSO.CreateTextFile(ThisWorkbook.Path & "\Test.txt", True)For x = 1 To UBound(aryVals, 1)strText = vbNullStringFor y = 1 To UBound(aryVals, 2)'strText = strText & Replace(aryVals(x, y), ",", """,""") & ";"strText = strText & aryVals(x, y) & ";"NextTStream.WriteLine strText ' Left(strText, Len(strText) - 1)' Debug.Print Left(strText, Len(strText) - 1)NextTStream.CloseEnd SubFunction 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 RangeIf FindAfter Is Nothing ThenSet FindAfter = RangeArg(1, 1)End IfSet FindRange = RangeArg.Find(What:=FindWhat, _After:=FindAfter, _LookIn:=LookWhere, _LookAt:=WholeOrPart, _SearchOrder:=ByColOrRow, _SearchDirection:=NextOrPrevious, _MatchCase:=MatchCaseBool)MatchFound = Not FindRange Is NothingEnd Function
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.
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;
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
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.
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