You can contact me with your questions or suggestions by sending an e-mail message.
This index page is arranged in sections: Permanent and Transitory items.
EPA Chemical Dictionary. New Entries
New entries in the PPIS file of 06 November 2017. PPISDICT.xlsx..(November 7)
EPA. New (Sub)Registration(s) Approvals: 06 November 2017
Added to PPIS files of 06 November.
Newest Registration(s) Approvals. Excel file APPR1716.xlsx has: AIs and Pcts, Registrant, Product Name, EPA Reg. No., link to PPLS for the product label. Note: If the link leads to No Label Found you will have to query PPLS later for the label.
Newest Transferred Registrations. None in this file set.
Newest SubRegistrations. Excel file SUBS1716.xlsx...(November 7)
Database, Word, Excel, etc. Searches. Formal, very year old, Logic
Are Access or other database Queries/Questions confusing? If so, a simple explanation. All programs which offer search options use Formal Logic constructs which go back to the 19th Century. The constructs were developed to ask questions in a structed manner. Hence, cutting to the chase: "Is/Like, And, Or, Not, Starts With, Anywhere" have been taken up by programmers because they are explicit and can be coded as program instructions.
Even more, about Windows. In Access database a common, simple way to do such as an Update is through the Builder (saves typing, using mostly clicks). Several instructions are a hold over from the earliest days of programming. Without giving examples, the old instructions include Left$ (grab stuff from the start of...); Mid$ (grab stuff from somewhere in the thing); Right$ (grab stuff stating at the end of the thing). "So simple only a child could do it", as Tom Lehrer sung about New Math. How else could I have easily deconstructed the, below, EPA/PPIS Site Codes...(November 2)
Using Three Parts of EPA's Site Code
On occasion I've wanted to know about products and/or registrants where labels have a group of related Sites. EPA's PPIS Site code files allow for efficient searching of related Sites. Useful you?
Site Code Parts. There are: 72 Categories; 3377 SubCategories; 79 Qualifiers. Substantial reduction of 30054 individual Sites.
Extracted Site Code Parts. Excel file Site_1_7.xlsx has complete Site Code and the three parts of the Site Code. The three parts, if not obvious, are: Digits 1-2, Digits 1-5, Digits 6-7. Note for Categories 1-9, I delete (let Access ignore) the leading zero.
Site Category Name. Excel file Site_S2N.xlsx has the Category number and name. Some names are mine, some lifted from the PPIS file.
Site SubCategory Name. Excel file Site_S5N.xlsx has the SubCategory Name and number.
Site Qualifier Name. Excel file Site_S67N.xlsx has the Qualifier Name and number. FYI: names are generally the same as the parenthetical part of the complete Site Name.
Example. Syngenta's Ornamental Perennial Grasses. I want to know "How many labels does Syngenta have for each Ornamental Grass". Screen shot XMPL_5A.jpg shows my Query. I looked at the list of S5 names to find the SubCategory (33000 - 33999). I know Syngenta's CoNr, but could have looked that up in the Company Address table. The Query returns 775 RegNr and Site Name, which I save in a table. Screen shot XMPL_5B.jpg shows my Query asking Access to Count RegNr for each Ornamental. Excel file XMPL_5C.xlsx is the result...(November 2)
Palmer Amaranth. A Troublesome and Common Weed
One result of a 2016 survey by Weed Science Society of America is that Palmer Amaranth appears on the list of Most Troublesome and the list of Most Common weeds for selected Broadleaf crops, Fruits, and Vegetables.
Sites. Excel file PalmerA1.xlsx has Count of RegNr for Sites on labels which name Palmer Amaranth as a weed.
Registrants. Excel file PalmerA2.xlsx has Count of RegNr for Registrants labelling for Palmer Amaranth as a weed.
AIs. Excel file PalmerA3.xlsx has All_AI and Percentage for labels naming Palmer Amaranth as a weed.
Three Registrants. Excel file PalmerA4.xlsx has Count of Registrations for each All_AI and Percentage on labels by FMC (CoNr 279), BASF (CoNr 7969), and Du Pont (CoNr 352) which name Palmer Amaranth as a weed.
All Weeds. Excel file PalmerA5.xlsx has Count of Registrations for every weed on labels on which Palmer Amaranth is named.
Three Registrants. Same Weeds Excel file PalmerA6.xlsx has for the three Registrants above, the SAME WEEDS on at least some labels of each Registrant WHEN Palmer Amaranth is a named weed on each Registrant's label...(October 31)
Two Screen Shots. Using EPA's PPIS text files
This posting is for those considering doing PPIS text files with a database, such as Access, but don't want to start with my tedious Audio-Visual tutorials way below. Others might want to see how things are done, even if no interest in doing a database.
All I will say here about PPIS files and Access is that PPIS text files once stuffed into Access are NO DIFFERENT in principle than databases used by: your employer, your bank, your credit card company, NPIRS, state or federal agencies, or big data miners, etc., etc., etc.
Relating/Connecting PPIS data. This screen shot PPIS_Tbl.jpg shows relation/connection between and among Access/PPIS tables (Excel analogy: Sheets) which I most often use.
You will notice after your eyes initially glaze over, the many lines between Active Regs and other Tables. A product's Registration Number is the most common way to extract information. Important. A feature of a relational database is that using an item, or items, common to Tables you can extract ANY INFORMATION regardless of where data is stored.
FYI. I set up this screen shot as an Access Query because that was convenient. No one would ever Run this Query.
A practical example. Suppose you, or someone in your organization, asked for all Dicamba registrations. Screen shot PPISXMPL.jpg shows How-To.
Notice that Registration Number and Company Number come out of the Active Regs table. No problem for Access. Notice also in the Field entries Access tells you which item is extracted from which data table. Mix and choose however is convenient or strikes you at the moment. FYI: 547 returns if you were to Run this Query.
Queries. Just in case. Queries (qry...) are those I use every time I post What's New about registrations. They're really just macros by another name. Starting a Query? On the Access (2007) ribbon bar: click on Create, then Query Design. Access pops up a list of all the Tables in your database. Click on those you want put into the Query box. Connect/relate Tables by click-drag-drop the common item(s). FYI: I typed dicamba*, Access did the rest. I told Access to find all registrations where dicamba is the first part of the AI Common Name AND anything after. Hence, Access returns for dicamba acid and every dicamba salt...(October 27)
More Pesticide Foreign Establishments
I just found the EPA web site with National List of Foreign and Domestic Pesticide Establishments. There 138 which are NOT on my posting below. Format is close to what I posted, except for the Establishment Number (is as appears on product labels). Excel file Est_More.xlsx has the 138...(October 24)
EPA. New (Sub)Registration(s) Approvals: 23 October 2017
Added to PPIS files of 23 October.
Newest Registration(s) Approvals. Excel file APPR1715.xlsx has: AIs and Pcts, Registrant, Product Name, EPA Reg. No., link to PPLS for the product label. Note: If the link leads to No Label Found you will have to query PPLS later for the label.
Newest Transferred Registrations. Excel file XFER1715.xlsx.
Newest SubRegistrations. Excel file SUBS1715.xlsx...(October 24)
File All_Subs,xlsx has: Sub From, Sub To, Pct AI, All AI, Prod Name, EPA Reg. Nr., Dist Nr, Dist Status. FYI. Dist Status column has Alternate Brand Names indicated...(October 21)
Pesticide Foreign Establishments
The file below is my compilation of Foreign Pesticide Establishments.
FYI and caution. I can guarantee my list is neither complete nor up to date. Also, there are duplicate entries because back when EPA used a 2 letter code for Country but later switched to a 3 letter code.
Excel file NotUSEst.xlsx. Should you not be familiar with How-To. Get from Google a link to FOIA Online, then, after choosing EPA as the Agency, begin guessing at approriate search term(s).
Hint. For much you want to add to your search term EPA-HQ and even better are EPA-HQ-2016 or EPA-HQ-2017. Elsewise Years back to 2012 will show up...(October 19)
Pests on Labels. Registrations/Registrant/Pest Type
Excel file Pest_Co.xlsx has Count of Registrations for each Registrant, and Total Registrations..
I've abbreviated Pest Type to save space. DO = Disinfectant Organism; F = Fungus; I = Invertebrate; ILS = Invertebrate Life Stage; N = Nematode P = Plant; PRE = Plant Preemergence; Reg = Plant Regulator; V = Vertebrate.
Total is NOT the sum of Registrations in each row. I separated parts of Invertebrate and Plant labels, for I hope clarity. This would result in double counting Registrations. My Total is Count of Registrations for Unique Values of CoNr - RegNr for each Registrant.
For Access database users as an FYI or just in case. I used the usual Access operations Table Design and Make Table. Generating the file above I used these Access built-in operations/functions (i.e., click on): Unique Value, Count, Builder, Update...(October 18)
Pests on Labels. Example: Using Product Approval Date
A stretch just in case for anyone doing a dayabase and PPIS. Example of combining a Pestxxx file and a PPIS file.
Question: Who has most recently registered a Sulfentrazone product for a Plant/Weed pest.
This screen shot Sulfen.jpg shows my Query. I don't bother to show the result.
Should you have a question about this, or any other Query with Access, just ask...(October 16)
Pests on Labels
I used PPIS files of 02 October 2017 for this post.
Format. Each file below has the same format:
EPA Reg. No. - CoNr - Co Name - Pest Name - PctAI - All_AI - EPA's pest code
Be patient. I and P files can took a while to load to your screen.
Disinfectant Organism. Excel file Pest_D.xlsx.
Fungus. Excel file Pest_F.xlsx.
Invertebrate, other than with Life Stage Qualifier. Excel file Pest_I.xlsx.
Invertebrate with Life Stage Qualifier. Excel file Pest_ILS.xlsx.
Nematode. Excel file Pest_N.xlsx.
Plant, other than PREEMERGENCE or REGULATOR. Excel file Pest_P.xlsx.
Plant, PREEMERGENCE. Excel file Pest_PRE.xlsx.
Plant, REGULATOR. Excel file Pest_REG.xlsx.
Vertebrate. Excel file Pest_V.xlsx...(October 16)
Weed Pests. (Mostly) Dicamba Combinations
My last posting on this topic. Using in a teeny, tiny, way for obvious to you results from my postings below. Again, merely a suggestion about using EPA's PPIS files.
Individual AIs. Excel file Comb_01.xlsx has for each AI in a Combination: How many Combinations, Registrations, Registrants.
Dicamba Acid or Salt are used in 115 Combinations. Remainder of this posting is about Dicambas.
Dicamba Combinations. Excel file Comb_02.xlsx has all the Dicamba Combinations.
AIs. Excel file Comb_03.xlsx has the individual AIs found in Dicamba Combinations.
Weeds on Dicamba Labels. Excel file Comb_04.xlsx has: How many Dicamba combinations for each of my (see below) 603 selected Weeds.
You don't need to look. The answer is 583 of the 603. Might not mean anything but... of the 20 No Label Weeds 19 are PREEMERGENCE. However, there are PREEMERGENCE Weeds on Dicamba labels.
HENBIT. I choose HENBIT as a most labeled Weed by Dicambas. Excel file Comb_05.xlsx has: Registrant and Dicamba Combination for label naming HENBIT. Note: There are 154 entries here while I showed above 115 Dicamba combinations. Obvious answer is that more than one Registrant registers the same Combination.
Henbit. Dicamba Combinations. Label Sites. Although EPA's PPIS files do not allow a direct association of label Pests and Sites, results can be suggestive. Excel file Comb_06.xlsx has: How many Dicamba product Registrations for HENBIT pest label Sites. Surely obvious to those who know/care that 84% of the Sites are Non-Crop (of course not obvious to me until I saw the results)...(October 15)
Weed Pests. AI Combinations. 2
In this posting I show the data I'll use later asking about Who/What. Database and PPIS users might get a suggestion about something to do with PPIS. Excel users might be interested in skimming my files.
Combinations. Excel file WeedComb.xlsx has: my Combination ID, Combinations, Count of Registrations, Registrants, label Weeds.
Each AI. Excel file Weed_AI.xlsx has: my Combination ID, my ID for each AI in the Combination, AI common name.
Weeds. For this exercise I selected 603 Weeds, those with 50 or more labels. Excel file Weed603.xlsx has: my Weed ID, EPA's pest code, Weed name, Count of Registrations, Registrants, Combinations, AIs in Combinations.
AI Trends. Excel file Weed93AI.xlsx has: my AI ID, AI common name, Count of Combinations registered in one or more of years 2017, 2016, 2015.
Database users will note that I can connect (relate) data types via my IDs (and product RegNr which I don't show here)...(October 9)
EPA. New (Sub)Registration(s) Approvals: 2 October 2017
Added to PPIS files of 2 October.
Newest Registration(s) Approvals. Excel file APPR1714.xlsx has: AIs and Pcts, Registrant, Product Name, EPA Reg. No., link to PPLS for the product label. Note: If the link leads to No Label Found you will have to query PPLS later for the label.
Newest Transferred Registrations. Excel file XFER1714.xlsx.
Newest SubRegistrations. Excel file SUBS1714.xlsx...(October 3)
EPA. New (Sub)Registration(s) Approvals: 15 September 2017
Added to PPIS files of 15 September.
Newest Registration(s) Approvals. Excel file APPR1713.xlsx has: AIs and Pcts, Registrant, Product Name, EPA Reg. No., link to PPLS for the product label. Note: If the link leads to No Label Found you will have to query PPLS later for the label.
Newest Transferred Registrations. Excel file XFER1713.xlsx.
Newest SubRegistrations. Excel file SUBS1713.xlsx...(September 16)
PPIS: All Companies
EPA has updated its company-full.txt PPIS file. Entries are for 2005 (last revision to PPIS file formats) to September 2016. There are now 84031 entries with information about entities doing something with pesticides which requires an EPA Company Number.
In case you have not used previous versions and want to import the fixed-width file into a database or spreadsheet, below are field names I use, starting position on each line for each field, and the field width (number of reserved spaces).
Co_NR: Start = 1, width = 6. Co_Name: Start =7, width =60. Co_Name2: Start = 67, width = 60. Co_Street: Start = 127, width = 50. Co_City: Start = 177, width = 50. Co_State: Start = 27, width = 2. Co_Zip: Start = 229, width = 5. Co_Contact: Starrt = 234, width = 40. Co_Phone: Start = 274, width =10...(October 6)
Foreign Pesticide Production Establishments. Y2014
Excel file SSTS2014.xlsx is my extract from FOIA requesr EPA-HQ-2015-006206 for reporting year 2014...(May 2015)
Foreign Pesticide Production Establishments. Two Summaries
Files below are from my collection of EPA's SSTS (Section Seven Tracking System) reports for 2004, 2005, 2006, 2011, 2012, 2013.
Note: Both files contain some duplicates.
Note: EPA's Excel format files for 2011-2013 are posted on the web. Go to the FOIA search site. Search for: EPA-HQ-2014-005123.
Establishments. Excel file EstNotUS.xlsx.
Products. Excel file SSTSPROD.XLS.
Country Code-Name. If not familiar with country codes, converted to names at http://www.epa.gov/compliance/resources/publications/monitoring/fifra/fgnestablishments.html...(April 2013)
Playing A-V files. Read this first.
Tutorial sessions move from finding EPA Reg. No. for latest label image additions to PPLS up to creating a browser-ready file such as this one, which has Registrant, Product, Actives information and with links to PPLS to get Label Images.
Access database Templates.
Access 2007. Zip file PPIS7TMP.zip extracts to PPIS7TMP.accdb. As of June 2014 this is the entire Template database I am using each month with EPA-OPP's PPIS text files. Minimal explanations below. Feel free to ask me "How-To, Say-What?, etc.).
Access Template database has 31 Tables. 18 Tables are Designed to receive EPA's PPIS Fixed-Width text (*.txt) files. For each PPIS Table I have an Import Specification (use the Advanced tab and then the Spec tab after telling Access which text file to append to which data table). Other Tables are those I use and each is filled in the Template. Again, ASK if any interest.
Quick and Simple: One File Import. Download then Import PPIS product.txt file. Run (double-click) qry01, qry02, qry03. Voila! tblActiveRegs has the currently Active Registrations.
Source. CDPR's data tables and descriptions of table contents is found at CDPR Database stuff.
Note: My Template databases have ONLY 8 Tables for CDPR data. There are many more, such as label Pests and Sites.
Note: CDPR's text data files are very much like EPA's for PPIS: Fixed-Width file format, except CDPR uses the 3 letter extension "dat" rather than EPA's "txt" file name extension. Importing data files and using Queries is the same as with PPIS files.
Import Specifications. Excel file CDPR_imp.xls tells you into which Access Table to Append each of the 8 CDPR Fixed-width text files and which Import Specification to use.
Queries. Excel file CDPR_qry.xls tells you what each of the 8 Queries does. Do them in numerical order (else funny results or a dreaded ERROR message). I.e., qry01, qry02, qry03, etc.
For further information, or to discuss a specific project, contact Bruce
e-mail your request to firstname.lastname@example.org (that's email@example.com).
All original materials are copyright © 1996 - 2011 by Bruce M. McKay, and all rights are reserved.
The URL for this page is http://www.bmckay.com