importing data to access 2000 for a database

[Amy]

I am trying to download the FAA database of aircraft
registries into a database to create mailing labels. This
file is 95,781 KB and is comma delimited--but I have no
file extension to know what kind of file this is (yes,
call me dumb but I cannot find any type of file). I am
not really familiar with Access-I am more familiar with
Works and Excel but neither of these programs will open
the entire file. So--I am trying Access. I have read up
on how to import data and have tried all methods. I first
create a new data base 'using blank access database', then
I save it with a name, then I 'create table by entering
data', then I go to "File" "get external data" and have
tried using both options there and that is where my
process ends--I have tried to use all of the "type of
file" but get no where. The file is c:\unzipped\AR072004
\Master and that is all that I can find. Any hints?

 

[Ken Snell]

Try adding ".txt" (without the quotes) to the end of the unzipped filename.
Then try to import into ACCESS.

 

[Amy]

Thank you Ken Snell!
Yeap--call me dumb-a simple solution and I had been
looking for a bigger problem. Thanks!
I was able to get a lot of records but it did give me
a "type conversion" error on a bunch of records though--
any hints there?

 

[Ken Snell]

Just means that the data in those fields in the text file do not match what
ACCESS expects for the field in the table to which the data are being
written. You'll need to look at the error file that is generated to see
which data fields they are and that will suggest how to change the table
(are you using an existing table or letting ACCESS create a table?).

 

[Carol]

CREATE A SHELL TABLE IN WHICH TO IMPORT EVERYTHING.

then set all the datataypes to text or even memo.

Then you can import, then later try to find the appropriate datatype

 

[Amy]

I am letting Access create a table. Should I not be doing
that? Does this mean--when I come to the "database"
window that I click on "create table by entering data"?
When Access does import the info--it shows up in it's own
table leaving the created table blank.

Should I "import" or "link tables"? I will be doing this
whole process again with an updated database on another
computer.

The error file just says "Type Conversion Failure" and
lists "field 2" or "field 3" (occasionally more field
numbers) with the last column being a row number. Total
number of records with errors 209027. I was able to get
355359 valid records in the non-error database.

Thanks!

 

[Amy]

To create a shell table--when I come to the "database"
window--choose "Create table by entering data" or one of
the other two options? I am not sure exactly how to do
that.
Thanks for your time

-----Original Message-----
CREATE A SHELL TABLE IN WHICH TO IMPORT EVERYTHING.

then set all the datataypes to text or even memo.

Then you can import, then later try to find the

appropriate datatype

 

[Ken Snell]

With regard to the error file, you need to look at the record in the text
file that ACCESS says is erroring and see what the data are in that field.
That's the only way you can determine what the conversion problem is.

Creating your own table means that, unless you've told ACCESS the data
formats for each field being imported (this can be done via the Wizard),
ACCESS will guess what the format should be...and often, as you're finding,
it guesses incorrectly.

Creating and keeping an empty table for receiving the imported records is a
good thing to do..you can set the format of the fields directly in the
table's design. Easiest way I do this is to import the data into a new
table, then delete the data from the table and then edit the table's fields
to the desired formats. Then I reuse that table to receive data, and use
append query to copy data into a permanent table, and then delete the data
from the initial table so that it's empty for the next import.

 

[Amy]

Thanks for your help Ken.
I will keep playing with it.
Amy