Importing records with multiple field occurrences

[Walter Cohen]

Hello.
I have some data that I want to import into Access and want to know the best
way to set up the table definition.

The data is in text format.
The first three fields of each record are unique (although I'd want to have
the first field as a key).
Each record then has 30 occurrences of 5 fields.
I am using the import wizard to bring in the data and it prompts me to
identify each field along with a name, type, length,
For each of the 30 occurrences (of 5 fields) do I need to choose a unique
name for each field and also explicitly figure out the start/length of each
field?
Is there an easier way?

Example:
0001 DOG 225 A B C D E F G H I J K L M N O P Q R S T ......etc

Does the table definition have to be something like:
NAME START LENGTH TYPE
number 1 04 Text
animal 5 04 Text
total 9 04 Text
A1 13 02 Text
B1 15 02 Text
C1 17 02 Text
D1 19 02 Text
E1 21 02 Text
A2 23 02 Text
B2 25 02 Text
C2 27 02 Text
D2 29 02 Text
E2 31 02 Text
A3 33 02 Text
....

Thanks,
Walter

 

[Ernie]

I'm new at this but I would recommend something along the
lines of two tables. The first one having the

number
animal
total

and the second one having the "30 occurances of 5 fields
each"

number2 (primary key autonumber)
number (foriegn key from first table)
field1
field2
field3
field4
field5



This will then allow you to have 30 "records" and only
name 5 fields. (I don't recommend using the names that I
gave as above though.)

 

[John Nurick]

Hi Walter,

What you're proposing is the obvious way, and probably the simplest if
you plan to import this 153-field fixed width file into a 153-field
Access table. But that's neither desirable nor necessary: it should be
split into (probably) two tables, something like this:

tblAnimals
number (I assume this is the primary key)
animal
total

tblData
number (foreign key into tblAnimals)
instance (identifying one of the 30 occurrences of 5 fields)
fldA
fldB
fldC
fldD
fldE
..

How much you can simplify things depends entirely on how comfortable you
are with text files and code that modifies them. For instance, instead
of paistakingly creating an import specification to import the wide file
to a wide table you can use SCHEMA.INI. This is a text file that
contains the specification of the file to import; and because it's a
text file one can generate the repetitive bits with a script or even by
cutting and pasting. There's some information in Help but more here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090

http://www.devx.com/tips/Tip/12566

Another approach is to import only a few fields at a time. Set up two
normalised tables as suggested above. Then define an import
specification just for tblAnimals and import the data there.

Next, define an import specification that
includes the number field
skips x characters to the next "A" field
defines one group A .. E

Import that data to tblData. Then modify the specification so it skips
the group of fields you've just imported. Import that data; repeat 28
times.

Or - if you enjoy munging text files - it's possible to write a little
program in your favourite scripting language that will convert the wide
fixed-width file into two narrow tab-delimited ones that Access can
import without difficulty.