154
edits
Changes
Jump to navigation
Jump to search
Patent Data Processing - SQL Steps (view source)
Revision as of 15:51, 17 June 2016
, 15:51, 17 June 2016no edit summary
CLONING:
CREATE DATABASE allpatentsProcessed WITH TEMPLATE allpatent OWNER researcher;
== 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
