# Using SQL to query iNat's DWCA taxonomy export

**URL:** <https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377>\
**Category:** Tutorials\
**Created:** [January 23, 2022, 6:51pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377 "2022-01-23T18:51:03Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![pisum](https://avatars.discourse-cdn.com/v4/letter/p/91b2a8/32.png) [@pisum](https://forum.inaturalist.org/u/pisum)\
**Post date:** [January 23, 2022, 6:51pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/1 "2022-01-23T18:51:03Z")

</div>

it’s not necessarily an easy thing to do, but you could use the data in the the taxonomy DWCA export ([https://www.inaturalist.org/taxa/inaturalist-taxonomy.dwca.zip](https://www.inaturalist.org/taxa/inaturalist-taxonomy.dwca.zip)) as the source for searching common names and lexicons.

for me, i think the simplest process would be:

1. download the file
2. extract the files
3. create a new SQLite database
4. import taxa.csv as a new table
5. import the VernacularNames-XXX.csv files into a new table (data from all these files goes into a single table).
6. (optional) index tables
7. run SQL queries as desired

i haven’t tried (yet), but i wouldn’t expect it to take more than 10 or 15 minutes to accomplish #1-6 above. (and if i saved a few scripts from that process, it would be even faster to do in the future.) it should be possible to do similar things in R or Python, etc.

for reference, VernacularNames files include these fields:

- taxon\_id (numeric ID)
- vernacularName
- language (code)
- locality (name)
- countryCode
- source (description)
- lexicon (name)
- contributor (name)
- created (UTC datetime)

and the taxa file contains these fields:

- id (numeric ID)
- taxonID (iNat taxon page URL)
- identifier (iNat taxon page URL)
- parentNameUsageID (iNat taxon page URL)
- kingdom
- phylum
- class
- order
- family
- genus
- specificEpithet
- infraspecificEpithet
- modified (UTC datetime)
- scientificName
- taxonRank
- references

the main limitation of this approach is that it would not be real-time. also, the export may include only active taxa. and, of course, not everyone may have the technical experience needed to work with these files.

UPDATE: i created a local database using the data from latest iNat taxonomy export file, and here’s an example of query that i ran on it:

 ![image](https://us1.discourse-cdn.com/flex016/uploads/inaturalist/original/3X/b/4/b414b8000641cc792ebf66022db44c4781f8614f.png)

(if it would be helpful, i could probably load a snapshot of the latest dataset to data.world so that it could be easily accessed using whatever technology you’re most comfortable with. just let me know.)

---

<div class="post-metadata">

**Author:** ![optilete](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.inaturalist.org/optilete/32/1908_2.png) [@optilete](https://forum.inaturalist.org/u/optilete)\
**Post date:** [January 24, 2022, 8:09pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/2 "2022-01-24T20:09:32Z")

</div>

Thank you for pointing to the download. I found the culprit.

---

<div class="post-metadata">

**Author:** ![raymie](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.inaturalist.org/raymie/32/12650_2.png) [@raymie](https://forum.inaturalist.org/u/raymie)\
**Post date:** [January 24, 2022, 9:08pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/3 "2022-01-24T21:08:16Z")

</div>

I’d also like to be able to search for species without common names.

---

<div class="post-metadata">

**Author:** ![pisum](https://avatars.discourse-cdn.com/v4/letter/p/91b2a8/32.png) [@pisum](https://forum.inaturalist.org/u/pisum)\
**Post date:** [January 24, 2022, 9:22pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/4 "2022-01-24T21:22:32Z")

</div>

this is also possible by querying against the data in the DWCA export file.

for example, this query:

```auto
SELECT A.scientificName, A.taxonID
FROM taxa A
LEFT JOIN vnames B
	ON A.id = B.id
WHERE taxonRank = 'species'
	AND genus = 'Asclepias'
	AND B.id is NULL

```

returns:

| scientificName | taxonID |
| --- | --- |
| Asclepias mcvaughii | [https://www.inaturalist.org/taxa/207411](https://www.inaturalist.org/taxa/207411) |
| Asclepias circinalis | [https://www.inaturalist.org/taxa/277429](https://www.inaturalist.org/taxa/277429) |
| Asclepias jorgeana | [https://www.inaturalist.org/taxa/277432](https://www.inaturalist.org/taxa/277432) |
| Asclepias mirifica | [https://www.inaturalist.org/taxa/277434](https://www.inaturalist.org/taxa/277434) |
| Asclepias pratensis | [https://www.inaturalist.org/taxa/277436](https://www.inaturalist.org/taxa/277436) |
| Asclepias pseudorubricaulis | [https://www.inaturalist.org/taxa/277437](https://www.inaturalist.org/taxa/277437) |
| Asclepias scheryi | [https://www.inaturalist.org/taxa/277438](https://www.inaturalist.org/taxa/277438) |
| Asclepias subaphylla | [https://www.inaturalist.org/taxa/277439](https://www.inaturalist.org/taxa/277439) |
| Asclepias vinosa | [https://www.inaturalist.org/taxa/277440](https://www.inaturalist.org/taxa/277440) |
| Asclepias virletii | [https://www.inaturalist.org/taxa/277441](https://www.inaturalist.org/taxa/277441) |
| Asclepias nummularioides | [https://www.inaturalist.org/taxa/277619](https://www.inaturalist.org/taxa/277619) |
| Asclepias zanthodacryon | [https://www.inaturalist.org/taxa/277621](https://www.inaturalist.org/taxa/277621) |
| Asclepias atroviolacea | [https://www.inaturalist.org/taxa/284334](https://www.inaturalist.org/taxa/284334) |
| Asclepias conzattii | [https://www.inaturalist.org/taxa/284336](https://www.inaturalist.org/taxa/284336) |
| Asclepias coulteri | [https://www.inaturalist.org/taxa/284337](https://www.inaturalist.org/taxa/284337) |
| Asclepias crocea | [https://www.inaturalist.org/taxa/284338](https://www.inaturalist.org/taxa/284338) |
| Asclepias fournieri | [https://www.inaturalist.org/taxa/284340](https://www.inaturalist.org/taxa/284340) |
| Asclepias gentryi | [https://www.inaturalist.org/taxa/284341](https://www.inaturalist.org/taxa/284341) |
| Asclepias masonii | [https://www.inaturalist.org/taxa/284343](https://www.inaturalist.org/taxa/284343) |
| Asclepias pellucida | [https://www.inaturalist.org/taxa/284345](https://www.inaturalist.org/taxa/284345) |
| Asclepias puberula | [https://www.inaturalist.org/taxa/284346](https://www.inaturalist.org/taxa/284346) |
| Asclepias schaffneri | [https://www.inaturalist.org/taxa/284348](https://www.inaturalist.org/taxa/284348) |
| Asclepias standleyi | [https://www.inaturalist.org/taxa/284349](https://www.inaturalist.org/taxa/284349) |
| Asclepias bifida | [https://www.inaturalist.org/taxa/284616](https://www.inaturalist.org/taxa/284616) |
| Asclepias leptopus | [https://www.inaturalist.org/taxa/284619](https://www.inaturalist.org/taxa/284619) |
| Asclepias pringlei | [https://www.inaturalist.org/taxa/284622](https://www.inaturalist.org/taxa/284622) |
| Asclepias kamerunensis | [https://www.inaturalist.org/taxa/437303](https://www.inaturalist.org/taxa/437303) |
| Asclepias elegantula | [https://www.inaturalist.org/taxa/503712](https://www.inaturalist.org/taxa/503712) |
| Asclepias barjoniifolia | [https://www.inaturalist.org/taxa/542965](https://www.inaturalist.org/taxa/542965) |
| Asclepias adscendens | [https://www.inaturalist.org/taxa/580329](https://www.inaturalist.org/taxa/580329) |
| Asclepias bicuspis | [https://www.inaturalist.org/taxa/580331](https://www.inaturalist.org/taxa/580331) |
| Asclepias brevicuspis | [https://www.inaturalist.org/taxa/580332](https://www.inaturalist.org/taxa/580332) |
| Asclepias compressidens | [https://www.inaturalist.org/taxa/580334](https://www.inaturalist.org/taxa/580334) |
| Asclepias concinna | [https://www.inaturalist.org/taxa/580335](https://www.inaturalist.org/taxa/580335) |
| Asclepias cooperi | [https://www.inaturalist.org/taxa/580336](https://www.inaturalist.org/taxa/580336) |
| Asclepias crassinervis | [https://www.inaturalist.org/taxa/580337](https://www.inaturalist.org/taxa/580337) |
| Asclepias disparilis | [https://www.inaturalist.org/taxa/580341](https://www.inaturalist.org/taxa/580341) |
| Asclepias dissona | [https://www.inaturalist.org/taxa/580342](https://www.inaturalist.org/taxa/580342) |
| Asclepias expansa | [https://www.inaturalist.org/taxa/580344](https://www.inaturalist.org/taxa/580344) |
| Asclepias fallax | [https://www.inaturalist.org/taxa/580345](https://www.inaturalist.org/taxa/580345) |
| Asclepias fulva | [https://www.inaturalist.org/taxa/580347](https://www.inaturalist.org/taxa/580347) |
| Asclepias gordon-grayae | [https://www.inaturalist.org/taxa/580349](https://www.inaturalist.org/taxa/580349) |
| Asclepias meliodora | [https://www.inaturalist.org/taxa/580353](https://www.inaturalist.org/taxa/580353) |
| Asclepias meyeriana | [https://www.inaturalist.org/taxa/580354](https://www.inaturalist.org/taxa/580354) |
| Asclepias montevaga | [https://www.inaturalist.org/taxa/580355](https://www.inaturalist.org/taxa/580355) |
| Asclepias monticola | [https://www.inaturalist.org/taxa/580356](https://www.inaturalist.org/taxa/580356) |
| Asclepias nana | [https://www.inaturalist.org/taxa/580358](https://www.inaturalist.org/taxa/580358) |
| Asclepias navicularis | [https://www.inaturalist.org/taxa/580359](https://www.inaturalist.org/taxa/580359) |
| Asclepias oreophila | [https://www.inaturalist.org/taxa/580360](https://www.inaturalist.org/taxa/580360) |
| Asclepias patens | [https://www.inaturalist.org/taxa/580361](https://www.inaturalist.org/taxa/580361) |
| Asclepias peltigera | [https://www.inaturalist.org/taxa/580362](https://www.inaturalist.org/taxa/580362) |
| Asclepias praemorsa | [https://www.inaturalist.org/taxa/580363](https://www.inaturalist.org/taxa/580363) |
| Asclepias randii | [https://www.inaturalist.org/taxa/580364](https://www.inaturalist.org/taxa/580364) |
| Asclepias rara | [https://www.inaturalist.org/taxa/580365](https://www.inaturalist.org/taxa/580365) |
| Asclepias schlechteri | [https://www.inaturalist.org/taxa/580366](https://www.inaturalist.org/taxa/580366) |
| Asclepias ulophylla | [https://www.inaturalist.org/taxa/580368](https://www.inaturalist.org/taxa/580368) |
| Asclepias velutina | [https://www.inaturalist.org/taxa/580369](https://www.inaturalist.org/taxa/580369) |
| Asclepias vicaria | [https://www.inaturalist.org/taxa/580370](https://www.inaturalist.org/taxa/580370) |
| Asclepias woodii | [https://www.inaturalist.org/taxa/580371](https://www.inaturalist.org/taxa/580371) |
| Asclepias dregeana | [https://www.inaturalist.org/taxa/596584](https://www.inaturalist.org/taxa/596584) |
| Asclepias melantha | [https://www.inaturalist.org/taxa/622354](https://www.inaturalist.org/taxa/622354) |
| Asclepias alpestris | [https://www.inaturalist.org/taxa/625237](https://www.inaturalist.org/taxa/625237) |
| Asclepias amabilis | [https://www.inaturalist.org/taxa/625238](https://www.inaturalist.org/taxa/625238) |
| Asclepias ameliae | [https://www.inaturalist.org/taxa/625239](https://www.inaturalist.org/taxa/625239) |
| Asclepias candida | [https://www.inaturalist.org/taxa/625264](https://www.inaturalist.org/taxa/625264) |
| Asclepias dependens | [https://www.inaturalist.org/taxa/625274](https://www.inaturalist.org/taxa/625274) |
| Asclepias flanaganii | [https://www.inaturalist.org/taxa/625275](https://www.inaturalist.org/taxa/625275) |
| Asclepias graminifolia | [https://www.inaturalist.org/taxa/625276](https://www.inaturalist.org/taxa/625276) |
| Asclepias grandirandii | [https://www.inaturalist.org/taxa/625277](https://www.inaturalist.org/taxa/625277) |
| Asclepias foliosa | [https://www.inaturalist.org/taxa/625526](https://www.inaturalist.org/taxa/625526) |
| Asclepias inaequalis | [https://www.inaturalist.org/taxa/625528](https://www.inaturalist.org/taxa/625528) |
| Asclepias langsdorffii | [https://www.inaturalist.org/taxa/625530](https://www.inaturalist.org/taxa/625530) |
| Asclepias longirostra | [https://www.inaturalist.org/taxa/625539](https://www.inaturalist.org/taxa/625539) |
| Asclepias longissima | [https://www.inaturalist.org/taxa/625542](https://www.inaturalist.org/taxa/625542) |
| Asclepias minutiflora | [https://www.inaturalist.org/taxa/625560](https://www.inaturalist.org/taxa/625560) |
| Asclepias mtorwiensis | [https://www.inaturalist.org/taxa/625563](https://www.inaturalist.org/taxa/625563) |
| Asclepias nuttii | [https://www.inaturalist.org/taxa/625572](https://www.inaturalist.org/taxa/625572) |
| Asclepias palustris | [https://www.inaturalist.org/taxa/625582](https://www.inaturalist.org/taxa/625582) |
| Asclepias pilgeriana | [https://www.inaturalist.org/taxa/625584](https://www.inaturalist.org/taxa/625584) |
| Asclepias pseudoamabilis | [https://www.inaturalist.org/taxa/625586](https://www.inaturalist.org/taxa/625586) |
| Asclepias pseudofimbriata | [https://www.inaturalist.org/taxa/625588](https://www.inaturalist.org/taxa/625588) |
| Asclepias pygmaea | [https://www.inaturalist.org/taxa/625593](https://www.inaturalist.org/taxa/625593) |
| Asclepias senecionifolia | [https://www.inaturalist.org/taxa/860958](https://www.inaturalist.org/taxa/860958) |
| Asclepias fimbriata | [https://www.inaturalist.org/taxa/1104559](https://www.inaturalist.org/taxa/1104559) |
| Asclepias boliviensis | [https://www.inaturalist.org/taxa/1159860](https://www.inaturalist.org/taxa/1159860) |
| Asclepias schumanniana | [https://www.inaturalist.org/taxa/1188941](https://www.inaturalist.org/taxa/1188941) |

---

<div class="post-metadata">

**Author:** ![optilete](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.inaturalist.org/optilete/32/1908_2.png) [@optilete](https://forum.inaturalist.org/u/optilete)\
**Post date:** [January 29, 2022, 10:59am UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/6 "2022-01-29T10:59:18Z")

</div>

> [@raymie](#):
>
> I’d also like to be able to search for species without common names.

Species without common names for a specified lexicon and a specific country (=checklist?) and common names that occur for more then one species within a lexicon:

_SELECT vnames.vernacularName, vnames.contributor, Taxa.scientificName, vnames.locality, vnames.source, vnames.lexicon, vnames.created, Taxa.taxonID_  
_FROM Taxa INNER JOIN vnames ON Taxa.id = vnames.id_  
\*WHERE \*  
_((_  
\*(vnames.vernacularName) In \*

_(SELECT vnames.vernacularName_  
_FROM Taxa INNER JOIN vnames ON Taxa.id = vnames.id_  
_WHERE taxonRank= ‘Species’_  
_GROUP BY [vernacularName] \*  
 HAVING (Count()\>1 ))_

_))_  
_ORDER BY vnames.vernacularName;_

Interessting file, you can also find out which lexicon has the most common names after English and which user has the first position in the leader board of adding common names…

* * *

Postscript:  
There is a policy for common names and genus and infraspecies. I was wondering what to do with this so I removed them from the result of this query, but that is not correct…  
[https://forum.inaturalist.org/t/duplicating-common-name-for-nominate-subspecies/11298/8?u=optilete](https://forum.inaturalist.org/t/duplicating-common-name-for-nominate-subspecies/11298/8)

---

<div class="post-metadata">

**Author:** ![pisum](https://avatars.discourse-cdn.com/v4/letter/p/91b2a8/32.png) [@pisum](https://forum.inaturalist.org/u/pisum)\
**Post date:** [January 29, 2022, 2:19pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/7 "2022-01-29T14:19:30Z")

</div>

> [@optilete](#):
>
> common names that occur for more then one species within a lexicon

i think this more accurately reflects the above description:

```auto
WITH vnames_multiple_species AS (
	SELECT X.vernacularName, X.lexicon, Y.taxonRank
	FROM vnames X
	JOIN taxa Y
		ON X.id = Y.id
	-- WHERE Y.taxonRank = 'species'
	GROUP BY X.vernacularName, X.lexicon, Y.taxonRank
	HAVING COUNT(DISTINCT X.id) > 1
)
SELECT B.taxonRank, B.scientificName, A.*
FROM vnames A
JOIN taxa B
	ON A.id = B.id
WHERE EXISTS (
    SELECT *
	FROM vnames_multiple_species C
	WHERE A.vernacularName = C.vernacularName
		AND A.lexicon = C.lexicon
		AND B.taxonRank = C.taxonRank
		AND C.taxonRank = 'species' )
ORDER BY B.taxonRank, A.vernacularName, B.scientificName

```

---

<div class="post-metadata">

**Author:** ![jwidness](https://avatars.discourse-cdn.com/v4/letter/j/9d8465/32.png) [@jwidness](https://forum.inaturalist.org/u/jwidness)\
**Post date:** [January 29, 2022, 7:27pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/8 "2022-01-29T19:27:56Z")

</div>

(I moved some posts here since they seemed more in the vein of a tutorial, feel free to edit the title or anything else)

---

<div class="post-metadata">

**Author:** ![optilete](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.inaturalist.org/optilete/32/1908_2.png) [@optilete](https://forum.inaturalist.org/u/optilete)\
**Post date:** [January 31, 2022, 5:50pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/9 "2022-01-31T17:50:46Z")

</div>

> [@pisum](#):
>
> i think this more accurately reflects the above description:

Not for MS Access:  
As others have stated in the comments, Access SQL does not support the _with_ keyword the same way that TSQL does. You can accomplish close to the same thing though by writing the first query and saving it. The saved query can then be referenced in your Access SQL as though it was a table (similar to creating a view in TSQL).  
[https://stackoverflow.com/questions/24970777/how-do-i-use-the-with-statement-in-access](https://stackoverflow.com/questions/24970777/how-do-i-use-the-with-statement-in-access)

---

<div class="post-metadata">

**Author:** ![pisum](https://avatars.discourse-cdn.com/v4/letter/p/91b2a8/32.png) [@pisum](https://forum.inaturalist.org/u/pisum)\
**Post date:** [January 31, 2022, 7:12pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/10 "2022-01-31T19:12:51Z")

</div>

ok. i didn’t realize you were working in Access. Access doesn’t support common table expressions nor count(distinct), and it optimizes queries differently. so i think this would be how i would write the query for Access:

```auto
SELECT B.scientificName, A.*
FROM (
	SELECT D.*, C.taxonRank
	FROM (
		SELECT vernacularName, lexicon, taxonRank
		FROM (
			SELECT DISTINCT X.vernacularName, X.lexicon, Y.taxonRank, X.id
			FROM vnames AS X
			INNER JOIN taxa AS Y
				ON X.id = Y.id
			WHERE Y.taxonRank = 'species'
		) AS Z
		GROUP BY vernacularName, lexicon, taxonRank
		HAVING COUNT(*) > 1
	) AS C
	INNER JOIN vnames AS D
		ON D.vernacularName = C.vernacularName
		AND D.lexicon = C.lexicon
) AS A
INNER JOIN taxa AS B
	ON A.id = B.id
	AND A.taxonRank = B.taxonRank
ORDER BY A.taxonRank, A.vernacularName, B.scientificName

```

it’s not as easy to read (in my opinion) than my earlier query, but i _think_ it accomplishes the same thing (though i didn’t really check results). you could try to recreate this query using nested Access queries, but i’m counting 3 nests here. so it’s probably just easier to open up SQL view in the Query Designer, and write the query directly in there (or copy and paste this).

if you compare this query against the one you wrote, i think you’ll see a few differences. just based on your earlier description of your query, i think this query will more accurately match your description.

---

<div class="post-metadata">

**Author:** ![optilete](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.inaturalist.org/optilete/32/1908_2.png) [@optilete](https://forum.inaturalist.org/u/optilete)\
**Post date:** [February 2, 2022, 9:14pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/11 "2022-02-02T21:14:31Z")

</div>

I should add indexes, it took over 2 hours and I still don’t get an answer with this Q.

---

<div class="post-metadata">

**Author:** ![pisum](https://avatars.discourse-cdn.com/v4/letter/p/91b2a8/32.png) [@pisum](https://forum.inaturalist.org/u/pisum)\
**Post date:** [February 2, 2022, 11:24pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/12 "2022-02-02T23:24:27Z")

</div>

you could try indexing, but i suspect there’s enough data here that you might be operating at the file size limit for Access (in which case, indexing probably won’t help).

you could try reducing the number of records that you import into your database. (for example, only upload the vernacular names for the lexicons that you’re really interested in.)

otherwise, you probably need to use a different database. unless i need server-client architecture, i tend to use SQLite to make simple databases. if you can write SQL on your own (without using a visual designer), then i think [DB Browser](https://sqlitebrowser.org) is a really good application to manage SQLite databases. it has a very simple interface and includes good functionality for importing data.

---

<div class="post-metadata">

**Author:** ![optilete](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.inaturalist.org/optilete/32/1908_2.png) [@optilete](https://forum.inaturalist.org/u/optilete)\
**Post date:** [February 4, 2022, 9:54pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/13 "2022-02-04T21:54:00Z")

</div>

> [@pisum](#):
>
> (for example, only upload the vernacular names for the lexicons that you’re really interested in.)

:-) It’s like going to China and learning Quechua.

I imported only one (the most important) lexicon but all taxa of iNaturalist. If I should import all lexicons I think I should script something in VBA or Python to do it. I did not find another database my company let me install on my laptop but I will take a look at SQLLite.

---

<div class="post-metadata">

**Author:** ![pisum](https://avatars.discourse-cdn.com/v4/letter/p/91b2a8/32.png) [@pisum](https://forum.inaturalist.org/u/pisum)\
**Post date:** [February 5, 2022, 4:03am UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/14 "2022-02-05T04:03:00Z")

</div>

hmmm… if you imported only one lexicon into your Access database and the query still didn’t return anything for you (in a reasonable amount of time), then it’s probably that your computer doesn’t have the resources to handle a big data set like that in Access. i just tried importing taxa and English vnames, and it ran in just few seconds in Access 365 (even without any indexes).

that said, if your query is working for you, it should get you close enough to your intent probably, since your set of vnames includes only one lexicon. so if that’s working for you, then it’s probably good enough in this case.

…

i did a quick web search to refresh my memory about how to import multiple files into an Access table, and it looks like it probably does require some VBA, which is unfortunate. (in DB Browser, i just have to select all the vname csv files at the same time, and the import wizard will load them all into the same table. it takes just a minute or two.)

since you mentioned you can do Python, and since it sounds like you can’t install new apps on your machine, you still might have the ability to run SQL using the sqlite3 module in Python. SQL queries should run faster / more efficiently in SQLite than in Access, but going through Python, it’s more coding to accomplish some of the steps vs going through a DB app. (you can also do SQL-like queries in Python itself without going through SQL, but it’s just ugly to read compared to SQL, in my opinion.)

---

<div class="post-metadata">

**Author:** ![optilete](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.inaturalist.org/optilete/32/1908_2.png) [@optilete](https://forum.inaturalist.org/u/optilete)\
**Post date:** [February 5, 2022, 11:32pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/15 "2022-02-05T23:32:25Z")

</div>

> [@pisum](#):
>
> and it looks like it probably does require some VBA, which is unfortunate

I was afraid for it so I tried an ms dos line that does the trick: copy VernacularNames-\*.csv VernacularNames\_all\VernacularNames\_all.txt /on

---

<div class="post-metadata">

**Author:** ![pisum](https://avatars.discourse-cdn.com/v4/letter/p/91b2a8/32.png) [@pisum](https://forum.inaturalist.org/u/pisum)\
**Post date:** [February 6, 2022, 12:25am UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/16 "2022-02-06T00:25:41Z")

</div>

smart

---

<div class="post-metadata">

**Author:** ![optilete](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.inaturalist.org/optilete/32/1908_2.png) [@optilete](https://forum.inaturalist.org/u/optilete)\
**Post date:** [February 15, 2022, 11:38pm UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/17 "2022-02-15T23:38:31Z")

</div>

After all this work I guess a GUI is implemented to browse the history for each taxon…

* * *

Probably another history, accoording to the error message  
‘‘History was introduced in February 2022, so no history is available prior to that date.’’

---

<div class="post-metadata">

**Author:** ![optilete](https://sea2.discourse-cdn.com/flex016/user_avatar/forum.inaturalist.org/optilete/32/1908_2.png) [@optilete](https://forum.inaturalist.org/u/optilete)\
**Post date:** [February 6, 2023, 11:58am UTC](https://forum.inaturalist.org/t/using-sql-to-query-inats-dwca-taxonomy-export/29377/18 "2023-02-06T11:58:32Z")

</div>

Results of DWCA can be found:

[The state of common names feb 2023](https://forum.inaturalist.org/t/the-state-of-common-names/39118)  
[Finding out common names’ numbers per language 2020 & jan 2023](https://forum.inaturalist.org/t/finding-out-common-names-numbers-per-language/17902)  
[Clean up currently available lexicons](https://forum.inaturalist.org/t/clean-up-currently-available-lexicons/12453)

* * *

and for the post above, I cannot edit??  
[taxon history is live now](https://forum.inaturalist.org/t/taxon-history-is-now-live/30008)