Question Check to see if import file has matching table field before import (1 Viewer)

[ccondran08]

Hi, I have a small problem here when I run a saved import of a spreadsheet file and sometimes there is an extra field in the spreadsheet that doesn't match the field in the table that it is getting imported to. Therefore it is creating an error message.

Does anyone know of any code that will allow you to check the spreadsheet file first to see if they all match the fields in a certain table before starting the import and then activate a message box if there is an extra field that needs to be deleted ?

Many thanks.

 

[Pyro]

I don't think you can do what you are asking with saved imports (or at least i haven't tried it ). Two potential solutions would be:

1. Automate the entire import process, and check each column as you go.
2. Import the data to a temporary table, then use an append query to copy only the data from the fields you require, before dropping the temp. table.

 

[gemma-the-husky]

I would import to a temporary table, and then check the import for the columns you expect.

this will return true if the field exists, or error (not false - error) if it doesn't

foundfield = currentdb.tabledefs("importedtable").fields("fieldname") = "fieldname"

amend this as necessary to check all the fields.
because people view excel files, you can get extra rows and columns you didn't expect. I always prefer a csv to an excel file for that reason (and other reasons)

extra fields won't matter if you do it this way, anyway.