7,612
edits
Changes
Jump to navigation
Jump to search
[[Category:Internal]]{{Project|Has project output=|Has sponsor=McNair Center|Has title=Patent Data Processing - SQL Steps|Has owner=Shelby Bice,|Has project status=Subsume|Has keywords=Tool}}Return to [[Internal Classification::Legacy| Patent Data (Wiki Page)]].
ScriptsThe USPTO has a repository of patent data on their Bulk Data Storage system. We have this data downloaded and loaded into a table on the patent database. Here are the steps followed:* Download file from the BDS system - we have access to CSV files.* Create table with required specs* use the \COPY command to copy the data from the file into the table. Script follows.
/* creating patent data tables from '''Script: https://bulkdata.uspto.gov/data2/patent/maintenancefee/*/'''
CREATE TABLE PatentMaintenanceFee(patentnumber varchar,applicationnumber int,smallentity varchar,filingdate date,grantissuedate date,maintenancefeedate date,maintenancefeecode varchar); /* creating patent data tables from : https://bulkdata.uspto.gov/data2/patent/maintenancefee/*/
\COPY CREATE TABLE PatentMaintenanceFee FROM '/bulk/USPTO_Consolidated/MaintFeeEvents_20160613.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV( patentnumber varchar, applicationnumber int, smallentity varchar, filingdate date, grantissuedate date, maintenancefeedate date, maintenancefeecode varchar );-- RESULT : COPY 14042059
CREATE TABLE HistoricalPatentData(applicationid int,pubno varchar,patentnumber varchar,NBER int,USPC varchar,USPC_sub varchar,applicationdate date,prioritydate date,pubdate date,displaydate date,disptype varchar,exp_dt date,exp_dt_max date,pta int); /* creating tables for historical patent data - USPTO */
\COPY historicalpatentdata FROM '/bulk/USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ' CREATE TABLE HistoricalPatentData( applicationid int,' HEADER NULL AS '' CSV pubno varchar, patentnumber varchar, NBER int, USPC varchar, USPC_sub varchar, applicationdate date, prioritydate date, pubdate date, displaydate date, disptype varchar, exp_dt date, exp_dt_max date, pta int );
Patent Data Processing - SQL Steps (view source)
Revision as of 13:34, 21 September 2020
, 13:34, 21 September 2020no edit summary
== Objective ==
== USPTO Consolidated Patent Data ==
\COPY PatentMaintenanceFee FROM '/* creating tables for historical patent data bulk/USPTO_Consolidated/MaintFeeEvents_20160613.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- USPTO */RESULT : COPY 14042059
\COPY historicalpatentdata FROM '/bulk/USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ',' HEADER NULL AS '' CSV; --COPY 11191813 [[Category:Internal]][[Internal Classification::Legacy| ]][[Category:Patent]]
