Import and append a column from excel to an existing db

[Colin Hayes]

Hi

I have imported a table from excel called IMPORT. It has a column of
reference numbers and a column called ASIN

My existing database is called Rec,

I need to add the ASIN column from my IMPORT table to my Rec table.

Can anyone advise how to do this?

I'm a beginner , so in simple terms please..

It needs to match against the existing rows in my Rec db, so that the
info I'm importing lines up. The ASIN column has data in precise order ,
so it should just match.

It needs also to become part of the Rec db , so I can abandon the IMPORT
db once it has been transferred.

Any help appreciated.

 

[Klatuu]

Add the new field to the Rec table.
Create an Update query to add the data from the IMPORT table. You will need
to filter it on the primary key of the Rec table matching the same field in
the IMPORT table so each record gets the correct value.

 

[Colin Hayes]

Add the new field to the Rec table.
Create an Update query to add the data from the IMPORT table. You will need
to filter it on the primary key of the Rec table matching the same field in
the IMPORT table so each record gets the correct value.

HI

I get an error , I'm afraid :

I've added the new field to the Rec table.

I've opened the query page and have both Rec and IMPORT tables showing.

I have the field from Rec that I want to update selected. I also have
the field (ASIN) from IMPORT that I want to bring in showing.

I select an Update query. Then , in the 'Update To' cell in the Rec
column I'm putting [ASIN].

When I run it , it appears to be working. The progress ticker on the
bottom of the screen goes to halfway and then stops. However , it then
whirrs away for about 15 minutes , and then gives an error 'Note enough
space on temporary disc'.

What am I doing wrong , I wonder?

Grateful for any pointers.

 

[Klatuu]

You need to join the two tables on a field common to both tables. Without
them joined, what it doing is creating a record for each possible
combinatioin of the two tables. The join needs to be constucted so the query
knows which record to append to.
--
Dave Hargis, Microsoft Access MVP

Add the new field to the Rec table.
Create an Update query to add the data from the IMPORT table. You will need
to filter it on the primary key of the Rec table matching the same field in
the IMPORT table so each record gets the correct value.

HI

I get an error , I'm afraid :

I've added the new field to the Rec table.

I've opened the query page and have both Rec and IMPORT tables showing.

I have the field from Rec that I want to update selected. I also have
the field (ASIN) from IMPORT that I want to bring in showing.

I select an Update query. Then , in the 'Update To' cell in the Rec
column I'm putting [ASIN].

When I run it , it appears to be working. The progress ticker on the
bottom of the screen goes to halfway and then stops. However , it then
whirrs away for about 15 minutes , and then gives an error 'Note enough
space on temporary disc'.

What am I doing wrong , I wonder?

Grateful for any pointers.

 

[Colin Hayes]

You need to join the two tables on a field common to both tables. Without
them joined, what it doing is creating a record for each possible
combinatioin of the two tables. The join needs to be constucted so the query
knows which record to append to.

Hi

OK Thanks for that.

I had a go at it , but it's beyond me , I'm discovering. It's such an
obstructive program.

Not really sure what you mean when you say 'The join needs to be
constructed so the query knows which record to append to.'

However , I joined the tables each to the correct fields and ran the
query. No error message , but no updates either. I changed the names of
the target fields so they were identical , and gained an error message.
I changed them back and ran it with them joined , and still nothing.

After many many hours now I am dying of frustration with this. I just
want to paste a single column from Excel.....

 

[Klatuu]

What I meant was to join on the fields that make the record unique so it will
update the correct row.

I would start by using a select query so I could see what is returning, then
once I was getting the correct results, change it to an update query.

 

[Colin Hayes]

What I meant was to join on the fields that make the record unique so it will
update the correct row.

I would start by using a select query so I could see what is returning, then
once I was getting the correct results, change it to an update query.

Hi Dave

When I run a select query , I can see the two target columns side by
side , exactly as I want. Data lined up exactly ready to switch between.
This is without joined tables.

Then , when I run the update I get this very long pause and the eventual
'temp file out of space.'

When I do join them , I join the source and destination fields. When I
run this , the progress meter runs very fast , but no update is made.

I'll keep playing with it , I'm clearly doing something wrong. I may
crack it somehow.

Thanks again. Grateful for any ideas.

I did have a look on the net before , but find the 'solutions' to be far
too technical in their language , and assuming of knowledge.

 

[Klatuu]

Post the SQL of the update version of the query. Maybe we can have a look.

 

[Colin Hayes]

Post the SQL of the update version of the query. Maybe we can have a look.

HI

