Removing last two characters in a line

[RichardMGreen]

Hi all

I've got a file (CSV) that has extra characters in it. These are only visible if you open the file in WordPad.

Is it possible to open the file using WordPad, remove the last two characters from every line (except the header which is line 1) and then resave the file?

All help greatly appreciated.

 

[Ruddles]

No, but it's a trivial exercise to do it using VBA.

Would you like me to provide some code?

 

[RichardMGreen]

That would be brilliant if you could.

 

[Ruddles]

Bearing in mind these things sometimes take a couple of attempts to get right, try this against a copy of your CSV file - i.e. make a copy of it and run the code against that. It will prompt you to navigate your way to the file and then it makes a .BAK copy of it 'just in case'.

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub ProcessTextFile()
 
  Dim sFileName As String
  Dim sBakFile As String
  Dim iPtr As Integer
  Dim iFHin As Integer
  Dim iFHout As Integer
  Dim sRecord As String
  Dim iRecords As Long
  Dim iChanged As Long
  
  ChDrive Left(ThisWorkbook.Path, 2)
  ChDir Mid(ThisWorkbook.Path, 3)
  sFileName = Application.GetOpenFilename(FileFilter:= _
              "CSV (Comma delimited) (*.csv), *.csv, Text (*.txt), *.txt, All files (*.*), *.*")
  If sFileName = "False" Then Exit Sub
  
[COLOR=green]  ' make a filename based on the name of the input file but with the extension BAK: we will use
  ' this as our input file, so if anything goes wrong the user still has a copy of his original
  ' file intact and unmodified
[/COLOR]  iPtr = InStrRev(sFileName, ".")
  If iPtr = 0 Then
    sBakFile = sFileName & ".bak"
  Else
    sBakFile = Left(sFileName, iPtr - 1) & ".bak"
  End If
  FileCopy sFileName, sBakFile
  
  iRecords = 0
  iChanged = 0
  
  Close
  iFHin = FreeFile()
  Open sBakFile For Input As iFHin
  iFHout = FreeFile()
  Open sFileName For Output As iFHout
  
[COLOR=green]  ' get the header line and write it out unchanged
[/COLOR]  Line Input #iFHin, sRecord
  Print #iFHout, sRecord
  
  Do Until EOF(iFHin)
    Line Input #iFHin, sRecord
    If Len(sRecord) > 1 Then
[COLOR=green]      ' if there are at least two characters in the line, remove the last two
[/COLOR]      sRecord = Left(sRecord, Len(sRecord) - 2)
      iChanged = iChanged + 1
    End If
    Print #iFHout, sRecord
    iRecords = iRecords + 1
  Loop
  
  Close iFHin
  Close iFHout
  
  MsgBox "Done!" & Space(10) & vbCrLf & vbCrLf _
       & "Header plus " & Format(iRecords, "#,##0") & " records read" _
       & Space(10) & vbCrLf & vbCrLf _
       & Format(iChanged, "#,##0") & " records changed" & Space(10), _
       vbOKOnly + vbInformation
 
End Sub[/FONT]

 

[RichardMGreen]

Hi Ruddles

I've just tested your code and it doesn't update anything. It's hittin EOF as soon as the headers are written.
I think it's because the file is missing line breaks due to the last two characters being in place.

Is there any way of updating the code to take account of missing line breaks?

 

[Ruddles]

I think need to know the exact structure of the file. When you said "remove the last two characters from every line", that suggested there was more than one line in it. What are these "two characters"? Are they in place of the normal carriage-return/line-feed?

Can you provide a hex dump of the file? Or send me the file itself?

(I shall PM you my email addy JIC.)

 

[RichardMGreen]

The last two characters are always a comma and a set of speech marks (i.e. ,").

It looks like these are in place of the normal carriage returns/line feeds.
Unfortunately, just to make life harder, every field has a set of speech around it so I can't just do a find/replace to remove them unless you can think of some way around it.

 

[Norie]

Richard

Perhaps these characters are the line breaks?

Are you trying to import this code into Excel?

By the way, why do the quotes (speech marks) prevent a find and replace?

 

[RichardMGreen]

@Norie
Each field is surrounded by speech marks, so you get:-
"field1","field2","field3"

the file at the moment looks like this:-
"field1","field2","field3",""field1 of next line","field2 of next line","field3 of next line","
and so on.
There are strange characters in each record (it appears as a small square in Notepad but doesn't appear in Wordpad) but there's very little I can recognise as a line break.

The information is to be imported into Access into a linked table.

How would I remove the extra ," from each line (highlighted in red above)?

::edit::

I've just imported one of the wierd characters into Excel by pasting it directly into the formula bar instead of inst a cell and it's character code 10 which, from Google, is a line break.

It looks like it's just the extra ," that's causing the issue here.

 

[Norie]

Richard

To replace that you could replace ,"" with ,".

Or even just "" with " (double-quote with single quote).

I don't know if that will help with the 'hidden' character though.

Where does the data actually come from originally and what is it you are trying to import, eg a text file?