Access and Excel -- HELP!!!

[Guest]

I have an Excel file that contains the following information in columns:
Field Name, Picture (type of data), Start (starting position), Length, End
(ending position)

(for example; 3 EX-Parcel-Number, X(11), 1, 11, 11)

I need to be able to import(?) that into Access (to design the table) so I
can import a text file (which contains 1M+ rows of data) and have it display
properly.

I hope that makes sense. If not, reply and ask any question you need.


THANKS!!!

 

[Guest]

You need to name your fields in Row 1 of the Excel spreadsheet. Names should
be ONE WORD, like "FieldName" rather than "Field Name". Once you've named
each field, make sure there are NO BLANK ROWS anywhere in the spreadsheet. No
"totals" or ANYTHING other than raw columnar data, with a SINGLE ROW OF FIELD
NAMES at the top.

Then simply start Access, then FILE (menu) / GET EXTERNAL DATA / IMPORT, and
follow the dialog. You dan directly import from an Excel spreadsheet. Pretty
simple once you've done it a couple times.

 

[Guest]

I have tried that already, but the data is in a text file. The raw data from
the text file looks like this:

0010331100720012003130200207200220022311151105030N0200010YYNNNNN0000000700000300000205000000000000000000000N000000000000010N10022065Y10011400N00000000N0000001005971005970210000000000000000000000000NN000000100000000210063700000000NN00N000
000000000000000 000000000000000 000000000000000 000000000000000
000000000000000 000000000000000 000000000000000 000000000000000
000000000000000 000000000000000 000000000000000 000000000000000
000000000000000 000000000000000 000000000000
00000000000000234432R200112000000000 000000000000000 000000200308090003

It's just a long string of numbers and letters. The start/length/end values
tell the database where to cut the data. I don't know how to get those
values into the database (without going and manually entering them). I
wouldn't mind entering them manually, but I have over 400 fields (total, in 5
different databases) to put in.

Hope this clarifies a little...

 

[Guest]

I have tried that and it doesn't work unfortunately. The data I need to
import into the tables is in a text file. An example of a row in the text
file:

0010331100720012003130200207200220022311151105030N0200010YYNNNNN0000000700000300000205000000000000000000000N000000000000010N10022065Y10011400N00000000N0000001005971005970210000000000000000000000000NN000000100000000210063700000000NN00N000
000000000000000 000000000000000 000000000000000 000000000000000
000000000000000 000000000000000 000000000000000 000000000000000
000000000000000 000000000000000 000000000000000 000000000000000
000000000000000 000000000000000 000000000000
00000000000000234432R200112000000000 000000000000000 000000200308090003

As you can see, it's just a long string of numbers and letters. The
start/length/end fields have to be put in there to tell it where to chop the
data. There are about 300 fields that need to be defined, so it would be
unbelievably time consuming to edit the fields by hand, one by one.

Hope this helps to clarify.

 

[Guest]

Okay, so you have a flat-file delimited by field length (as opposed to using
a delimited such as a comma. Are all the records the same length?

 

[Guest]

Yes, they are all the same.

Okay, so you have a flat-file delimited by field length (as opposed to using
a delimited such as a comma. Are all the records the same length?

 

[Guest]

Also, are you saying that each record (row) of your flat-file has THREE
HUNDRED sub-feilds i it?

No matter what, I don't see any way around some serious coding. I now
understand that your
spreadsheet is the metadata describing the record layout. But even if you
coded VBA code
for in order to use that, you'd still have to relate each feild coming in to
a field in a table.
Also, IIRC Access has a hard limit of 255 columns in a single table, so if
you have 300,
you'd have to break it up into multiple tables anyway.

 

[Guest]

No, there are 5 databases I am going to have to build totalling 300 or so
fields. The biggest has 239 fields and there are a a couple with 26 fields,
one with 56, and another with 47.

 

[Guest]

Well, you can certainly import the meta-data and use that to parse the
incoming flat-file. But you'll have to code the actual "puts" to the table
fields. I see no way around it. even if you used the automation available in
the Access Import function, you still have to manually setup the record
layout. Any way you look at it, it's gonna get ugly.

I'd open the flat-file in VBA, and use the imported meta-data to parse the
incoming file, then ADO writes to add each record. Why? So I could
single-step through during development and see what was actually happening,
one line at a time.

The positive is that you only have to slog through it once.

Dennis