Remove last character in csv file, Excel vba

Question

I am generating a CSV file from Excel (2013) like this:

.SaveAs Filename:= _
    filePath & MyFileName, _
    FileFormat:=xlCSV, _
    Local:=True, _

That works fine but at the last row I get semicolon in the end as well:

994085;None ApS.;2;G; 12 ;31-12-2015; 40 ; -   ;Operational TB ;

How can I remove the last ; in that file?

I saw it is easy in a string. I am not sure I can put the whole file into string. It is around 6-700K characters. It has to go pretty fast no matter what I implement

Any ideas appreciated!

open the file in one go and read it into `MyData` / `Array`. See [Open the text file in memory](http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-sheet/11267603#11267603) — Siddharth Rout, Jun 10 '15 at 07:30
@SiddharthRout thanks but sometimes the file can get 2-3M characters. Isn't there a fast way to get the last row only? — Pepys, Jun 10 '15 at 07:34
you just can't retrieve the last character directly. Did you try the above method and check how much time is it taking? — Siddharth Rout, Jun 10 '15 at 07:36
In the above code `strData(ubound(strData))` will give you the last row — Siddharth Rout, Jun 10 '15 at 07:40
How about deleting the last character in the excel file just before exporting as .csv, then replacing after the export? — tea_pea, Jun 10 '15 at 08:47
@MissPalmer but there is no semicolons in the excel file in the first place. I just save it as csv format and that adds separator itself (based on the locale settings) — Pepys, Jun 10 '15 at 09:58
@SiddharthRout I tried this Ubound but no success and gave up quickly. Not a big priority task anyway but thanks for the help! — Pepys, Jun 10 '15 at 10:00
Can you post the code that you tried? It is prettry simple actually — Siddharth Rout, Jun 10 '15 at 10:01
You may also want to check what `strData(ubound(strData)-1)` has? — Siddharth Rout, Jun 10 '15 at 10:02
@SiddharthRout so I took "Open the file in text memory" code from your post and replaced the last row with strData() = strData(UBound(strData) - 1) Is that what you mean? I got "cannot assign to array" error — Pepys, Jun 11 '15 at 11:39
no that is not what I meant. In that code, after the line `strData() = Split(MyData, vbCrLf)`, type `msgbox strData(ubound(strData))`. What do you get? If it is not `,` then replace the above line with `Msgbox strData(ubound(strData)-1)`. What do you get in both scenarios? — Siddharth Rout, Jun 11 '15 at 11:41
With -1, I get the whole last row(there is extra blank row) Exactly what I need. Have to figure out how to delete the very last character of it ie. the semicolon :) — Pepys, Jun 11 '15 at 11:47
The original cause of your problem is probably that at the end last line you have a cell with empty string in it. If you remove it (programmatly or manually you could remove the last `;` — Xavier Combelle, Jun 11 '15 at 12:53
@XavierCombelle you have a point. I don't have control over the file though. And changing it programatically is the same as doing it after the generation of the csv — Pepys, Jun 11 '15 at 13:35

score 1 · Answer 1 · answered Aug 04 '15 at 15:56

Open "partnumbers.txt" For Output As #2
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
If Right(xFname$, 1) = "1" Then
  Print #2, Left(xFname$, 13)
  ActiveCell.Offset(xRow) = Left(xFname$, 13)
End If
xRow = xRow + 1
xFname$ = Dir
Loop
End If
Close #2

Basically you could use something like this. Here i am counting 13 characters and deleting the rest from each line in the txt; obviously this code will need to be rearanged to your needs, but you can take a look at the theory of this. Good Luck!

Remove last character in csv file, Excel vba

1 Answers1