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.

 

[RichardMGreen]

It's a CSV file and it's downloaded from a SFTP site.

I'll try the find and replace on a copy of the file and see how that goes.

::edit::

Solved that particular problem.
I copied the bit I wanted to change (i.e. the extra ," and the square character) and did a find/replace to just leave the square character and all's fine now.
The table in Access is now correct.

 

[Norie]

Richard

Any chance you can upload a section of the file somewhere and provide a link?

How have you tried importing it so far?

PS Just realised what the S in SFTP stands for, so you probably can't upload.

 

[RichardMGreen]

Can't share any part of the file at all unfortunately, I'd probably get sacked.
Now I've done the find/replace I've got the right number of records in all the tables (they are linked tables to keep the database size down, it's already at 300MB).

I just need some way of doing the find/replace automatically until the software vendors sort it out at their end.

Can I automate this via Excel?

 

[Norie]

Richard

How did you do the Find/Replace that worked? Manually?

What you could try is reading the file in, using Replace in VBA.

You can read the file with standard VBA file I/O methods, just like Ruddles did, and that can be done in Excel/Access/Word/PowerPoint.

ie anywhere you can use VBA

 

[RichardMGreen]

Hi Norrie, I did it manually to start with as a test.
Now I know it works I can have a go at doing the find/replace bit, it's the chr(10) for the line feed that's got me a little nervous.

 

[Ruddles]

If it's a straight replacement - a nice neat pair of CR/LF characters in exchange for your two dodgy characters - that can be done by opening the file in binary mode and zapping them in situ.

 

[Norie]

Well I never got the hang of binary mode so I used FSO.

Sub TextFileFindReplace()
Dim strFileName As String
Dim strInputFile As String
Dim strOutputFile As String
Dim FSO As Object ' FileSystemObject
Dim fs As Object ' File
Dim ts As Object ' TextStream
 
    strInputFile = "C:\SFTPFile.txt"
    
    strOutputFile = "C:\Output.txt"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set fs = FSO.GetFile(strInputFile)
      
    Set ts = fs.OpenAsTextStream(ForReading)
  
    strInput = ts.ReadLine
    
    ts.Close
    
    ' replace Chr(10) with empty string
    strOutput = Replace(strInput, Chr(10), "")
    
    ' replace "" with "
    strOutput = Replace(strOutput, Chr(34) & Chr(34), Chr(34))
    
    FSO.CreateTextFile (strOutputFile)
    
    Set fs = FSO.GetFile(strOutputFile)
    
    Set ts = fs.OpenAsTextStream(ForWriting)
    
    ts.Write (strOutput)
    
    ts.Close
    
End Sub

There's probably an easier way to do this in VBA, I'm more used to using FSO elsewhere.

 

[RichardMGreen]

Well, so far I have this:-

Option Explicit
Public Sub ProcessTextFile()
    Dim sFileName As String, sBakFile As String, iPtr As Integer, iFHin As Integer, iFHout As Integer
    Dim sRecord As String, charpoint As Integer
    sFileName = Application.GetOpenFilename(FileFilter:= _
    "CSV (Comma delimited) (*.csv), *.csv, Text (*.txt), *.txt, All files (*.*), *.*")
    If sFileName = "False" Then Exit Sub
    ' 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
    iPtr = InStrRev(sFileName, ".")
    If iPtr = 0 Then
        sBakFile = sFileName & ".bak"
    Else
        sBakFile = Left(sFileName, iPtr - 1) & ".bak"
    End If
    FileCopy sFileName, sBakFile
    Close
    iFHin = FreeFile()
    Open sBakFile For Input As iFHin
    iFHout = FreeFile()
    Open sFileName For Output As iFHout
    ' get the header line and write it out unchanged
    Line Input #iFHin, sRecord
    charpoint = InStr(1, sRecord, Chr(10)) - 1
    Print #iFHout, Left(sRecord, charpoint)
    If Len(sRecord) > 1 Then
        ' if there are at least two characters in the line, remove the last two
        sRecord = Replace(sRecord, "," & Chr(34) & Chr(10), Chr(10), InStr(1, sRecord, Chr(10)))
    End If
    Print #iFHout, Mid(sRecord, charpoint + 1, Len(sRecord) - charpoint - 2)
    Close iFHin
    Close iFHout
    MsgBox "Done!", vbOKOnly + vbInformation
End Sub

The only snag I've found is that the header is being read in as part of the record.
I've managed to sort that bit out, as well as getting a blank row when I look in the Access linked table.
I just need to get rid of a couple of extra line breaks at then end and I'm sorted.