Import tab delimited text file with header rows

[Hugh self taught]

Hi All,

Here is a sample portion of my text file. I'm looking for suggestions on how
to import this into a temp table. I need all the info (except the blank lines
Duh..Heh..)

The very first line is the Competition name. I'm sure you can figure out
what the rest is.

Test - Ballroom & Latin Festival - 24/05/08

1. ADULT BEGINNER BALLROOM (W/Q) (final)

94 T KORZENIEVSKI & M FRANK
253 E HEYNS & T GUNTHER
11 J ADAMS & T GELLARD
35 C HOLMES & R MALAN
69 S VAN DER KOLFF & M DE JAGER
70 C VAN DER MERWE & E VAN DER MERWE
115 D THOMPSON & D COWELL

4. YOUTH BRONZE BALLROOM (W/Q) (semi final)

11 J ADAMS & T GELLARD
35 C HOLMES & R MALAN
69 S VAN DER KOLFF & M DE JAGER
70 C VAN DER MERWE & E VAN DER MERWE
115 D THOMPSON & D COWELL
121 J VAN ROOYEN & A DE COSTA

5. ADULT BRONZE BALLROOM (W,Q) (semi final)

33 M HERHOLDT & K BEHMER
36 N JARDIM & M DA SILVA
80 R CAMPBELL & N EASTWOOD

In my file there are other blank tabbed fields not shown here which I can
deal with later.

Any help is greatly appreciated.

 

[Piet Linden]

Hi All,

Here is a sample portion of my text file. I'm looking for suggestions on how
to import this into a temp table. I need all the info (except the blank lines
Duh..Heh..)

The very first line is the Competition name. I'm sure you can figure out
what the rest is.

Test - Ballroom & Latin Festival - 24/05/08                        

1. ADULT BEGINNER BALLROOM (W/Q) (final)                                

94              T KORZENIEVSKI & M FRANK            
253             E HEYNS & T GUNTHER        
11              J ADAMS & T GELLARD        
35              C HOLMES & R MALAN          
69              S VAN DER KOLFF & M DE JAGER                
70              C VAN DER MERWE & E VAN DER MERWE          
115             D THOMPSON & D COWELL             

4. YOUTH BRONZE BALLROOM (W/Q) (semi final)                            

11              J ADAMS & T GELLARD        
35              C HOLMES & R MALAN          
69              S VAN DER KOLFF & M DE JAGER                
70              C VAN DER MERWE & E VAN DER MERWE          
115             D THOMPSON & D COWELL             
121             J VAN ROOYEN & A DE COSTA          

5. ADULT BRONZE BALLROOM (W,Q)  (semi final)                            

33              M HERHOLDT & K BEHMER              
36              N JARDIM & M DA SILVA              
80              R CAMPBELL & N EASTWOOD           

In my file there are other blank tabbed fields not shown here which I can
deal with later.

Any help is greatly appreciated.

Hugh,
I would probably open the text file in code and then read through the
lines one at a time and import I don't think the set-based approach
of using pure SQL will work here, because you have at least two
different kinds of facts in the same "document'/"table". The first
facts are about the competition, "Competition Number" (e.g., "5") and
"Competition Ballroom?" and Competition type "(semi-final)"

Then there are the pairs with <pair number> <member 1> & <member 2>

So, the way I would probably go about it is:
read through the file a line at a time, and use a function to evaluate
the kind of information in the line.
If it's a "Test Type", put it in the "Tests" table.

Rules (derived and may be incorrect...):
If the line starts with a number but does NOT contain an &, then it's
a Test Grouping.
If the line starts with a number and does contain an &, then it's a
Pair.
If the line does not have any numbers at the beginning, it's a Test
Name

I guess in the future, (if you can control it), I would suggest not
storing lots of facts inside text. It's a serious hassle to split
apart... Just one of those lessons you learn the hard way...

 

[Piet Linden]

Hi All,

Here is a sample portion of my text file. I'm looking for suggestions on how
to import this into a temp table. I need all the info (except the blank lines
Duh..Heh..)

The very first line is the Competition name. I'm sure you can figure out
what the rest is.

Test - Ballroom & Latin Festival - 24/05/08                        

1. ADULT BEGINNER BALLROOM (W/Q) (final)                                

94              T KORZENIEVSKI & M FRANK            
253             E HEYNS & T GUNTHER        
11              J ADAMS & T GELLARD        
35              C HOLMES & R MALAN          
69              S VAN DER KOLFF & M DE JAGER                
70              C VAN DER MERWE & E VAN DER MERWE          
115             D THOMPSON & D COWELL             

4. YOUTH BRONZE BALLROOM (W/Q) (semi final)                            

11              J ADAMS & T GELLARD        
35              C HOLMES & R MALAN          
69              S VAN DER KOLFF & M DE JAGER                
70              C VAN DER MERWE & E VAN DER MERWE          
115             D THOMPSON & D COWELL             
121             J VAN ROOYEN & A DE COSTA          

5. ADULT BRONZE BALLROOM (W,Q)  (semi final)                            

