Q-01
Patent horizon
“Which active ingredients have listed patent expirations in the next 24 months? Group by ingredient and show every underlying application and patent row.”
DWG 00 · PUBLIC DATA ANALYSIS BLUEPRINT · REV 1.0
Analyze the FDA Orange Book with ChatGPT or Claude
No custom application. No database setup. No SQL required. Download the public files from FDA, attach them to an AI assistant, ask your question, and validate the work.
Retirement note · FDAcharts
FDAcharts was a demo of natural language → SQL → Orange Book charts. General-purpose AI assistants can now analyze the source files directly, so the demo has been retired and replaced by this reproducible guide.
Process diagram AScale 1:1
01
FDA
ZIP
02
UNZIP
3 TXT
03
UPLOAD
AI CHAT
04
VERIFY
ANSWER
PROJECT
ORANGE BOOK AI
STATUS
PUBLISHED
SQL
OPTIONAL
SHEET
00 OF 06
SOURCE
FDA.GOV
TOOLS
GPT / CLAUDE
CHECK
HUMAN
DATE
2026-07-21
General note: plausible output is not validated output. Preserve the model's code, assumptions, and source rows.
DWG 01 · PROCEDURE
The four-step workflow
- GET
Download from FDA
Open the official Orange Book Data Files page, download the current compressed file, and unzip it. Start from FDA each time so your analysis is tied to the current release.
- ADD
Upload the text files
Attach Products.txt, Patent.txt, and Exclusivity.txt to a new ChatGPT or Claude conversation. They are tilde-delimited text files, not ordinary comma-separated CSVs.
- ASK
Define the analysis
Tell the model what decision or question you are exploring. First require a file and column inventory, date parsing checks, and a proposed analysis plan.
- CHK
Verify the result
Review the joins, row counts, filters, duplicate handling, and generated code. Spot-check important records against the official Orange Book Search.
| Item | File | Use it for |
|---|---|---|
| 01 | Products.txt | Products, ingredients, dosage forms, applicants, application identifiers, approval dates, RLD/RS and TE fields |
| 02 | Patent.txt | Listed patents, expiration dates, use codes, claim flags, delist-request flags and submission dates |
| 03 | Exclusivity.txt | Exclusivity codes and expiration dates associated with application products |
FDA describes all three as ASCII text files delimited by a tilde (~). The durable starting point is the FDA data-files page, which also defines every field.
Scope note A · What it covers
Approved products and equivalence data
The Orange Book's formal title is Approved Drug Products with Therapeutic Equivalence Evaluations. FDA describes it as a source for drug products approved on the basis of safety and effectiveness, together with therapeutic-equivalence evaluations and related patent and exclusivity information. That makes it useful for questions about applications, products, ingredients, dosage forms, routes, applicants, reference status, listed patents, and exclusivity records.
Read FDA's Orange Book overview ↗Scope note B · What it does not prove
A listed date is not a launch forecast
These files do not contain the full regulatory history, current labeling, clinical evidence, litigation posture, supply status, commercial strategy, or every event that may affect market entry. A patent or exclusivity expiration date is a reported data point, not a prediction that a generic will launch on that day. Phrase conclusions narrowly, keep the download date with the result, and consult the current FDA record and appropriate specialists for consequential decisions.
DWG 02 · CONTROL PROMPT
Start with structure, not the answer
A vague prompt invites a polished guess. This prompt forces the assistant to inspect the data's grain, parsing, joins, and exceptions before it calculates anything.
Operating instruction
Paste the prompt after attaching the files. Replace the final bracketed line with your question. Continue in the same conversation so corrections retain the file context.
Ask for code even if you never intend to write code. It is the shortest audit trail between the question and the answer.
starter_prompt.txtReady to paste
You are analyzing the current FDA Orange Book data files I attached. Before answering my question: 1. Inventory every file, delimiter, column, row count, and date field. 2. Explain what one row represents in each file. 3. Do not assume joins. Propose and test the composite join keys first. 4. Report duplicate keys, unmatched rows, nulls, and unparseable dates. 5. State whether each result counts rows, products, applications, patents, or exclusivities. For every answer: - cite the source file(s) and column(s); - show the filters and date assumptions; - show the SQL or code used; - include the underlying records or a reproducible result table; - flag uncertainty instead of filling gaps. My question is: [REPLACE WITH YOUR QUESTION]
DWG 03 · EXAMPLE QUERIES
Questions worth asking
Q-02
Approval trend
“Compare annual NDA and ANDA product approvals. Explain whether you are counting applications, products, or rows, and exclude non-date approval values explicitly.”
Q-03
Portfolio view
“For each applicant, count distinct applications, products, listed patents, and unexpired exclusivities. Show the join cardinality and rank the top 20.”
Q-04
Dosage and route
“Show the most common dosage form and route combinations by ingredient, separating prescription, OTC, and discontinued products.”
Q-05
Exclusivity map
“Summarize unexpired exclusivity codes by year and ingredient. Keep the code as reported and link each aggregate back to its underlying application products.”
Q-06
Data quality
“Profile all three files: row counts, null rates, duplicate composite keys, unparseable dates, unmatched joins, and fields whose values need interpretation.”
Detail B · Under the hood
The SQL did not disappear
The assistant may load the files into Python, a dataframe, SQLite, DuckDB, or another analysis environment, then parse, join, filter, group, and chart the records. You no longer need to hand-write the SQL, but asking to see the code remains essential when the result matters.
DWG 04 · QUALITY CONTROL
Validate before you rely
- QC-01
Release
Record when you downloaded the files. FDA says the downloadable data are updated monthly.
- QC-02
Delimiter
Confirm the model parsed the files with a tilde (~), not a comma or tab.
- QC-03
Grain
Define what one row represents in each file before counting anything.
- QC-04
Join
Use the application type, application number, and product number together; inspect one-to-many matches.
- QC-05
Dates
Keep approval dates, patent expiration dates, exclusivity dates, and patent submission dates distinct.
- QC-06
Counts
Ask for both total rows and distinct products/applications so duplicated joins are visible.
- QC-07
Evidence
Require source file names, columns, filters, and generated code or SQL with every substantive answer.
- QC-08
Spot check
Verify a sample of important records in the official FDA search before using the result.
DWG 05 · TOOL SELECTION
ChatGPT or Claude?
Either is a reasonable starting point. Use the assistant already approved for your work, and judge the analysis by its reproducibility rather than the logo above the chat.
Tool A
ChatGPT
OpenAI documents attaching data files, inspecting columns, running calculations, and creating visualizations. Ask it to preserve the code and export a result table alongside its narrative.
Tool B
Claude
Anthropic documents TXT and CSV uploads, among other file types. Ask Claude to use code execution when available and to return its assumptions, code, and intermediate checks.
File capabilities and limits vary by product, plan, model, and date. Check the linked product documentation if an upload is rejected. Do not add confidential company data unless your approved environment and policy allow it.
DWG 06 · RFI REGISTER
Frequently asked questions
RFI-01Do I need SQL to analyze the Orange Book?
No. ChatGPT and Claude can inspect the files and perform the filtering, joining, aggregation, and charting from natural-language instructions. For important work, ask the model to show the SQL or code it used so the analysis can be reviewed.
RFI-02Which Orange Book files should I upload?
The FDA download currently unpacks into Products.txt, Patent.txt, and Exclusivity.txt. Upload all three when your question crosses product, patent, and exclusivity data. A product-only question may need only Products.txt.
RFI-03Can I trust the first answer?
Treat it as an analysis draft, not a verified fact. Require the model to report source files, columns, row counts, filters, join keys, and duplicate handling. Then spot-check material findings in the official FDA Orange Book Search.
RFI-04Why was the original FDAcharts demo retired?
FDAcharts demonstrated natural-language-to-SQL analysis of Orange Book data. General-purpose AI assistants can now work directly with the public files, making a dedicated demo unnecessary. This guide preserves the useful workflow without maintaining a separate application.
RFI-05Does an Orange Book patent expiration predict generic launch?
No. Patent and exclusivity data are useful research inputs, but they do not by themselves predict the timing of generic approval or launch. Legal, regulatory, commercial, and product-specific factors can all affect timing.
RFI-06Is this regulatory, medical, or legal advice?
No. This is an educational data-analysis workflow. Use current official FDA records and qualified regulatory, medical, or legal review for decisions that carry material consequences.
Primary references
- FDA — Orange Book Data Files ↗
- FDA — Orange Book Search ↗
- OpenAI — Analyze a data export ↗
- Anthropic — Upload files to Claude ↗
Published 2026-07-21 · Source links checked 2026-07-21 · Educational use only · FDAcharts application retired