Copying and pasting information from excel to access

[oli insight]

I have an excel spreadsheet with 500 rows of information. I want to copy and paste that information into an access table that i'm using, column by column.... But access only lets me copy and paste cell by cell from excel to access. Of course i could do that or even use one of my forms i created but that only allows me to import the information record by record, but that would take for ever when i'd like to do the 500 records in a few big scoops (column by colunm).
I've tried using the template of my access table, putting the information into it using excel and then importing the excel speadsheet but it won't "update" the current access table, it simply "overwrites" or "adds" another table...

How can I quickly and easily copy and paste chunks of information from excel to an existing access table ?

Thanks for your time

[garethfx]

I have an excel spreadsheet with 500 rows of information. I want to copy and paste that information into an access table that i'm using, column by column.... But access only lets me copy and paste cell by cell from excel to access. Of course i could do that or even use one of my forms i created but that only allows me to import the information record by record, but that would take for ever when i'd like to do the 500 records in a few big scoops (column by colunm).
I've tried using the template of my access table, putting the information into it using excel and then importing the excel speadsheet but it won't "update" the current access table, it simply "overwrites" or "adds" another table...

How can I quickly and easily copy and paste chunks of information from excel to an existing access table ?

Thanks for your time

i normally link the database to the spreadsheet using the tables link wiz then once done use append to transfer the data over

G

[jamjar]

i normally link the database to the spreadsheet using the tables link wiz then once done use append to transfer the data over

G

You can't copy and paste (as you've found out).
You have to import or link the data, then as G suggests, use an append query to add it to your table.
It sounds like you've already figured out how to import the data. And I assume you already have a table set up to which you'd like to add the new data.

1. Use a query wizard to create a query based on the new table, choosing all the fields you want. (If you want all the fields, just hit >>).
2. Finish the wizard and see if you're happy with what its showing (should be good at this point).
3. Click the Design button (top left toolbar, looks like a blue triangle with a pencil above it).
4. Click the Query Type button (middle of toolbar, looks like two spreadsheets).
5. Select Append Query
6. Select the table you want to append to.
7. For each field, select the appropriate field to Append to (4th row down in the lower section of the form).
8. Click the Run button (middle of toolbar, looks like a red !)
9. You should receive a prompt asking you to confirm you wish to append x number of rows ....

Step 7 will be easy if you used the same Field Names (column headings) in your tables.
If it all works out OK, you can save this query so you don't have to create it again. Just import the new data to the same new data table each time, overwriting the old one, and run the query. Just be careful not to run it twice, or you'll duplicate your data (unless you have a primary key to prevent that .... !)

James

[oli insight]

You can't copy and paste (as you've found out).
You have to import or link the data, then as G suggests, use an append query to add it to your table.
It sounds like you've already figured out how to import the data. And I assume you already have a table set up to which you'd like to add the new data.

1. Use a query wizard to create a query based on the new table, choosing all the fields you want. (If you want all the fields, just hit >>).
2. Finish the wizard and see if you're happy with what its showing (should be good at this point).
3. Click the Design button (top left toolbar, looks like a blue triangle with a pencil above it).
4. Click the Query Type button (middle of toolbar, looks like two spreadsheets).
5. Select Append Query
6. Select the table you want to append to.
7. For each field, select the appropriate field to Append to (4th row down in the lower section of the form).
8. Click the Run button (middle of toolbar, looks like a red !)
9. You should receive a prompt asking you to confirm you wish to append x number of rows ....

Step 7 will be easy if you used the same Field Names (column headings) in your tables.
If it all works out OK, you can save this query so you don't have to create it again. Just import the new data to the same new data table each time, overwriting the old one, and run the query. Just be careful not to run it twice, or you'll duplicate your data (unless you have a primary key to prevent that .... !)

James

Nice one J, That really helped, cheers ! : )