OK Here's the SQL for the update version of the query , no fields
joined.


UPDATE IMPORT, Record SET Record.ASIN = [ASINNO];


I'm trying to move the data from Table IMPORT , field ASINNO

to

Table Record , field ASIN.


The Select version shows the correct columns side by side in perfect
position.

When I run this , it pauses for a long time and then gives an error.

Thanks again.

 

[John W. Vinson]

HI

OK Here's the SQL for the update version of the query , no fields
joined.


UPDATE IMPORT, Record SET Record.ASIN = [ASINNO];


I'm trying to move the data from Table IMPORT , field ASINNO

to

Table Record , field ASIN.


The Select version shows the correct columns side by side in perfect
position.

When I run this , it pauses for a long time and then gives an error.

Is there some field in the two tables that you can use as a link? You have a
"Cartesian Join" here - it will attempt to update *every single record* in
Record with the value from *every single record* in Import. If you have 20000
records in RECORD and 5000 records in IMPORT, it will try to do the update
10.000,000 times (most of them wasted!)

You must - no option! - have some field with a unique index in the table that
you're updating, and a corresponding field in the source table; join the two
tables on this field and then do the update.


John W. Vinson [MVP]

 

[Colin Hayes]

HI

OK Here's the SQL for the update version of the query , no fields
joined.


UPDATE IMPORT, Record SET Record.ASIN = [ASINNO];


I'm trying to move the data from Table IMPORT , field ASINNO

to

Table Record , field ASIN.


The Select version shows the correct columns side by side in perfect
position.

When I run this , it pauses for a long time and then gives an error.

Is there some field in the two tables that you can use as a link? You have a
"Cartesian Join" here - it will attempt to update *every single record* in
Record with the value from *every single record* in Import. If you have 20000
records in RECORD and 5000 records in IMPORT, it will try to do the update
10.000,000 times (most of them wasted!)

You must - no option! - have some field with a unique index in the table that
you're updating, and a corresponding field in the source table; join the two
tables on this field and then do the update.


John W. Vinson [MVP]

Hi

OK thanks for that. No wonder it was taking so long and getting so
bloated.

So if I make a field in each of the tables 'unique' , and then join them
, that will make it work? Does it matter fields which I choose?

There's no field in either table at the moment which is identical , if
that's what you mean.

Also , when I open the design view for each table , how do I make a
field unique?

Thanks for your help.

 

[John W. Vinson]

OK thanks for that. No wonder it was taking so long and getting so
bloated.

So if I make a field in each of the tables 'unique' , and then join them
, that will make it work? Does it matter fields which I choose?

There's no field in either table at the moment which is identical , if
that's what you mean.

Then you have a LOGICAL problem.

You have 20000 records in one table.

You have 5000 records in another table.

You want to update each of the 20000 records with the correct one of the 5000
records.

How can you DETERMINE which one of those 5000 records is the correct one?

If that does not reflect your situation, please explain.

John W. Vinson [MVP]

 

[Colin Hayes]

HI

Thanks for your email. Sorry if the situation wasn't clear.

I have a table with 18 columns and 40000 rows. I have an excel file with
just one column and 40000 rows. The content of the cells in the excel
column match exactly against the content of the rows in the dB. I'm just
trying to put the excel column in as an additional column to the dB.

I have actually solved it now. I copied out the row of auto numbers from
the Access dB and pasted them next to the data column in Excel. Then I
imported it as a table to Access and made the auto number column my
primary key for that new table. I made the auto number column in the
existing Access table my primary key too , and added a column ready to
accommodate the new data. I joined these two indexed columns together in
an update query and copied the data from the imported table into my
existing one.

Seems a very long-winded way of doing things , but it did work...

Best wishes.

 

[John W. Vinson]

I have a table with 18 columns and 40000 rows. I have an excel file with
just one column and 40000 rows. The content of the cells in the excel
column match exactly against the content of the rows in the dB. I'm just
trying to put the excel column in as an additional column to the dB.

I have actually solved it now. I copied out the row of auto numbers from
the Access dB and pasted them next to the data column in Excel. Then I
imported it as a table to Access and made the auto number column my
primary key for that new table. I made the auto number column in the
existing Access table my primary key too , and added a column ready to
accommodate the new data. I joined these two indexed columns together in
an update query and copied the data from the imported table into my
existing one.

Seems a very long-winded way of doing things , but it did work...

That's one key difference between spreadsheets (which have a defined order of
records, and for which "the 154th row" is meaningful) and relational tables,
which don't. Glad you got it working.

John W. Vinson [MVP]