154
edits
Changes
Jump to navigation
Jump to search
Patent Data Processing - SQL Steps (view source)
Revision as of 12:46, 6 July 2016
, 12:46, 6 July 2016→USPTO Consolidated Patent Data
== USPTO Consolidated Patent Data ==
Scripts:
/* creating patent data tables from : https://bulkdata.uspto.gov/data2/patent/maintenancefee/*/
CREATE TABLE PatentMaintenanceFee( patentnumber varchar, applicationnumber int, smallentity varchar, filingdate date, grantissuedate date, maintenancefeedate date, maintenancefeecode varchar );
\COPY PatentMaintenanceFee FROM '/bulk/USPTO_Consolidated/MaintFeeEvents_20160613.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV; -- RESULT : COPY 14042059
/* creating tables for historical patent data - USPTO */
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 );
\COPY historicalpatentdata FROM '/bulk/USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ',' HEADER NULL AS '' CSV;
--COPY 11191813
