Importing Data via CSV and Error Checking

[billelev]

I am importing data from a CSV file into an MS Database.

The file containing the data has around 20 fields, and each field has a range of possible values. These values are known.

I would like to be able to check, on import, whether the data in a field contains one of the legitimate field values (or not). What would be the best (i.e. simplest/most elegant/robust) way of doing this?

I am currently thinking that a "Select Case" method for each field may be the way to go, but I have a feeling a better solution may exist.

For example, for the first field, check to see if the value is valid using Select Case:

    Select Case dataFldOne
        Case "ACES"
            result = TRUE  
         Case "FIX"
            result = TRUE
        Case "PHN"
            result = TRUE  
        Case "SDOT"
            result = TRUE       
        Case Else
            result = FALSE
    End Select

[ADezii]

Personally, I would Import 'ALL' the data, then run a series of DELETE Queries to eliminate the data which does not conform to certain Field specifications.

[billelev]

Personally, I would Import 'ALL' the data, then run a series of DELETE Queries to eliminate the data which does not conform to certain Field specifications.

But you would still need to check the data in order to perform the delete query. How would you check the data?

[ADezii]

But you would still need to check the data in order to perform the delete query. How would you check the data?

I will need much more information than that which you have provided, such as:

1. Name of the Table to which the CSV Data was Imported.
2. Field Names within this Table.
3. Criteria within these Fields that would be the Criteria for Records containing these values to be Deleted. e.g. tblImport would contain a Field named [State], if the values PA, NJ, or FL appear in this Field, Delete those Records.
4. etc...

[MMcCarthy]

Personally I would use lookup tables to hold the known values. For example if we were discussing countries I would have a lookup table holding all country values. Then on the import you could run a DLookup() statement to see if the value is contained in the table. The efficiency of this depends on how many fields this applies to and how many values are contained therein.

Mary