33              M HERHOLDT & K BEHMER              
36              N JARDIM & M DA SILVA              
80              R CAMPBELL & N EASTWOOD           

In my file there are other blank tabbed fields not shown here which I can
deal with later.

Any help is greatly appreciated.

I guess I forgot how to read...
What did you want to do with the data? That determines how it should
be parsed...
You could import it directly into a table with two columns, but then
what you need to do with it? (How are you going to summarize it, etc?)

 

[Piet Linden]

this is fun... 3-level hierarchy:
Course: Ballroom & Latin Festival
Section: 1. ADULT BEGINNER BALLROOM (W/Q) (final)
Members:
94 T KORZENIEVSKI & M
FRANK
253 E HEYNS & T GUNTHER
11 J ADAMS & T GELLARD
35 C HOLMES & R MALAN
69 S VAN DER KOLFF & M DE
JAGER
70 C VAN DER MERWE & E VAN DER
MERWE
115 D THOMPSON & D COWELL

I imported the data so that it comes out into 2 columns:
PairOrSection TEXT(255),
PairName TEXT(255).

Then I added two more columns to dump the parsed data into.
Section (TEXT), which should really be a number,
and
PairNumber (TEXT), which should also be a number...

(but I'm just worried about the parsing for now, so everything's
text).

then I ran this:

Public Sub FixData()
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("loDanceData", dbOpenTable)

Do Until rs.EOF
If InStr(1, rs.Fields("PairOrSection"), ".") > 0 Then
rs.Edit
rs.Fields("Section") = CInt(Left(rs.Fields
("PairOrSection"), InStr(1, rs.Fields("PairOrSection"), ".") - 1))
rs.Update
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub

Public Sub CopyDownSection()

Dim strSection As String
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("loDanceData", dbOpenTable)
rs.MoveFirst

If Not IsNull(rs.Fields("Section")) Then
strSection = rs.Fields("Section")
Else
rs.MoveNext
End If

Do Until rs.EOF
If IsNull(rs.Fields("Section")) And strSection <> "" Then
rs.Edit
rs.Fields("Section") = strSection
rs.Update
Else
strSection = rs.Fields("Section")
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Sub

seems to work... test it on a backup, though... don't mess up your
real data if the test doesn't work.

 

[Hugh self taught]

Hi Piet,

Thanks for your responses. To clarify..... this data comes from another
program used by dance competition adjudicators & no matter whether it's in
txt or html, it'll have the same layout. Unfortunately the developer isn't
able at this stage to bring it out in excel format which would make life a
lot easier so I have to deal with what I've got.

My function in this is to assign & keep track of the points each couple has
earned from their placings. So as the sample I used indicates, the very first
line is competition details then a blank line then an event header, another
blank line then the placings in sequence of the couples by number with names
for that event etc etc..

I'll bring this data into temp tables so it won't mess with my current data,
which will be the format forward anyway as once the data is updated to the
current data it's no longer needed. I always make a backup before
experimenting as well.

I've seen samples of line input in the forum somewhere so I'll go look for
them to experiment with.
I think one of my issues is going to be to identify which event the couples
belong to & in the correct sequence. Can you imagine the drama if I gave 5th
place points to the couple who won? Ideally I'd get something that would have
a sequential key field of some sort to identify the "Event" as one column
with the couple's number as the next, the text on the one side of the "&" as
the male & the text on the right side as the female. That way I could do a
lookup in my database table I already have with couples with their numbers to
verify that it's the correct partnership (the couple may have changed
partners) & I'd be able to group correctly.

Hope that all makes sense

 

[Hugh self taught]

Hi Piet,

For me to test the code you've given me, could you also give me the code you
used to get the data imported in the first instance?

I've done some some SQL INSERTs & UPDATEs etc as well as via the external
data import facility from excel & another access db but nothing as complex as
this is turning out to be. Every corner brings me a new challenge & I'm
learning all the time. It's great..!!

 

[Hugh self taught]

Hi Piet,

I've been experimenting with the competition scoring program & found I could
get the data out as follows which may make positioning slighly easier:-

AT HOME BALLROOM & LATIN - 21/02/2009

1. OPEN BEGINNER BALLROOM (W/RFT) FINAL

Result List Top 8 Couples

1 ( 51) D HOPKINS & M BEDDOW
2 ( 43) T REIST & C LAGARDE
3 ( 53)



2. UNDER 16 BRONZE BALLROOM (W/Q) FINAL

Result List Top 8 Couples

1 ( 35) D HATTINGH & S ESTEVES
2 ( 215) C GROOTBOOM & D ROBBERTSE
3 ( 68) A BOTHA & M KLEYNHANS
4 ( 21) M NEL & E KENDALL



3. YOUTH BRONZE BALLROOM (W/Q) FINAL

Result List Top 8 Couples

1 ( 142) A DA SILVA & M DA SILVA
2 ( 119) C MORRISON & S VAN DEVENTER
3 ( 80) A VERSTER & S COOPER
4 ( 216) D THOMSON & K ALEXANDER