MS Access How to Import Text Document such as CSV File into Table and get Access to Accept a Short Text For a Autonumber Field

I am trying to import a CSV file into MS Access and on all the AutoNumber Fields it denies it due to policy error.

I have a SKUs table and I am importing records into it. One field in the SKUs table is called ManuID. It is a field for a Manufactures name like GENERAL ELECTRIC.

I have a Manufactures table called Manufactures. General Electric is AutoNumber / ManuID 620.  

 I have 1000s of records to import. How can I do this. Thanks.

Dustin StanleyEntrepreneurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric ShermanAccountant/DeveloperCommented:
AutoNumber field = Numeric not Text.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dustin StanleyEntrepreneurAuthor Commented:
Sorry but can you explain how to use this code. Thanks
Eric ShermanAccountant/DeveloperCommented:
It is not code Dustin ... I am saying an AutoNumber field should be Numeric and should not contain Text.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dustin StanleyEntrepreneurAuthor Commented:
That's honestly what I thought but what do I know.

But there has to be a way!

I have a CSV File and it has thousands of products and all of them products have a manufacture name.

I need to import them into Access and auto match the Manufacture short text to the correct corresponding ManuID/AutoNumber in the Manufactures Table.

There has to be a way I know! Well not yet LOL!
An autonumber is what is known as a surrogate key.  The autonumber is generated in YOUR application and is not know by the application which is the source of the input file so you can NEVER match on YOUR autonumber to some input from another system.

Perhaps you are using the wrong terms in your problem description.  It is possible to import text fields into numeric fields provided the text fields actually contain ONLY numbers.  But autonumbers are different.
Eric ShermanAccountant/DeveloperCommented:
Yes, to be honest ... I am a bit confused as to exactly what you are asking.  Can explain in detail a little further maybe with pictures of the CSV data showing the field in question as well as the table/field you are trying to import into.

Dustin StanleyEntrepreneurAuthor Commented:
Ok I think my best bet here is to make my MANUFACTURENM my Natural Primary Key. So I can then import the data correctly.  Basically I have a CSV file and it has the fields let say. Product Name, Manufacture, MPN, Quantity... A record would look like this

GE Camera Black, General Electric, XS50567, 12

I have a Table Named Manufactures and General Electric is 620 AutoNumber ManufacturesID PK.

So I would have to import it into access like this.

GE Camera Black, 620, XS50567, 12

I am trying to find out a way I could get Access to recognize General Electric as Autonumber Manufacture ID 620

I think the Natural key Unique No Duplicates would be best.  I have thousands of items so converting them to Key Autonumbers before import would be a challenge.
Using an external field over which you have no control can be a problem as a primary key.  I would use an autonumber as the PK and I would make a unique index on the natural key.  When you append data from the external file, do not map anything to the autonumber column.  The autonumber is generated by Access as each new record is added.  It is not normal (although it is possible) to provide it in an append query.  The only time an append query would ever include the autonumber is if you are doing a conversion and you want to maintain the original numeric key values so that you don't have to worry about fixing up the foreign keys in the dependent tables.
Dustin StanleyEntrepreneurAuthor Commented:
I would use an autonumber as the PK and I would make a unique index on the natural key.  

This is how I am currently set up.

When you append data from the external file, do not map anything to the autonumber column.

Are you saying just don't import this info??

If so I HAVE to have this info and manually putting in 1000s of these would be horrible.
Autonumbers are AUTOMATICALLY GENERATED.  You DO NOT enter them.  The database engine creates them as each new row is added.
Dustin StanleyEntrepreneurAuthor Commented:
Yes I understand and that is how I am setup currently.

ManufactureID      /       ManufactureName

1                                             General Electric
2                                            3m
3                                             Allen Bradley
4                                               Dell
I feel like we are talking in circles.  

Is ManufactureID in the input file?  Is it ALWAYS numeric?  Is it UNIQUE?  If the answer to all three questions is yes, then you can append the value to an autonumber field.  

HOWEVER, if the ID is always in the input file and this is not just a conversion issue, then it is technically NOT an autonumber and you should change the data type to long integer.  Autonumber is a specialized data type and this field seems to not conform to the rules.
Dustin StanleyEntrepreneurAuthor Commented:
I feel like we are talking in circles.

I apologize and sometimes feel the same.

Is ManufactureID in the input file?
If you mean where I am importing to (Access) then Yes it is ALWAYS NUMERIC UNIQUE AUTONUMBER.

then you can append the value to an autonumber field.

This is the part I am unaware of and exactly how to. Or if it is even what I need.

I am going to DO MY BEST here at explaining everything step for step.

I have a previous application software on my computer (lets call it "OLDWARE") that I used for keeping track of information (Database).

OLDWARE is not an Access Database.

OLDWARE can export CSV files.

when I export a CSV file from OLDWARE the field for Manufacture is in Text and it says the text name of the Manufacture eg...General Electric...

I have created my new Access database called "NewAccess"

I have a table called "Manufactures" in New Access.

That Manufactures table is set up with a field AUTONUMBER UNIQUE ACCESS GENERATED NUMBER as the Primary Key.

Manufactures tables has a second field called "ManuNm" (Manufactures Name)

ManuNm is Indexed Unique No Duplicates

I have a 2nd Table in NEW ACCESS Called "SKUs"

SKUs has a field called ManuNm and it has a relationship to the table MANUFACTURES

The KEY Relationship is on ManufacturesID Primary Key and ManuNm (Skus Table) Foreign Key

****************THE MAIN AREA OF CONCERN BELOW**********************

If I was to import the CSV file made from OLDWARE into the table SKUs in NEWACCESS it would error upon the field ManuNm

The error is because the TEXT of GENERAL ELECTRIC is not a Numeric Value.

I hope this helps.
What part of - autonumbers MUST be numeric are you not understanding?  You CANNOT use manufacturer name as an autonumber.  If your Old system had included a numeric identifier, you COULD use that to append to the autonumber field.

To go from a text string in your old system to an autonumber in the new one is a bit of a challenge but it can be done.  First of all, start a separate database that you will use ONLY for the converstion because you will be creating a series of append and update queries that you will need to run in a specific order.  No conversion ever happens correctly the first time.  It is always trial and error.  Once you get all the steps worked out, then you push the button for the final time and the old data gets loaded into the new tables and you cut over to using the new application.

To move to an autonumber, you have to load the tables in top down order because the parent's autonumbers must be generated before you can append the child records that include the foreign keys.

1. load the parent table by selecting the old table and appending to the new one.  Access will automatically generate the Autonumber primary key.  Do NOT map any other field to the autonumber.
2. To load a dependent table, select the old dependent table and the NEW parent table.  Draw a join line on the OLD text primary key.  Select all the columns from the old dependent table EXCEPT for the old foreign key.  For the foreign key (which is now numeric), select the PK of the joined parent table.

You need to do this for each level down you go in the hierarchy.  Always include the new parent table and join to it on the old PK-FK but append the new numeric FK by choosing the PK from the parent table.
Dustin StanleyEntrepreneurAuthor Commented:
This is my NEWACCESS manufactures table currently just a quick post to help.

I will look into what you said above and see what i can do. Thanks.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.