Reading a .txt file in Excel VBA

vodkasoda · Jul 5, 2008

Hi, I need a little bit of help here ... I can read and process a .txt file from my Excel VBA program, but I am having trouble as follows :

The file I am reading contains a number of heading lines, followed by a number of data lines.

All of the data lines end in a date, so I can use the code ...

Code:

If IsNumeric(Right(MyDSRec, 1)) Then

... to pick up the data lines & ignore the heading lines. This is fine and works perfectly.

However, the .txt file that I receive is out of my control, and is prone to error, in that the data line occasionally has what I assume is a "Tab" character after the last date, which means that the line above does not recognise the last character as being numeric.

If you double click "06" at the end of each line on the example below, you should be able to see what I mean (in the .txt document, if I place the cursor at the end and press backspace, all characters at the end are deleted, back to the 06, so they are not individual spaces) ...

Code:

RAMSEY11		29/05		03/06		03/06			13/06
SHAKESPEARE 11		02/06		09/06		09/06			18/06	
PREMIER6		03/06		04/06		05/06			18/06

Can somebody please suggest a way around this ?!?

BrianB · Jul 5, 2008

Use a temporary line of code to get the Ascii code of the offending character and use it something like this

Code:

'- PARTIAL CODE
    x = Right(MyDSRec, 1)
    MsgBox (Asc(x))     ' temporary line to get code of last character
    If IsNumeric(x) Or Asc(x) = 9 Then

You will probably need to add code to remove the odd character if present.

xenou · Jul 5, 2008

I think Brian's idea is a good one.

I'm curious though, how are you reading the text file? Are you using the "Open C:file.txt For Input as #1" type of code? I was wondering if you used a TextStream object from the scripting library perhaps it would recognize the end of line marker and ignore it...
maybe.

AB

vodkasoda · Jul 5, 2008

Alexander,

I'm just putting Brian's suggestion to the test ...

Yes, I do as you say :

Code:

Open "E:\DESPATCH SCHEDULES.TXT" For Input As #1

'Process entire Despatch Schedule
Do While Not EOF(1)
    Input #1, MyDSRec

