Appending Excel 2007 data to Access tables

[pjaj]

I have a number of Excel spreadsheets (1 a week for the past 9 months - so far) in which 3 columns contain the same data (name1 name2 and class) from week to week but the other 12 change. There is also a column containing the creation date, repeated for every row. There are approximately 100 rows in each sheet.

I have imported the first into Access and appended the second. So then I had each name duplicated and two sets of 100 dates.

I then ran an analysis to normalise the common data into 3 separate lookup tables leaving all the variable data in the main table. So far so good.

But now I want to append the other 30+ spreadsheets, but I only seem to be able to do so into the original single table with the duplicate records.

Can Access import Excel data into multiple tables based on the field names (column headings)? If so, how?

 

[G37Sam]

Have a look at the TransferSpreadsheet command

 

[pjaj]

OK, it looks like the command I will have to use, but I'm not very happy with Macro / VBA programming. So I looked it up in another site and they say :-

Create a new macro, and in the first action box select “TransferSpreadsheet”.

Tried that (actually I'd got that far before I started looking) I typed in the command name - and Access 2007 says:-

"The text you entered isn't an item in the list"

And it's right, TransferSpreadsheet is not offered as one of the possible actions. Where do I go from here? Looks like I'm going to have to plunge into VBA editor / programming - ugh!

I've been reading up on TransferSpreadsheet but it seems that the columns in the Excel sheet must match 1-to-1 with the fields in the Access table (same as the import wizard). This is no longer the case as the data is now spread over 4 tables after normalisation. Have I missed something?

 

[DCrake]

Can you supply a copy of a sample spreadsheet please. I think you are overcomplicating things.

David

 

[neileg]

Why not append all 30 sheets into one table AND THEN analyse the data?

 

[DCrake]

Neil:
That was my tack but wanted to look at the spreadsheet first to be sure.

David

 

[pjaj]

Yes, I was thinking along the same lines as well - import all 36 then analyse. The only slight downside to this is that I get a new spreadsheet each week, but I suppose it does not matter.

I've uploaded a sample workbook here View attachment email081101.xls

BTW moderators - forum does not recognise or accept xlsx (Excel 2007) files for upload.

These are data on FTSE 100 & 250 company performance over a week and historically (this sheet is for the week 1/11/2008). Before I import into Access I need to massage the sheets a little bit. If you look at the "250" tab you will see that columns D,G & L contain date information which would change each week and consequently fail to import into Access when it tries to match them to field names. The "100" tab contains the slightly massaged data, I've removed the date component of the headings and added a date column "Yeild Date". Columns A & B contain the company EPIC code and name which is, of course, constant across all spreadsheets. Column C likewise contains the market sector in which the company operates. Hence the 4 normalised tables are Names, Sector, Yield Date and all the week-to-week variable data.

I'm currently working on the FTSE 100 data but I intend to go back and do the same for the 250 etc. Perhapse I should just forget normalisation!

 

[DCrake]

Having had a quick look at your spreadsheet, especially the 250 tab. What have columns D,G & L have in common?.....

Yes they all have a / (slash) in the first row. Access doesn't like this character as a being part of a field name. If you revise this column heading in both your spreadsheet and table you should overcome the existing problem.

David

 

[pjaj]

Having had a quick look at your spreadsheet, especially the 250 tab. What have columns D,G & L have in common?.....

Yes they all have a / (slash) in the first row. Access doesn't like this character as a being part of a field name. If you revise this column heading in both your spreadsheet and table you should overcome the existing problem.

David

I get these spread sheets sent to me. I have no control over the layout or headings. In the sheet I appended the 3 columns are titled "27/10 Div", "31/10 Close" and "31/10 P/E" in the next week's sheet they are "03/11 Div", "07/11 Close" and "07/11 P/E". So obviously Access won't recognise these as being the same field. By removing the date part of the title and appending a "Yield Date" column I can still tie the data to a specific date whilst keeping the column titles / field names the constant from sheet to sheet.

Access has not obviously baulked over the remaining "/" in "P/E" column L, neither has it objected to "(" and ")" in column N

Never the less, I will experiment with what you suggest and report back later. Import two sheets, normalise into 4 tables, try to import a third sheet.