Changes

[[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)]].

- --------------+---------+-----------

patent_2015 | patentdata --------------+-------------- orgname | assignee city | city country | country patentnumber | patent state | state

Column | Type | Modifiers ---------------+-------------------+----------- lastname | character varying | firstname | character varying | address | character varying | postcode | character varying | orgname | character varying | city | character varying | country | character varying | patentnumber | integer | state | character varying | patentcountry | character varying | nationality2 | character varying | residence | character varying | asgseq | integer | asgtype | integer |

SELECT patent, kind, gdate, 'NULL', 'NULL', NULL, NULL, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', -1, 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', claims, apptype, appnum, gyear, appdate, appyear FROM patents

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. '''Script:'''  /* creating patent data tables from : https://bulkdata.uspto.gov/data2/patent/maintenancefee/*/

CREATE TABLE \COPY 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

\COPY PatentMaintenanceFee FROM ' /bulk/USPTO_Consolidated* creating tables for historical patent data - USPTO */MaintFeeEvents_20160613.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;-- RESULT : COPY 14042059

CREATE TABLE HistoricalPatentData(applicationid int \COPY historicalpatentdata FROM '/bulk/USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ',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)' HEADER NULL AS '' CSV;

\ --COPY historicalpatentdata FROM '/bulk/USPTO_Consolidated/HistoricalFiles/historical_masterfile.csv' DELIMITER AS ',' HEADER NULL AS '' CSV;11191813