Unfortunately I am self taught at VBA (I'm a COBOL programmer by trade, this is all a*se about face logic to me !!!) and tend to plod through using the methods I've used before, so I have no idea what you mean by "a TextStream object from the scripting library", sorry !!!

Alexander Barnes said:
I think Brian's idea is a good one.

I'm curious though, how are you reading the text file? Are you using the "Open C:file.txt For Input as #1" type of code? I was wondering if you used a TextStream object from the scripting library perhaps it would recognize the end of line marker and ignore it...
maybe.

AB

Kenneth Hobson · Jul 5, 2008

You may need to check what that last character is. You may be capturing the vbcrlf which has 2 codes.

Try:

Code:

Sub test()
  Dim MyDSRec As String
  MyDSRec = Replace("RAMSEY11 29/05 03/06 03/06 13/06", " ", vbTab)
  MsgBox IsNumeric(Right(MyDSRec, 1)), , True
  MyDSRec = MyDSRec + vbTab
  MsgBox IsNumeric(Right(MyDSRec, 1)), , False
  
  'Add tab to end of string.
  MyDSRec = RTrimD(MyDSRec)
  MsgBox IsNumeric(Right(MyDSRec, 1)), , True
End Sub

Function RTrimD(str As String, Optional delim As String = vbTab)
  Do Until (Right(str, 1) <> delim)
    str = Left(str, Len(str) - 1)
  Loop
  RTrimD = str
End Function

vodkasoda · Jul 5, 2008

Brian,

Brilliant, it may or may not be the best way of doing things, but it works and solves my problem, thank you !!!

I coded it as follows :

Code:

MyEndChar = Right(MyDSRec, 1)

If Asc(MyEndChar) = 9 Then
    MyDSRec = Left(MyDSRec, (Len(MyDSRec) - 1))
End If

Thanks again

BrianB said:
Use a temporary line of code to get the Ascii code of the offending character and use it something like this

Code:

'- PARTIAL CODE x = Right(MyDSRec, 1) MsgBox (Asc(x)) ' temporary line to get code of last character If IsNumeric(x) Or Asc(x) = 9 Then

You will probably need to add code to remove the odd character if present.

vodkasoda · Jul 5, 2008

Thanks for getting back Kenneth, that was my first thought, find out what that character is, I tried vbTab & vbcrlf but neither was picked up by an IF statement ... but as you can see, Brian's suggestion worked just fine ...

xenou · Jul 5, 2008

Here's some belated playing around -- since you've got your program working I'm posting more out of my own curiosity!

Let's say you want to read this data and get it onto an excel sheet - my strategy here was to create an array, fill it up, and write it to a sheet. Is there a better way? The array has some zero-length strings corresponding to the extraneous tabs at the end of some lines - I've just collected them and written them to my sheet along with the rest.

Any ideas for a more direct approach? The one thing that had me puzzling was how to append the data from each line of the the text file into my array as a separate element - I've used a temporary array to hold the line after being split, and transferred it to my main array with a simple loop.

AB
(TIA for any replies - no need to either as this is just an exercise!)

Code:

Option Explicit
Sub Test()
Dim a As Variant, b As Variant
Dim ws As Worksheet
Dim lngCounter As Long
Dim x As Long
Dim strTemp

Set ws = Workbooks.Add.Worksheets(1)
Open "C:\TEMP\shakespearetest.txt" For Input As #1
ReDim b(1 To 11, 1 To 1) 'First dimension must be the greatest possible number of columns of data including ending tabs with no values

Do While (EOF(1) = False)
    
    lngCounter = lngCounter + 1
    ReDim Preserve b(1 To 11, 1 To lngCounter)
    
    Line Input #1, strTemp
    a = Split(strTemp, vbTab)
    For x = 0 To UBound(a)
        b(x + 1, lngCounter) = a(x) 'Careful - a is zero-based, b is one-based (for Excel columns)
    Next x
Loop

Close #1

ws.Cells(1, 1).Resize(UBound(b, 2), UBound(b, 1)).Value = WorksheetFunction.Transpose(b)

End Sub

Kenneth Hobson · Jul 5, 2008

So, it was a tab.

You could have used:

Code:

 MyEndChar = Right(MyDSRec, 1)

If MyEndChar = vbtab Then
    MyDSRec = Left(MyDSRec, (Len(MyDSRec) - 1))
End If

Here is a simple way to look at comparisons of that nature.

Code:

Sub t()
  Dim c As String
  c = vbTab
  MsgBox c = vbTab, , "c = vbTab"
  MsgBox Chr(9) = vbTab, , "Chr(9) = vbTab"
  MsgBox Asc(vbTab) = 9, , "Asc(vbTab) = 9"
End Sub

I would recommend that you check your data. There could be a case where more than one field was missing so multiple tabs may be at the end. Getting all scenarios accounted for usually takes a good knowledge of the data.

cheers

vodkasoda · Jul 5, 2008

Thanks Kenneth, but when I checked "if = vbTab" it didn't react ...

As for the data, I have no control over it, it comes from somebody who generates it from, I believe, an old Commodore 64 !!! I have no idea why it has this extra data on the end of some lines but if I mention it, he has "far more important things to worry about", which to be fair is quite right, I am only trying to automate this for my own convenience ...

I have put the code in a loop, so that any amount of extra "special characters" are removed ... just in case !!!

Kenneth Hobson said:
So, it was a tab.

You could have used:

Code:

MyEndChar = Right(MyDSRec, 1) If MyEndChar = vbtab Then MyDSRec = Left(MyDSRec, (Len(MyDSRec) - 1)) End If

Here is a simple way to look at comparisons of that nature.

Code:

Sub t() Dim c As String c = vbTab MsgBox c = vbTab, , "c = vbTab" MsgBox Chr(9) = vbTab, , "Chr(9) = vbTab" MsgBox Asc(vbTab) = 9, , "Asc(vbTab) = 9" End Sub

I would recommend that you check your data. There could be a case where more than one field was missing so multiple tabs may be at the end. Getting all scenarios accounted for usually takes a good knowledge of the data.

cheers

Reading a .txt file in Excel VBA

vodkasoda

Board Regular

BrianB

Well-known Member

xenou

MrExcel MVP

vodkasoda

Board Regular

Kenneth Hobson

Well-known Member

vodkasoda

Board Regular

vodkasoda

Board Regular

xenou

MrExcel MVP

Kenneth Hobson

Well-known Member

vodkasoda

Board Regular

Similar threads

Forum statistics

Share this page

Reading a .txt file in Excel VBA

Board Regular

Well-known Member

MrExcel MVP

Board Regular

Well-known Member

Board Regular

Board Regular

MrExcel MVP

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock