Results 1 to 13 of 13
  1. #1
    KINGOFCHAOS17 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2020
    Posts
    6

    CSV Import Filter

    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 load just 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?

    Appreciate any assistance you can provide.


    BasicCatalogue.TXT

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,999
    Link the CSV file as an external table.
    then make an append query to filter what comes in.

  3. #3
    KINGOFCHAOS17 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2020
    Posts
    6
    How do I do that please? I'm a complete novice to Access.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,999
    click external data, text file, choose the csv file.
    click LINK option
    then a box will appear, choose delimited (if so)
    bottom left corner, click ADVANCED,
    make sure all your settings are correct here,
    once done, SAVE AS and give a Spec name. Spec Names are used for all CSV file imports.
    then OK,
    continue thru all the settings until the end when you give it a table name.

    once linked in, build your query to import only the records you want by applying criteria filters.

  5. #5
    KINGOFCHAOS17 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2020
    Posts
    6
    Thank you and apologies, I should have been more specific. I know how to link a CSV file, just unsure how to write the query to filter out the records per the criteria provided at the start of the chain.

  6. #6
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,999
    use the query wizard, bring in the table,
    bring down the fields from the table to the grid,
    under criteria, set the filter:
    [city]='New York'

    etc

  7. #7
    KINGOFCHAOS17 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2020
    Posts
    6
    I'm sorry, but I'm not seeing how this works the IF THEN ELSE type scenario I described in the opening message.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,684
    Is this a one time import or does it happen daily, weekly, monthly?
    How many lines (records) in a normal CSV file?
    Table names?


    I would use VBA and queries to import the data from the CSV file into a temp (the data is temp, keep the table) table.
    Then write a UDF (user defined function) to parse the data and append the records to the correct table.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    KINGOFCHAOS17 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2020
    Posts
    6
    To answer your questions, it’s a weekly task, and contains approximately 3000 lines. The table is called Catalog and uses the field names specified in the same data file. I wouldn’t know where to start with VBA or UDF, my access skills are extremely basic.

  10. #10
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,006
    let me put this in a way that people can understand -- you know your data, we do not have the faintest idea. Explain as if you are explaining it to a stranger. and use examples

    ok so this is what I think you want by this statement

    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 load just import the first record corresponding to the NIIN.

    your data is
    "NIIN","CIIC","AAC","DMIL","ESD_EMI","HMIC","PMIC" ,"SOS","SCHEDULE_B","SLC","UI","REP_REC_CODE","MOE "
    "006469012","U","Z","Q","","P","A","SMS","88033000 60","0","EA","N","DF"
    "006469012","U","Z","Q","","P","A","SMS","88033000 60","0","EA","N","DS"
    "006469012","U","Z","Q","","P","A","SMS","88033000 60","0","EA","","DN"

    so out of those three records, you want the first one because it has DF

    with this one you want the third record because it has DF
    "NIIN","CIIC","AAC","DMIL","ESD_EMI","HMIC","PMIC" ,"SOS","SCHEDULE_B","SLC","UI","REP_REC_CODE","MOE "
    "010925376","U","D","Q","","N","A","SMS","88033000 60","0","EA","","GP"
    "010925376","U","D","Q","","N","A","SMS","88033000 60","0","EA","F","DS"
    "010925376","U","D","Q","","N","A","SMS","88033000 60","0","EA","P","DF"

    and this one, you want the first record (No DF but there is a DS)
    "NIIN","CIIC","AAC","DMIL","ESD_EMI","HMIC","PMIC" ,"SOS","SCHEDULE_B","SLC","UI","REP_REC_CODE","MOE "
    "011288269","U","Y","Q","","N","A","SMS","98031000 00","0","EA","","DS"
    "011288269","U","V","Q","","N","A","SMS","98031000 00","0","EA","","DN"

    I can't see an example where there is no DF and no DS so I've made this up. In this one you want the first record (GP)
    "NIIN","CIIC","AAC","DMIL","ESD_EMI","HMIC","PMIC" ,"SOS","SCHEDULE_B","SLC","UI","REP_REC_CODE","MOE "
    "010925376","U","D","Q","","N","A","SMS","88033000 60","0","EA","","GP"
    "010925376","U","D","Q","","N","A","SMS","88033000 60","0","EA","Z","DM"
    "010925376","U","D","Q","","N","A","SMS","88033000 60","0","EA","","DN"

    Assuming this is correct, the first two options (Has DF or if not has DS) is fairly straight forward, the 3rd less so - you need to define 'first' - the problem is databases have no concept of 'first' without a sense of order and there is no field in your data that can do this - it needs to be something like a unique ID or date/timestamp. So if you can compromise 'first' to mean 'any' (i.e. doesn't matter in the 4th example whether you get the GP, DM or DN record) then perhaps we are in business.

    Other things you need to clarify


    • can you have duplicate NIIN-DF or NIIN-DS records - is so, which one would you want to import? (remember there is no first)
    • does 'any' meet your requirement rather than 'first'.
    • is MOE always populated - (i.e. it will never be "")
    • is the data always consecutive (i.e. all records with the same NIIN are grouped together)


    Does this accurately describe what you are trying to do? If so please confirm and expand on if necessary. If it is not right, use a similar level of detail and style to explain exactly what you want to do

  11. #11
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,006
    assuming my assumptions are correct see attached DB
    Attached Files Attached Files

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,684
    Here is my attempt......


    You could use code to select the CSV file and import it to the tmp table, then parse the data...


    Good luck with your project.....
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #13
    KINGOFCHAOS17 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2020
    Posts
    6
    Thank you all, much appreciated for your hard work and assistance, this is exactly what I was trying to achieve.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Linked Outlook Tasks Folder Doesn't Import All Data
    By Bkper087 in forum Import/Export Data
    Replies: 0
    Last Post: 11-19-2019, 12:06 AM
  2. Replies: 6
    Last Post: 09-08-2019, 03:18 PM
  3. Replies: 1
    Last Post: 04-26-2016, 06:29 AM
  4. Replies: 13
    Last Post: 01-25-2016, 01:36 PM
  5. Replies: 3
    Last Post: 07-28-2015, 09:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums