02_import_data_files.sql

--- IMPORT UNZIPPED DATA FILES ---
-- cdanne@diw-econ.de
-- 2023-10-10

--- CHANGE DB ---
USE patstat2023a;

--- CREATE TABLES ---
SET autocommit=0;
SET unique_checks = 0;
SET foreign_key_checks = 0;

--- tbl201 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls201_part01.csv'  
INTO TABLE tls201_appln
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls201_part02.csv'  
INTO TABLE tls201_appln
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls201_part03.csv'  
INTO TABLE tls201_appln
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 202 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls202_part01.csv'  
INTO TABLE tls202_appln_title
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

-- --- tbl 203 --- skipped due to size 
-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls203_part01.csv'  
-- INTO TABLE tls203_appln_abstr
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

--- tbl 204 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls204_part01.csv'  
INTO TABLE tls204_appln_prior
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 205 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls205_part01.csv'  
INTO TABLE tls205_tech_rel
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;


--- tbl 206 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls206_part01.csv'  
INTO TABLE tls206_person
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 207 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls207_part01.csv'  
INTO TABLE tls207_pers_appln
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 209 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls209_part01.csv'  
INTO TABLE tls209_appln_ipc
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;
 
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls207_part02.csv'  
INTO TABLE tls209_appln_ipc
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;
 
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls207_part03.csv'  
INTO TABLE tls209_appln_ipc
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 210 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls210_part01.csv'  
INTO TABLE tls210_appln_n_cls
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;
 
--- tbl 211 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls211_part01.csv'  
INTO TABLE tls211_pat_publn
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls211_part02.csv'  
INTO TABLE tls211_pat_publn
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 212 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls212_part01.csv'  
INTO TABLE tls212_citation
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls212_part02.csv'  
INTO TABLE tls212_citation
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls212_part03.csv'  
INTO TABLE tls212_citation
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 214 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls214_part01.csv'  
INTO TABLE patstat2023a.tls214_npl_publn
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES ;

--- tbl 215 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls215_part01.csv'  
INTO TABLE tls215_citn_categ
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls215_part02.csv'  
INTO TABLE tls215_citn_categ
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls215_part03.csv'  
INTO TABLE tls215_citn_categ
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

--- tbl 216 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls216_part01.csv'  
INTO TABLE tls216_appln_contn
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 222 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls222_part01.csv'  
INTO TABLE tls222_appln_jp_class
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;
 
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls222_part02.csv'  
INTO TABLE tls222_appln_jp_class
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 224 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls224_part01.csv'  
INTO TABLE tls224_appln_cpc
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n'  
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls224_part02.csv'  
INTO TABLE tls224_appln_cpc
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n'  
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls224_part03.csv'  
INTO TABLE tls224_appln_cpc
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n'  
IGNORE 1 LINES;

--- tbl 225 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls225_part01.csv'  
INTO TABLE tls225_docdb_fam_cpc
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls225_part02.csv'  
INTO TABLE tls225_docdb_fam_cpc
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

--- tbl 226 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls226_part01.csv'  
INTO TABLE tls226_person_orig
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls226_part02.csv'  
INTO TABLE tls226_person_orig
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

--- tbl 227 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls227_part01.csv'  
INTO TABLE tls227_pers_publn
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls227_part02.csv'  
INTO TABLE tls227_pers_publn
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 228 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls228_part01.csv'  
INTO TABLE tls228_docdb_fam_citn
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 229 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls229_part01.csv'  
INTO TABLE tls229_appln_nace2
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 230 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls230_part01.csv'  
INTO TABLE tls230_appln_techn_field
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

-- --- tbl 231 ---
-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part01.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part02.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part03.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part04.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part05.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part06.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part07.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part08.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part09.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part10.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part11.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

-- LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls231_part12.csv'  
-- INTO TABLE tls231_inpadoc_legal_event
-- FIELDS TERMINATED BY ','  
-- ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
-- IGNORE 1 LINES;

--- tbl 801 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls801_part01.csv'  
INTO TABLE tls801_country
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 803 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls803_part01.csv'  
INTO TABLE tls803_legal_event_code
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 901 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls901_part01.csv'  
INTO TABLE tls901_techn_field_ipc
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 902 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls902_part01.csv'  
INTO TABLE tls902_ipc_nace2
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

--- tbl 904 ---
LOAD DATA LOCAL INFILE '<SET PATH TO DATA FOLDER>/tls904_part01.csv'  
INTO TABLE tls904_nuts
FIELDS TERMINATED BY ','  
ENCLOSED BY '"' LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;


COMMIT;