Importing the European Union tenders into Neo4j

import_EU_tenders.cql

//-----------------------
//Import authorities
//-----------------------
 
CREATE CONSTRAINT ON (a:AUTHORITY) ASSERT a.contract_authority_official_name IS UNIQUE;
 
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.contract_authority_official_name IS NOT NULL
MERGE (a:AUTHORITY {contract_authority_official_name: line.contract_authority_official_name})
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_country) <> "" THEN [1] ELSE [] END | SET a.contract_authority_country = line.contract_authority_country)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_postal_code) <> "" THEN [1] ELSE [] END | SET a.contract_authority_postal_code = line.contract_authority_postal_code)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_address) <> "" THEN [1] ELSE [] END | SET a.contract_authority_address = line.contract_authority_address)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_phone) <> "" THEN [1] ELSE [] END | SET a.contract_authority_phone = line.contract_authority_phone)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url_info) <> "" THEN [1] ELSE [] END | SET a.contract_authority_url_info = line.contract_authority_url_info)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_email) <> "" THEN [1] ELSE [] END | SET a.contract_authority_email = line.contract_authority_email)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_town) <> "" THEN [1] ELSE [] END | SET a.contract_authority_town = line.contract_authority_town)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_attention) <> "" THEN [1] ELSE [] END | SET a.contract_authority_attention = line.contract_authority_attention)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_slug) <> "" THEN [1] ELSE [] END | SET a.contract_authority_slug = line.contract_authority_slug)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_official_name) <> "" THEN [1] ELSE [] END | SET a.contract_authority_official_name = line.contract_authority_official_name)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url) <> "" THEN [1] ELSE [] END | SET a.contract_authority_url = line.contract_authority_url)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url_buyer) <> "" THEN [1] ELSE [] END | SET a.contract_authority_url_buyer = line.contract_authority_url_buyer)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_fax) <> "" THEN [1] ELSE [] END | SET a.contract_authority_fax = line.contract_authority_fax)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url_participate) <> "" THEN [1] ELSE [] END | SET a.contract_authority_url_participate = line.contract_authority_url_participate)
;

//-----------------------
//Import operators
//-----------------------
 
CREATE CONSTRAINT ON (a:OPERATOR) ASSERT a.contract_operator_official_name IS UNIQUE;
 
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.contract_operator_official_name IS NOT NULL
MERGE (a:OPERATOR {contract_operator_official_name: line.contract_operator_official_name})
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_slug) <> "" THEN [1] ELSE [] END | SET a.contract_operator_slug = line.contract_operator_slug)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_town) <> "" THEN [1] ELSE [] END | SET a.contract_operator_town = line.contract_operator_town)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_address) <> "" THEN [1] ELSE [] END | SET a.contract_operator_address = line.contract_operator_address)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_postal_code) <> "" THEN [1] ELSE [] END | SET a.contract_operator_postal_code = line.contract_operator_postal_code)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_country) <> "" THEN [1] ELSE [] END | SET a.contract_operator_country = line.contract_operator_country)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_phone) <> "" THEN [1] ELSE [] END | SET a.contract_operator_phone = line.contract_operator_phone)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_email) <> "" THEN [1] ELSE [] END | SET a.contract_operator_email = line.contract_operator_email)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_url) <> "" THEN [1] ELSE [] END | SET a.contract_operator_url = line.contract_operator_url)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_fax) <> "" THEN [1] ELSE [] END | SET a.contract_operator_fax = line.contract_operator_fax)
;

//-----------------------
//Creating indexes to speed up the creation of relationships
//-----------------------
 
CREATE INDEX ON :AUTHORITY(contract_authority_official_name);
CREATE INDEX ON :OPERATOR(contract_operator_official_name);


//-----------------------
//Relationships between operators and authorities
//-----------------------
 
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts.csv" AS line
FIELDTERMINATOR ','
MATCH (b:AUTHORITY {contract_authority_official_name: line.contract_authority_official_name})
MATCH (a:OPERATOR {contract_operator_official_name: line.contract_operator_official_name})
MERGE (a)-[r:IS_CONTRACTED_BY]->(b)
ON CREATE SET r.contract_id = line.contract_id;