Excel spreadsheet spec

[Tanya Addison]

I can save an csv file specification to assist me with importing but I can not find a way of saving a similar spec for an excel file? Can anyone offer me any help?

Thanks tanya

 

[Norie]

Why do you want to save an Excel specification?

 

[Tanya Addison]

A file is being imported into the database as a table on a weekly basis. It details the drink orders for that week and each time it is imported it delets the existing one. I do not require all the fields in the spreadsheet but it is a standard format that is sent over from the warehouse every day!

 

[Norie]

Rather than deleting the whole table why not delete all the records?

This would leave you with a 'template', and you could just import into that.

The only problem is the fields you don't want.

Is this automated by the way ie using a macro or VBA?

 

[Tanya Addison]

Automated using a DoCmd to import. I don't mind add the records to an existing table but I do need to delete some cof the columns and I would possibly like to customise some of the others using a few string commands.
Is it better to do all this once the data is imported using queries?

Thanks Tanya

 

[Norie]

I would think so.

Why delete the columns? Just use queries to show only data that you want.

Again use queries to customise

 

[gbc123]

I'm with Norrie here - better to keep the standard format - import all 20 columns from the spreadsheet into your 20 fields in the access table. Then if you only need to get data back or report on fields 1-15, then just select these in your query. You can always delete the data in the fields you dont want after import if its easier for you to work with the table data.

Good luck.

G.

 

[Tanya Addison]

OK thanks!
Just one last things that may sound stupid. Why can you save a spec for a CSV file but not an xls file?

 

[Norie]

You just don't need it for an Excel file.

Access, supposedly, does all the work for you.

Importing from Excel is pretty straightforward as long as the data in the spreadsheet is well formed.