importing and processing excel file for report

[megatronixs]

Hi all,

I have an excel file that I need to prepare to create a report.
It would be great if I could do this with Access 2003. This would mean that I need to import the excel file and create a table to handle this.
Is there by any chance that I don't need to create a table and I can process the excel file?
Or maybe automate the import of the excel file into table and only use a few of the columns so later I can create the report in access.
Also what I need to do is add some columns with text in it (a standard text) that needs to add a day taken form the column to the right.

I simply would like to avoid to create a table from the import as this would make me end with a lot of tables (or mabye option to delete the table after finishing producing the report.

Any ideas on this would be really great.

Greetings,
megatronixs

 

[CJ_London]

Depends on how big your excel file is but you can simply link to the excel table using the excel option on the external data tab and write your queries from there. This can also be automated from VBA

Problems may occur with date and number formats (currency, 000 separators etc) - the data on the first line (of data) dictates the datatype for the column so if it is say a number but should be text you will get problems. If you import the data, or append to an existing table, you can resolve datatype issues by creating a suitable import profile using the wizard. You can also specify which columns to import.

The other issue, to do with size, is the excel data will not be indexed (which you can apply on import) so this can have a significant effect on performance. Up to 100,000 rows is probably OK, after that you will notice a big slowdown