Importing data

[Jenny1]

Someone recently answered my query with regards to uploading data to an access table.

The first coloumn is an auto incremented number.

Will there be any problems with importing data - when one field is auto increment?

Obviously I wont be uploading values for this column - can that be done?

 

[KenHigg]

Are you importing the data then appending to an existing table?

 

[Jenny1]

I dont get what you mean,

In fact I am not sure how I would go about importing.

Attached is my database,

I will be importing People and Organisation data.

What woud be the steps to do this??

 

[KenHigg]

Importing data is a little bigger topic than I could do justice to in a forum like this - You could google "Importing data in Access" and probably get good results -

 

[Rabbie]

Where is the imported data coming from. You need to know its format. Let us know please

 

[Jenny1]

The data is coming from a file with extention .csv looks exactly like a excel spreadsheet but only a single page.

I have attached the database - as you can see from the database, I want to upload to two tables, one is called people and the other organisations.

I have organised the data so that the columns match BUT the first field in my database is an auto increment ID. I dont want to upload data for the first field.

Please advise how I should do this.

 

[GaryPanic]

use the wizard

first make a copy of the empty d/base

now use the wizard - take you time read every screen .

I presume that the data you are importing is in two tables if it is not - then slightly more complicated

lets assume its one table that you have and you want to import into two tables - the way i would do this would be import the whole data into a new table (using the Wizard )
now use append qry on this table to insert into the other tables - then delete the datatable and you would be left with just the tables you want ...

if you get stuck let us know

 

[Rabbie]

You should be able to use an append query to add the records you want to your tables. The Autonumber field will update automatically. You will need to open your .csv files as linked tables until you have transferred the data.

 

[Jenny1]

Sorry If I am mixing this up,

But yes I have two tables,

First I am going to insert the organisations and then the people, I have arrange the data in two tables.

When you say use the wizard - are you saying rightclick on the main page and select import?

OR are you saying - use the wizard which says - 'create table by entering data'

And when you say make a copy of the empty database - I assume your saying this just incase the upload messes up??

Please advise

 

[GaryPanic]

yes - just in case it goes wobbly (wrong)
if you go to file -import it will talk you through this process - but read the screens carefully - this will really help you out in the long run - and if it goes tits up - then you still have the empty access dbase

this is not a complicated process- just a bit scary if its your first time after you have done this once or twice , then you canm do it in your sleep.
g

 

[Jenny1]

Ok Guys Ive tried soo hard and this is what happens.

Right click and import

Choose the file I want to import

Get message saying - your data seems to be in a delimited format, if it isn’t then choose the format the more correctly describes your data.

Delimited format is checked by default – characters such at comma or tab separate each field (which is the case so I leave this checked)

Displays data in rows separated by commas (in a white box on the same page as the above)

I ignore all other options i.e, advanced and press next

Which delimiter separates your fields?
By default comma is checked (I leave this checked as I think it is correct)

This time data is displayed separated by columns (in a whitebox on the same page as above)

Where would you like to store your data? In a new table, in a existing table?

I select the organisation (because this is what I want to upload to) table and click next

Then click finish
Data attempts to import and I get a message saying data could not be appended to table.

It then creates a table with the same name as the file I am uploading from but puts an “errors” extension on the end.

First problem is that data doesnt upload
Second problem is that I cant see any option which would allow me to upload data and NOT upload anything to the first column (i.e the auto incremented ord id)

Please advise

 

[GaryPanic]

repeat this - but into new table and see what happens - when its in a new table we then then mess around with it -

 

[GaryPanic]

how much data is being imported thousands of records or what ???

if this becomes a real bug - you can always zip the csv file up and one of the guys will drop it into a access file for you

if you don't want this to be done on the forum - I would be happy to have a look at it for you off the forum

 

[Jenny1]

Hi, Thanks for your replies - im only uloading about 600 records.

Anyway goodnews, I have managed to upload the organisations data to a new table - like you said.

All four fields exist in theri own table.

Now I have to get this information into the 'real table' and avoid entering anything into the auto incremented primary key,

Can you help??

 

[Jenny1]

is it just a case of adding the additional auto increment field and then renaming the new tabole so that it matches the old one. and deleting the old one.

 

[Jenny1]

I have just attempted this method - and realised once I rename the new table (which stores the data) to match the name of the old table It will replace the old table (which is what I want) but it will also delete the relationships of the old table so I will havr to do all the relations hsips again.

Is this the best way to go about this

 

[Rabbie]

It would be possible to use an append query to copy the data from the new table to the existing table. This would mean the relationships would not need to be recreated.

 

[Jenny1]

That sounds exactly like what I want to do.

How do I do this, I feel like I am nearly there.

Please advise

 

[Jenny1]

Microsoft Access append queries are good for adding data to a different table where the data is based upon a selection criteria. However, append queries are not always the most efficient way of adding records to another database. If you need to add all records and fields from one table to another table, the append query is not the best way to do it. Using Copy and Paste options in this case would be the best solution.


I got this off the web, it suggests I copy and paste.

especially seen as I am trying to move 659 records across

Whts do you think???

 

[Jenny1]

okay - as a test I copy and passted the data across - problem is its given me auto incremented org_ids which start at a ridiculously high number

for example the org_id of the first record is : 40243719


Why isnt it starting at 1??

Any ideas tips or advice please