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

 

[Mark_]

To add to the other's queries, will they be consistent in the names of their spreadsheets? Who will be managing where these files are saved? One of the most "Helpful" problems you will encounter is when each spreadsheet includes the date it was created in its name, especially if they don't have a consistent naming convention. This is mitigated if you (or a designee) is receiving them by email so you can save them with the appropriate name. Likewise can you confirm the creators of these spreadsheets won't change their formats?

 

[CarlettoFed]

For more concrete help, it is necessary to have examples of both Excel and Access files available.

 

[arnelgp]

Is there a way that I could import each sheet as is

it can be done of course.
but needs code to accomplish what you need.

what you will need is a form that has a List of all fields in Your table.
another List for the Columns of your worksheet.
there must be a code to "arrange"/delete Items of your List of Columns
according to the List of your Fieldnames.

in that way what you are coding is the actual "mapping" of your field
to the column of your excel worksheet.

the rest of the code to import can be used by Query or by VBA.

 

[AndT]

Just in case this is a recurring task, maybe try to harmonize the format of the data sources by providing a template or specifying the import file format.
Work needs to be done one way or the other. Wherever possible, I try to tidy up rather early on than down the line of a process.