Solved CSV Import Filter

[KINGOFCHAOS17]

I have a CSV file with multiple 'columns' of data I want to import into an Access table however I would like to filter which records are imported based on the contents of one field. I've attached a sample set of data to hopefully assist in explaining what I'm trying to do. In the sample data you'll note that that 006469012 appears multiple times in the column NIIN, what I'd like to do during import is activate an IF THEN ELSE type criteria to determine what gets imported based on the value in column MOE. What I'd like is, if MOE contains DF that records is imported and the rest associated with NIIN 006469012 are discarded, if DF doesn't exist try DS, if that doesn't exist then import the first record corresponding to the NIIN.

Honestly not sure what I'm trying to do is possible. If this can't be completed at the import stage, can it be done at a query level, if so, how?

Appreciate any assistance you can provide.

 

[theDBguy]

Hi. Not sure that is possible "during" the import process (unless you use some code to parse the CSV file, one line at a time, or something). What would probably be easier is to simply import the whole thing into a pre-processing table and then import what you need from there into your main table.

 

[KINGOFCHAOS17]

Thank you for your response. Once the complete CSV file is imported into a table how would I go about writing a query to filter based on the criteria I provided? Is that even doable? I'm trying to automate a process which at the moment is very time consuming and open to error.

 

[theDBguy]

Thank you for your response. Once the complete CSV file is imported into a table how would I go about writing a query to filter based on the criteria I provided? Is that even doable? I'm trying to automate a process which at the moment is very time consuming and open to error.

Hi. Yes, I am thinking it's possible, but I haven't looked at your sample file yet. Give me a sec, and I'll take a look. I'll be back...

 

[Ranman256]

link the file as external table,
make an append query to add the data from this table , and set criteria to filter or alter records.

 

[isladogs]

I agree about initially importing to a staging or buffer table for processing.
Next I suggest you add a Boolean field called Tag or similar to that table which will be used to identify (tag) the records you want to keep for importing to the final table.
Now create a function which works in turn through each of your conditions and sets the Tag field to true for all those wanted records.
Finally run an append query ro import all records where Tag = True

 

[theDBguy]

I agree about initially importing to a staging or buffer table for processing.
Next I suggest you add a Boolean field called Tag or similar to that table which will be used to identify (tag) the records you want to keep for importing to the final table.
Now create a function which works in turn through each of your conditions and sets the Tag field to true for all those wanted records.
Finally run an append query ro import all records where Tag = True

Hey, I may not need to look at your file if you can use this approach. Please let us know if you need help with it. Cheers!

 

[vba_php]

Thank you for your response. Once the complete CSV file is imported into a table how would I go about writing a query to filter based on the criteria I provided? Is that even doable? I'm trying to automate a process which at the moment is very time consuming and open to error.

if you have ever used a hosting company back-end tool like phpMyAdmin, you would notice that delete queries like you want to use are always run like a typical machine, like this:

DELETE FROM table WHERE id = 1
DELETE FROM table WHERE id = 2
DELETE FROM table WHERE id = 3
DELETE FROM table WHERE id = 4
DELETE FROM table WHERE id = 5
DELETE FROM table WHERE id = 6

that is because it was designed in a very BASIC manner. this might be what you have to do if you want to run a single query. obviously you don't want to do that. thus, a VBA routine is necessary if isladogs' solution does not work.

 

[CJ_London]

you can write a query to link to the csv file rather than creating a linked table - you can then link that query to other tables, apply criteria, whatever to use in an insert query. Basic structure for the query would be

SELECT * FROM [TEXT;DATABASE=C:\pathtofolder;HDR=Yes].filename.csv

simplistically the path is the database, the file the table. if you want to do this in one query rather than save the above then the starting point for your query would be

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\pathtofolder;HDR=Yes].filename.csv) AS txt

then go to the query design window and do what you need to append/filter/whatever

Benefit of the query method is it avoids creating and (perhaps) deleting table objects