Importing Excel data to an existing table

[tonyrhills]

I'm a newbie to this forum but I hope someone can solve my Access problem.



I am using Office 2003 and thought that each program interlaced almost seemlessly - but this appears not to be the case.

I have not used Access for over ten years (I have always used Excel), but now have a need for a database which gathers information on a monthly basis from four or five product retailers and assembles it in one database. This will give details of the customer, product purchased etc. I then wish to produce monthly reports and issue these to other stakeholders in this venture.

I can request the data in a set format from the product providers as an Excel sheet and I thought it would be easy to import each one into a single table, which can then be analysed both for that month and year to date etc.

To test what I am doing, I have created two Excel worksheets, both containing identical fields and with rows of data. I created an Access blank database and then used the external import wizard to get the first data into a table. This goes quite successfully, although it does not like the headers in the Excel spreadsheet, so I removed these.

The problem now comes to load the second batch of data which I would like to continue as additional records in the one table. The wizard allows me to pull up the second spreadsheet and displays the data correctly. If I leave the next page 'check here if first rows contains headers' unchecked (because I got rid of the headers and it is pure data), it will not allow me to select an existing table at the next stage. The radio button is for a new table and the lower button is greyed and you cannot select a table.

If, however, I check the previous 'first line contains headers' it then allows me to select an existing table and proceed, but then claims that errors have occured and the data has not loaded ... end of story!

I do not know what I am doing wrong here. The two Excel spreadsheets have identical columns etc. I have even cut the data down to just one column in each worksheet, but I still get the same result.

So my questions are:
1) Have I not set up the database in the correct fashion?
2) Have I not set up the table correctly to accept further data?
3) Has Access not loaded correctly and it is a software fault?

Any help would be much appreciated.

Tony Hills

[alansidman]

On the second go round, do you actually have headers in the first row and do they match exactly to the headers (field names) and in the same order as your table in Access?

[tonyrhills]

I've tried every which way - copied and pasted the headers to ensure perfect matching, removing headers, having headers on the initial set of data and not on the second batch and vice versa! Checked that they corresponded exactly with the headers in the table. I removed all formulae, leaving just raw data, removed any background fill and formatting on both spreadsheets and adjusted all column widths to be the same. I'm at a loss as to what I am doing wrong.

I did get it to work once - I opened a new Excel sheet, typed 1 to 5 in column A and did the same on a second new Excel worksheet, although I used 11 to 15. I headed each with the word Test and this worked which gave me hope, with 11 to 15 following on as new records from 1 to 5. But my hope was soon dashed when I tried the data I really wanted to import.

Tony

[tonyrhills]

Thanks Alan, but I think I've resolved my problem. I rechecked all the headers to ensure that they were a perfect match and found one which did not - 'country' instead of 'county'. Correcting this seems to have made it accept the second batch of data.

Many thanks for your help. Just a whole morning wasted trying to get it to work!

Tony