Automate 'Difficult' importing text file
 

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.

Automate 'Difficult' importing text file
 

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" wrote in message
...
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.

Automate 'Difficult' importing text file
 

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" wrote in message
...
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.

Automate 'Difficult' importing text file
 

What about dog names that contain spaces ? e.g. Smokin fast
Are they delimited with " ?

NickHK

MArk" wrote in message
...
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" wrote in message
...
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.

Automate 'Difficult' importing text file
 

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" wrote in message
...
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" wrote in message
...
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.