See what you get from this. based on a certain amount of guesswork on the
original data file format.
As you (apparently) have location and dog names that can have spaces in
them, without them being delimited between "", it slightly complicates the
reading, but not much.
Private Sub CommandButton1_Click()
Dim FileNum As Long
Dim InData() As String
Dim i As Long
Dim TempData As Variant
Dim LastElement As Long
Dim j As Long
Dim EntryCount As Long
Dim StartRange As Range
Dim RaceData(1 To 5) As Variant
'1 - Location
'2 - Race Number at that location
'3 - Dog Number
'4 - Dog Name
'5 - Price
Const Location As Long = 1
Const LocationRaceNumber As Long = 2
Const DogNumber As Long = 3
Const DogName As Long = 4
Const DogPrice As Long = 5
Const FileName As String = "C:\DogRace.dat"
Const Delim As String = " "
Const RaceText As String = "Race"
Set StartRange = Worksheets(2).Range("A1")
FileNum = FreeFile
'Clear old data
Erase InData
Open FileName For Input As FileNum
InData = Split(Input(LOF(FileNum), #FileNum), vbNewLine)
Close FileNum
For i = LBound(InData) To UBound(InData)
TempData = Split(InData(i), Delim)
LastElement = UBound(TempData)
If InStr(1, TempData(LastElement - 1), RaceText) > 0 Then
'Location data
Erase RaceData
For j = LBound(TempData) + 1 To LastElement - 2
RaceData(Location) = RaceData(Location) & " " & TempData(j)
Next
RaceData(Location) = Trim(RaceData(Location))
RaceData(LocationRaceNumber) = TempData(LastElement)
Else
'Dog Data
If IsNumeric(TempData(0)) = True Then
EntryCount = EntryCount + 1
RaceData(DogNumber) = TempData(0)
RaceData(DogName) = ""
For j = LBound(TempData) + 1 To LastElement - 1
RaceData(DogName) = RaceData(DogName) & " " & TempData(j)
Next
RaceData(DogName) = Trim(RaceData(DogName))
RaceData(DogPrice) = TempData(LastElement)
StartRange.Offset(EntryCount, 0).Resize(1, DogPrice).Value =
RaceData
End If
End If
Next
End Sub
NickHK
"MArk" <(E-Mail Removed)> wrote in message
news:59636C2E-52FD-4DA9-8357-(E-Mail Removed)...
> Thanks for reply Nick
> It comes in Spaces, and there is a blank line between them.I have to use
the
> import wizard to line the columns up.
>
> Field1 Field2 Field3
> 19: 57 The Meadows Race 1
> No. Dog Price
> 1 Fido 3.50
> 2 Brutus 21.50
> 3 Smokin fast 16.00
>
> So thats how it comes out in excel.
> I could do a simple left of cell B2 to just get 'the meadows' but problem
> is sometimes the No. of runners is not consistent.
>
>
> "NickHK" wrote:
>
> > How are the columns separated ?
> > Spaces, Tabs
> > Or fixed width ?
> >
> > i.e. How does the data appear when open in Excel ?
> >
> > And, is there really a blank line between race data ?
> >
> > NickHK
> >
> > "MArk" <(E-Mail Removed)> wrote in message
> > news:CBFC1BA3-E24E-45C6-8C30-(E-Mail Removed)...
> > > Hi guys
> > > Trying to import a txt file that comes out like this:
> > > ------------------------------------------------
> > > 19:57 The Meadows Race 1
> > > No. Dog Price
> > > 1 Fido 3.50
> > > 2 Brutus 21.50
> > > 3 Smokin fast 16.00
> > >
> > > 19:57 The Meadows Race 2
> > > No. Dog Price
> > > 1 Fido1 3.50
> > > 2 Brutus1 21.50
> > > 3 Smokin fast1 16.00
> > >
> > > -------------------------------------------------
> > >
> > >
> > > and convert like this:
> > >
> > > The Meadows 1 1 Fido 3.50
> > > The Meadows 1 2 Brutus 21.50
> > > The Meadows 1 3 Smokin fast 16.00
> > > and so....
> > >
> > > in otherwords put the header of each dog race into a column field.
> > > I can do it excel with about 2000 lines of if statements but can i do
it
> > in
> > > access easier as i need to automate it as i have todo thousands of
them.
> > >
> > >
> > >
> >
> >
> >
|
Similar Threads
