VBA Excel convert to text file remove commas

0
votes

I am converting a excel output into .txt and .dat file format, but whenever I convert it to .txt file the remaining spaces or empty columns gives a lot of comma on converted text file.

=IF(A13="","", IF(LEFT(A13,1)="T","", IF('JPMorgan Template New'!A14<>"",'JPMorgan Template New'!A14&'JPMorgan Template New'!B14&'JPMorgan Template New'!C14&'JPMorgan Template New'!D14&'JPMorgan Template New'!E14&'JPMorgan Template New'!F14&'JPMorgan Template New'!G14&" "&'JPMorgan Template New'!H14&" "&'JPMorgan Template New'!I14&'JPMorgan Template New'!J14&" "&'JPMorgan Template New'!K14&" "&'JPMorgan Template New'!L14&'JPMorgan Template New'!M14&" "&'JPMorgan Template New'!N14&'JPMorgan Template New'!O14&'JPMorgan Template New'!P14&" "&'JPMorgan Template New'!Q14&'JPMorgan Template New'!R14&'JPMorgan Template New'!S14,"T"&MIN(ROW(A14:A15))+ROWS(A14)-3&" " & SUM('JPMorgan Template New'!H:H))))

Sub SaveAsTXT()
Dim parts As Variant

parts = Split(ActiveWorkbook.Name, ".")
parts(UBound(parts)) = ".txt"

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & _
    Join(parts, "."), FileFormat:=xlCSV, CreateBackup:=False
End Sub

please see image below.enter image description here

enter image description here

enter image description here

2
vbaexcel
Post your conversion code please – Robin Mackenzie
added conversion code – Vince Osana
@Raunak Thomas sorry for my theme, that is on notepad++ anyway – Vince Osana
notepad screenshot added – Vince Osana
Ok that means excel is not converting it into a csv properly because the input data has some delimiters I guess. Can you tell me how the excel output in the first image is generated – Raunak Thomas

2 Answers

0
votes

I would first split the data with fixed width and afterwards save the file as csv. I guess you do not want to have Juan dela Cruz seperated in different strings.

Here is an example to split text with fixed width for a text with three columns.

Selection.TextToColumns Destination:=Range(myRange), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(7, 1)), TrailingMinusNumbers:= _ True

Another solution would be to add a defined delimeter in your creation formula and use this in the csv export.

0
votes

Try this

https://stackoverflow.com/a/30658557/3625350

Excel trailing comma bug in csv files

I think this may be occurring because your columns to the right are blank but not null (your horizontal scrollbar must be very small). Select all columns to the right of column A and press Ctrl and - (minus) this will delete the data. If that doesn't work out try to trim and remove delimiters using the first link in my answer

Edit: The formulae that you posted is not complete but it seems that a lot of cells are blank because in your if criteria there is "" as an output. When blank cells are converted to a csv it is considered a value because of which each empty cell is delimited by a comma. And I'm assuming that you are simply appending in the end. The steps I suggested on top should fix that