Importing from Excel

[BeachBoy]

Hi, newbie to databases here. My project is such

I am importing about 15 excel spreadsheet with various hotel booking data. Each spreadsheet could have slightly different spellings in the column (field names) so I have so far been correcting this in Excel before importing so it matches my master table. Is there a way that I could import each sheet as is, then link each imported table field names to my master? I have tried relationships but I never seem to any any results back.

I.e.
Master Table field : PROPNAME
Excel header: Property name

I have created a relationship here but how do I then run a query (Or something else) to show all of the imported data table in one big table?

Thanks

 

[Pat Hartman]

If the formats are consistent but the column names are variable, you can write a procedure to open Excel using OLE automation and replace the header row with one with the desired column headings. Then you can import the sheet as normal.

If you are saying the format of the workbooks is different, that's a bigger problem. You either have to get the users to use a consistent format or you have to be prepared to link each variant and either use a predefined append query that maps the fields or you create a new query if the sheet is a formerly unseen format.

This has nothing to do with relationships.

 

[plog]

Agree with Pat, not a relationship issue. In general you build an import process, run that and in the end all your data is in the appropriate tables/fields of your actual database.

The actual import process is predicated on the details of those 15 files. How often will you be doing this? Will you always have 15 or does it vary (sometimes 11, sometimes 9, sometimes 15)? Are all those files formatted the same--all have exact same fields in the exact same order and of the exact same data type? Or are there extra fields in some, some with different orders and different data types?

 

[CJ_London]

The solution depends on whether you have:

1. same number of columns in the same order with slightly different headings
2. Same number of columns in a different order with or without slightly different column headings
3. Different number of columns