Basic "?" Force Excel data into a table with different fields?

[sniffingvixen]

Using Excel 2003 and Access 2003.

I need to import Excel files from a third party. All files consist of one worksheet (different names) that contains results from a particular survey and, therefore, has a varying number of rows (300+) and columns (100+).

I'm interested in 15 columns, only a few of which have the same field name and column position for all files.

Almost all data are byte-sized numbers or "#NULL!". The exceptions are date (Custom: dd-mmm-yyyy), id (General: a 10-digit numeric ID that may start with zero), and store (General: 4-digit numeric ID). Of these, I only need "id".

I'd like to allow users to determine which columns to append to the SurveyResponse table, but can't figure how to implement the various options I've considered. Nor do I know which is the best.

Option 1
[ol]
[li]Import entire spreadsheet to...[/li]
[ol a]
[li]...a pre-made table with ~150 fields defined as text (importing field names as data too)?[/li]
[li]...a table created by VBA defined using Excel field names and data types?[/li]
[/ol]
[li]Add a field to that corresponds to ProjectID, which will be the same for all records.[/li]
[li]Allow user to pick needed columns. (another problem to solve on short notice without prior training ...)[/li]
[li]Append records to SurveyResponse table.[/li]
[/ol]
Option 2[ol]
[li]Allow user to select needed columns before importing.[/li]
[li]Append to SurveyResponses upon import with each record assigned the the user-defined ProjectID.[/li]
[/ol]

At the moment, the user selects the import file using the FileDialog property, which I'm planning to import using the TransferSpreadsheet method.

Any advice on where to look or how to proceed will be greatly appreciated.

Thanks,
j

 

[Darrylles]

Hi sniffing,

Unless you plan on becoming a data input admin: define the 15 column Excel spreadsheet that YOU want and lock down the cell properties (data-types), and have the users transpose the necessary data via cut & paste.

If users have a problem 'mapping' the old spreadsheet columns to your new spreadsheet columns - then they ask.

You then have a relatively simple import process to complete.
This is a one-off process (or should be - if spreadsheets are still to be used, then the resultant spreadsheet should be used in future as a template), therefore programming a solution would be a waste.

Otherwise, dependent on sheet quantities: you become a data
admin. rather than a database developer (or technical user).

If this is not feasible, then please explain your relationship to the '3rd party'.

Regards,

Darrylle








Never argue with an idiot, he'll bring you down to his level - then beat you with experience.