Data match and edit txt list from MS Access file

May 24, 2022 12:17 PM

I have an Access plant database (Office 365, with local database file) with Genus and plant family for each plant). I also have a plantsListJobName.txt list of five hundred plants (from an historic garden). This .txt is to Genus level (with other fields - all comma separated), and many of these 500 plants occur in the DB. I want to add plant family name to my text file where there is a match.

Text file looks like this, 100's of lines. Genus is always the first string.
Lamiastrum galeobdolon Herman's Pride?
Astrantia major Somerdale, variegated Vivid yellow variegation
Phebalium Illumination, good yellow, survived 96 cold event
Serophularia aquatica Variegata,


Lines end with:
nothing
.
?

Is there a workflow where I could check my .txt against the DB, and add the plant family name to my .txt - I don't want to add my historic plants to the DB as I don't expect to use many of them again after this job. I'm not a script-writing person, but can describe what I need, so I'll leave this here at this point and hope someone answers.

posted by unearthed to Computers & Internet (2 answers total)

Import the .txt into your Access database as a separate table, then join to the table that contains the plant family via query. Output the results of the query to a new .txt file. You can memail me if you need more assistance.
posted by mezzanayne at 1:07 PM on May 24, 2022 [2 favorites]

If you’re more comfortable working with Excel than Access then Power Query could definitely do this. It can import the data from the Access file and your CSV without modifying either original file, clean up that data however you need, and then and merge it using whatever criteria you wish. It’s all very visual and each operation you do to the resulting table is a step you can go back to and change as needed until the output is what you want. You’ll end up with an Excel table that could then be saved as a CSV txt file.

There’s definitely a learning curve, but I found it way easier to learn coming from a knowing basic spreadsheets background than Access was, which I still haven’t figured out. I can do everything I need with Power Query so I haven’t bothered. This would be a ten minute job and you could even make some cool charts!
posted by Jawn at 9:18 PM on May 24, 2022 [1 favorite]