 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / February 2008Import and append a column from excel to an existing db
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. 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.  SignatureDave Hargis, Microsoft Access MVP > Hi > [quoted text clipped - 17 lines] > > Any help appreciated. >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. 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. SignatureDave 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 [quoted text clipped - 23 lines] > > Grateful for any pointers. >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..... 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. SignatureDave Hargis, Microsoft Access MVP > >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 [quoted text clipped - 18 lines] > After many many hours now I am dying of frustration with this. I just > want to paste a single column from Excel..... >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. Post the SQL of the update version of the query. Maybe we can have a look. SignatureDave Hargis, Microsoft Access MVP > >What I meant was to join on the fields that make the record unique so it will > >update the correct row. [quoted text clipped - 21 lines] > 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. >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. >HI > [quoted text clipped - 13 lines] > >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 >> [quoted text clipped - 25 lines] > > 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. >OK thanks for that. No wonder it was taking so long and getting so >bloated. [quoted text clipped - 4 lines] >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] 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. >>OK thanks for that. No wonder it was taking so long and getting so >>bloated. [quoted text clipped - 19 lines] > > John W. Vinson [MVP] >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 [quoted text clipped - 11 lines] > >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] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.