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

Sorry but can you explain how to use this code. Thanks

It is not code Dustin ... I am saying an AutoNumber field should be Numeric and should not contain Text.

ET

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!

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.

ET

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.

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.

Yes I understand and that is how I am setup currently.

ManufactureID      /       ManufactureName
________________________________________________

1                                             General Electric
2                                            3m
3                                             Allen Bradley
4                                               Dell
ETC.................................................................................

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.

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.
THANK YOU FOR YOUR HELP!

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.

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.