Importing data from Multiple Excel Files into Access, mapping the fields and comparin

[Aiko Hayashi]

Hi, I'm moving over to Access from Filemaker I need to create an Access db that manages some data feeds from some supplier websites.

The data feeds come in differing excel formats 2003 and 2007. At the moment its 5 different feeds.

The feeds have the same general information in but come in different layouts. Example feed1.xls may have column 3 as
End Date, where feed2.xlsx may have column 6 as Expiry Date. The data is the same but in different columns and column names.

The end result I'm looking for is to merge all the data feeds into one table in Access to manage it. As the feeds change on a regular basis I also need to be aware of what has changed so highlighting the new / changed data would also be very useful.

Filemaker used to have a very good import tool that you could map excel fields to specific columns, but I cannot see this in Access.

What would be the best approach with Access?

[NeoPa]

That depends on how much automation you want to use. The more you want, the better information you'll need to provide in order even to be able to help and direct you.

At this stage though, I can tell you that importing in Access from Excel is quite lacking in flexibility and the ability to specify what you want. Therefore I would suggest you start looking at importing the spreadsheets into new tables and then examining the data imported in order to append it intelligently to your main table. Once the workbook has been imported you have access to all the fields and their names etc., etc.

[Mihail]

Why do not manage that in Excel ?
Create a new sheet, name the fields (columns) as you need, then fill that columns by simple append data from where you need.
Of course that task can be fully automated (in Excel VBA).

[NeoPa]

Mihail:
Why do not manage that in Excel ?

It's possible to handle it in Excel if you're comfortable with Application Automation, but the question is quite specific :

Aiko Hayashi:
What would be the best approach with Access?

[Mihail]

Hi, NeoPa !
Sorry, but I handle English like chinese they're symbols: as an idea. :)
I understand 75% then I complete the rest from my brain.
So forget me if I can't be very specific trying to provide some help.
More than, some times (not this time) I post only for subscribe to a thread. But, if I think that I have an idea, I post that idea too.

[NeoPa]

Mihail, I'm fully aware of the trouble you have posting and dealing with technical issues in English. I have trouble understanding much of what is said even by the people who are supposed to speak English natively, so I'm only impressed that you manage so well.

Don't worry about getting things wrong from time-to-time. We all do it anyway, and most of us don't have such a good excuse :-D

Only the most ignorant of members will ever criticise you for your attempts, and even those comments are unlikely to stay visible for very long ;-)

[Aiko Hayashi]

Hi, thanks for the responses and sorry for the late reply.

I will try and give some more specific info around what I have been asked to achieve.

So up to now I have been using Excel with a few macro’s to realign the columns around then merge to a single file, its turning into a right head ache as my manager has asked me to roll this out across the business, problem I have is that some of the sheets can be up-to twenty columns long. No two data feeds have the same column layout or even column names. Also more data feeds will soon be added. It seems to be a lot of manual work. I was hoping there would be a more elegant solution using Access and linking the tables in.

Here are some examples of the way that the excel data feed sheets are laid out.
(Most files contain a lot more columns)

File 1 – SupplierX–feed.xls
Opp Number Registered Customer Status Expiry
abc123 19/01/2012 BBC Approved 19/05/2012

File 2 – SupplierY-feed.xls
Customer name Deal ID Status Registration Date Expiry
ITV 555deal555 Review 02/02/2012 NA

File 3 – SupplierZ-feed.xls
Deal Number Customer Date Registered Deal Status Expiry Date
123456789 Channel5 01/10/2011 Expired 01/01/2012

Summary Report I’m looking to create will look like this.

Deal ID Customer Status Registered Expiry
abc123 BBC Approved 19/01/2012 19/05/2012
555deal555 ITV Review 02/02/2012 NA
123456789 Channel5 Expired 01/10/2011 01/01/2012

The data is generally the same but under different column names and different column position.

In term of automation, I was looking to dump all the data feeds into a single folder, this datadump would likely happen twice a week (mon + wed). Have these linked to a single Access database. Each feed as a separate table – this does not need to be dynamically created if say a new feed was added in, I can live with setting up a table each time again. The Deal ID columns will contain unique deal numbers, so this could be used a Primary key.

Access would then merge the feeds into a single report (prob to be exported to Excel).

Ideally I would be able view new records and changes to records since the previous datadump. E.g. in the above example if the status of deal 555deal555 for ITV changed to Approved I would be able highlight this record.

[NeoPa]

As your question is all about finding the common ground between disparate feeds, it's unusual that you say nothing about how to identify this common ground apart from very generally and non-specifically. Clever computer programs aren't really clever. They don't think like humans. They need to be told how to respond to the data they find. To do that you need to analyse the data they're likely to find and set up rules for the program to follow.

I see nothing that would even help with that in your latest post (though clearly you're trying to provide the required information). I hope this helps you better to understand what is required before we can help you, to proceed.

What data is consistent across all feeds?
What are the column titles for this data across the different feeds?

I hope you appreciate also, that programming something like this is many times more complicated than doing it yourself manually. I would say too complicated for most people to handle. What you ask is not trivial, and even less trivial to pass on to someone else if they need to be told. Don't be surprised if you find it's all too complicated at the end of the day.

[Mihail]

As I see the problem I think that is easier to design a really database, using Access, starting from zero.
After you have a functionally program fill the tables, even manually, then forget Excel.

I say that based on two of your sentences:
1) At the moment its 5 different feeds (#1);
2) Also more data feeds will soon be added (#7)
I wish to add that more suppliers will soon be added. Isn't it ?

This and your simple data say me that this task is a perfect candidate for an Access database.

Alternatively, standardize the Excel input using unique identifier for suppliers, feeds and so on.
But, in my opinion, this is a hard work. And why to do that if Access is designated to mange that type of data ?

Sorry NeoPa.
Again I don't answer to original question, but, in my opinion, is no answer to fit the original question and Aiko's background.
One of them must be changed :) .

[MMcCarthy]

Aiko

As I understand it. The data in these feeds is the same but the column titles and positions will vary. Access doesn't have a mapping tool as you understand it.

What NeoPa is saying to you is that in Access we use something call Automation to code the import routines. The code would be designed to open each excel workbook and examine it, then map the data appropriately. This code would necessarily be quite complicated.

An alternative suggestion you could try setting up saved import routines. By this I mean you can import each feed manually once and "Save and Name" the import steps. You can then use this template to import that feed from then on. However, this would depend on the feed never changing. By that I mean that column names and placement for Feed1.xls would always have to be the same.

If this is not the case the NeoPa's suggestion is the only way to go.

[NeoPa]

Close enough Mary. Not necessarily Application Automation, as it could be handled within Access, but the code would necessarily be somewhat complicated if it had to determine (from rules not yet available to us) what should be where. This could be manipulated within Access, but I believe your idea about multiple, predefined, import routines set up manually, to match all possible scenarios is a more straightforward approach. More likely manageable by a standard power-user of Access rather than a full-blown developer.