load_patstat/insert_data_to_patstat.sh at master · tsak/load_patstat · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
#!/bin/bash

# Copyright (c) 2015 Altay Özaygen

# this script has tree parts
# 1- creating tables in PATSTAT
# 2- inserting data into the database by using zip files obtained from CDs.
# 3- creaing indexes in PATSTAT. This one is taking more time than the
#    second step.

if [ -z "$1" ]; then
  echo "Please run $0 <dbname> [<schema>]";
  exit 0;
fi

DB=$1

if [ ! -z "$2" ]; then
    PGOPTIONS="--search_path=$2"
    export PGOPTIONS
fi

# control wheather all other files are present
if [ ! -e ./create_patstat_tables.sql ]; then
    echo "There is no create_patstat_tables.sql file!"
    exit 0
fi

if [ ! -e ./create_patstat_keys.sql ]; then
    echo "There is no create_patstat_keys.sql file!"
    exit 0
fi

# creating tables within the PATSTAT database
psql "$DB" < ./create_patstat_tables.sql

read -e -p "Enter the path to the zip files: [/path/to/patstat/zip_files]" ZIP_FILES_DIR

TMP_DIR=$ZIP_FILES_DIR/tmp

if [ ! -d $TMP_DIR ]; then
    mkdir $TMP_DIR
fi

# list of tables to be filled
table_list="
tls201_appln
tls202_appln_title
tls203_appln_abstr
tls204_appln_prior
tls205_tech_rel
tls206_person
tls207_pers_appln
tls209_appln_ipc
tls210_appln_n_cls
tls211_pat_publn
tls212_citation
tls214_npl_publn
tls215_citn_categ
tls216_appln_contn
tls222_appln_jp_class
tls223_appln_docus
tls224_appln_cpc
tls226_person_orig
tls227_pers_publn
tls228_docdb_fam_citn
tls229_appln_nace2
tls230_appln_techn_field
tls231_inpadoc_legal_event
tls801_country
tls803_legal_event_code
tls901_techn_field_ipc
tls902_ipc_nace2
tls904_nuts
tls906_person"

# control if there are zip files in the given directory
count=`ls $ZIP_FILES_DIR/*.zip 2>/dev/null | wc -l`
if [ $count == 0 ]; then
    echo "There is no zip file in ", $ZIP_FILES_DIR
    exit 0
fi

for table_name in $table_list
do
   echo $table_name
   # extract the tlsXXX part from the table name
   base_name="${table_name:0:6}"
   echo $base_name

   # for files starting tlsXXX_partXXX.gzip
   for file in $ZIP_FILES_DIR/$base_name*
   do
       # file tlsXXX_partXXX.zip is unziped in a temporary directory
       echo "unziping $file"
       gunzip -c $file > $TMP_DIR/file_to_be_inserted.csv
       psql -c "\COPY $table_name from '$TMP_DIR/file_to_be_inserted.csv' DELIMITER AS ',' CSV HEADER QUOTE AS '\"' " "$DB"
       echo "INSERTED $file"
   done
done

# cleaning the rest
rm $TMP_DIR/file_to_be_inserted.csv
rmdir $TMP_DIR

# creating index, it will take very long hours, don't despair :)
psql "$DB" < ./create_patstat_keys.sql