Solved

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

Posted on 2016-10-04
15
63 Views
Last Modified: 2016-10-06
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.

db73.jpg
0
Comment
Question by:Dustin Stanley
  • 7
  • 5
  • 3
15 Comments
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 250 total points
ID: 41828964
AutoNumber field = Numeric not Text.

ET
0
 

Author Comment

by:Dustin Stanley
ID: 41828971
Sorry but can you explain how to use this code. Thanks
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41828989
It is not code Dustin ... I am saying an AutoNumber field should be Numeric and should not contain Text.

ET
0
 

Author Comment

by:Dustin Stanley
ID: 41829013
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!
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41830217
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.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41830372
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
0
 

Author Comment

by:Dustin Stanley
ID: 41830676
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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 34

Expert Comment

by:PatHartman
ID: 41830706
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41831039
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41832092
Autonumbers are AUTOMATICALLY GENERATED.  You DO NOT enter them.  The database engine creates them as each new row is added.
0
 

Author Comment

by:Dustin Stanley
ID: 41832106
Yes I understand and that is how I am setup currently.

ManufactureID      /       ManufactureName
________________________________________________

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

Expert Comment

by:PatHartman
ID: 41832127
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41832213
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!
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41832251
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41832264
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.

db76.jpg
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now