Importing Query Data into an Existing Table

[TaylorDF]

Hi guys,

Stuck on an issue.

I'll start with an example to hopefully make it clearer and easier to understand.

• I receive a CSV file with the following headings [Age, Height, Name, Gender, Location] with 10 records every week.
• I have an access database which stores all of these in, although I decided that Height and Gender was not important so I have removed these from my access DB and added two new tick boxes at the end which are [Boy, Girl] which are both tick boxes

I am looking to be able to import this CSV file into my existing table without any issues. What I've done so far is made a query which only displays [Age, Name, Location] from the CSV file. Is there a way for me to then import that into the database with it automatically added the two tick box fields.

So my database would look like [Age, Name, Location, Boy, Girl]

If it doesn't make sense let me know and I'll try to explain better

Thanks.

Taylor

 

[Joe4]

If the headings in the CSV file match the headings in the Access Table, you should be able to import them directly into your Access Table.
If you go through the Import Wizard, you can set up an Import Specification and save it so that you can re-use so you don't need to go through those same steps every time.
Essentially, you would tell those two fields that you don't want to not import, and match the other ones up. It does not matter if you have those two "extra" fields in your Table that do not exist in your CSV file.

 

[TaylorDF]

Hi Joe,

Thanks for the help.

I gave this a try although it seems I can only select the fields that I do not want to import when I am making a new table and not when adding to an existing table.

How would I look to import new data into the existing table? All of the headings from the new data are in the old table although the old table has 10 columns
Thanks,

Taylor

So I now have a table with the newly imported data and my original table

 

[Joe4]

So, here are the steps:
1. Go to the External Data menu
2. Select "Text File" from the Import & Link ribbon
3. Browse to your CSV file
4. Select the "Append a copy of the records to the table:" option and select the Table name you are importing into
5. Click OK
6. Select the "Delimited" option and click Next
7. Check the "Comma" option
8. Check the "First Row Contains Field Names" field
9. Click "Next"
10. Click the "Advanced" button
11. On the Field Information grid, click the "Skip" boxes next to the Height and Gender fields
12. Click the "Save As" button
13. Give your Import Specification a name and click OK
14. Click the OK button
15. Click the Finish button
16. Click the Close button

This should do what you want. I actually walked through all of these steps and confirmed that it works.

Now that you have created an Import Specification, the future imports are easier. You would just do the following:
1. Go to the External Data menu
2. Select "Text File" from the Import & Link ribbon
3. Browse to your CSV file
4. Select the "Append a copy of the records to the table:" option and select the Table name you are importing into
5. Click OK
6. Click the Advanced button
7. Click the Specs button
8. Select your saved Import Specification and click Open
9. Click OK
10. Click Finish
11. Click Close

You can also set up a Macro to do the imports, if you know the file names, using the Import Specification we created.