Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Importing the European Union tenders into Neo4j
//-----------------------
//Clean everything
//-----------------------
MATCH (n)
OPTIONAL MATCH (n)-[r]-()
DELETE n,r;
//-----------------------
//Import authorities
//-----------------------
CREATE CONSTRAINT ON (a:AUTHORITY) ASSERT a.official_name IS UNIQUE;
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.contract_authority_official_name IS NOT NULL
MERGE (a:AUTHORITY {official_name: line.contract_authority_official_name})
ON CREATE SET a.number_of_contract = 1,
a.total_value_cost_eur= toInt(line.contract_total_value_cost_eur),
a.contract_value_vat_included= toInt(line.contract_contract_value_vat_included),
a.initial_value_cost= toInt(line.contract_initial_value_cost),
a.value_cost= toInt(line.contract_contract_value_cost),
a.initial_value_vat_included= toInt(line.contract_initial_value_vat_included),
a.value_cost_eur= toInt(line.contract_contract_value_cost_eur),
a.total_value_cost= toInt(line.contract_total_value_cost),
a.total_value_vat_included= toInt(line.contract_total_value_vat_included),
a.initial_value_cost_eur= toInt(line.contract_initial_value_cost_eur),
a.value_low_eur= toInt(line.contract_contract_value_low_eur),
a.value_high= toInt(line.contract_contract_value_high),
a.value_high_eur= toInt(line.contract_contract_value_high_eur),
a.value_low= toInt(line.contract_contract_value_low),
a.total_value_low= toInt(line.contract_total_value_low),
a.total_value_high= toInt(line.contract_total_value_high),
a.total_value_low_eur= toInt(line.contract_total_value_low_eur),
a.total_value_high_eur= toInt(line.contract_total_value_high_eur)
ON MATCH SET a.number_of_contract = a.number_of_contract +1,
a.total_value_cost_eur= a.total_value_cost_eur + toInt(line.contract_total_value_cost_eur),
a.contract_value_vat_included= a.contract_value_vat_included + toInt(line.contract_contract_value_vat_included),
a.initial_value_cost= a.initial_value_cost + toInt(line.contract_initial_value_cost),
a.value_cost= a.value_cost + toInt(line.contract_contract_value_cost),
a.initial_value_vat_included= a.initial_value_vat_included + toInt(line.contract_initial_value_vat_included),
a.value_cost_eur= a.value_cost_eur + toInt(line.contract_contract_value_cost_eur),
a.total_value_cost= a.total_value_cost + toInt(line.contract_total_value_cost),
a.total_value_vat_included= a.total_value_vat_included + toInt(line.contract_total_value_vat_included),
a.initial_value_cost_eur= a.initial_value_cost_eur + toInt(line.contract_initial_value_cost_eur),
a.value_low_eur= a.value_low_eur + toInt(line.contract_contract_value_low_eur),
a.value_high= a.value_high + toInt(line.contract_contract_value_high),
a.value_high_eur= a.value_high_eur + toInt(line.contract_contract_value_high_eur),
a.value_low= a.value_low + toInt(line.contract_contract_value_low),
a.total_value_low= a.total_value_low + toInt(line.contract_total_value_low),
a.total_value_high= a.total_value_high + toInt(line.contract_total_value_high),
a.total_value_low_eur= a.total_value_low_eur + toInt(line.contract_total_value_low_eur),
a.total_value_high_eur= a.total_value_high_eur + toInt(line.contract_total_value_high_eur)
;
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.contract_authority_official_name IS NOT NULL
MERGE (a:AUTHORITY {official_name: line.contract_authority_official_name})
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_country) <> "" THEN [1] ELSE [] END | SET a.country = line.contract_authority_country)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_postal_code) <> "" THEN [1] ELSE [] END | SET a.postal_code = toInt(line.contract_authority_postal_code))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_address) <> "" THEN [1] ELSE [] END | SET a.address = line.contract_authority_address)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_phone) <> "" THEN [1] ELSE [] END | SET a.phone = line.contract_authority_phone)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url_info) <> "" THEN [1] ELSE [] END | SET a.url_info = line.contract_authority_url_info)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_email) <> "" THEN [1] ELSE [] END | SET a.email = line.contract_authority_email)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_town) <> "" THEN [1] ELSE [] END | SET a.town = line.contract_authority_town)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_attention) <> "" THEN [1] ELSE [] END | SET a.attention = line.contract_authority_attention)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_slug) <> "" THEN [1] ELSE [] END | SET a.slug = line.contract_authority_slug)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url) <> "" THEN [1] ELSE [] END | SET a.url = line.contract_authority_url)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url_buyer) <> "" THEN [1] ELSE [] END | SET a.url_buyer = line.contract_authority_url_buyer)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_fax) <> "" THEN [1] ELSE [] END | SET a.fax = line.contract_authority_fax)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_authority_url_participate) <> "" THEN [1] ELSE [] END | SET a.url_participate = line.contract_authority_url_participate);
//-----------------------
//Import operators
//-----------------------
CREATE CONSTRAINT ON (a:OPERATOR) ASSERT a.official_name IS UNIQUE;
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.contract_operator_official_name IS NOT NULL
MERGE (a:OPERATOR {official_name: line.contract_operator_official_name})
ON CREATE SET a.number_of_contract = 1,
a.total_value_cost_eur= toInt(line.contract_total_value_cost_eur),
a.contract_value_vat_included= toInt(line.contract_contract_value_vat_included),
a.initial_value_cost= toInt(line.contract_initial_value_cost),
a.value_cost= toInt(line.contract_contract_value_cost),
a.initial_value_vat_included= toInt(line.contract_initial_value_vat_included),
a.value_cost_eur= toInt(line.contract_contract_value_cost_eur),
a.total_value_cost= toInt(line.contract_total_value_cost),
a.total_value_vat_included= toInt(line.contract_total_value_vat_included),
a.initial_value_cost_eur= toInt(line.contract_initial_value_cost_eur),
a.value_low_eur= toInt(line.contract_contract_value_low_eur),
a.value_high= toInt(line.contract_contract_value_high),
a.value_high_eur= toInt(line.contract_contract_value_high_eur),
a.value_low= toInt(line.contract_contract_value_low),
a.total_value_low= toInt(line.contract_total_value_low),
a.total_value_high= toInt(line.contract_total_value_high),
a.total_value_low_eur= toInt(line.contract_total_value_low_eur),
a.total_value_high_eur= toInt(line.contract_total_value_high_eur)
ON MATCH SET a.number_of_contract = a.number_of_contract +1,
a.total_value_cost_eur= a.total_value_cost_eur + toInt(line.contract_total_value_cost_eur),
a.contract_value_vat_included= a.contract_value_vat_included + toInt(line.contract_contract_value_vat_included),
a.initial_value_cost= a.initial_value_cost + toInt(line.contract_initial_value_cost),
a.value_cost= a.value_cost + toInt(line.contract_contract_value_cost),
a.initial_value_vat_included= a.initial_value_vat_included + toInt(line.contract_initial_value_vat_included),
a.value_cost_eur= a.value_cost_eur + toInt(line.contract_contract_value_cost_eur),
a.total_value_cost= a.total_value_cost + toInt(line.contract_total_value_cost),
a.total_value_vat_included= a.total_value_vat_included + toInt(line.contract_total_value_vat_included),
a.initial_value_cost_eur= a.initial_value_cost_eur + toInt(line.contract_initial_value_cost_eur),
a.value_low_eur= a.value_low_eur + toInt(line.contract_contract_value_low_eur),
a.value_high= a.value_high + toInt(line.contract_contract_value_high),
a.value_high_eur= a.value_high_eur + toInt(line.contract_contract_value_high_eur),
a.value_low= a.value_low + toInt(line.contract_contract_value_low),
a.total_value_low= a.total_value_low + toInt(line.contract_total_value_low),
a.total_value_high= a.total_value_high + toInt(line.contract_total_value_high),
a.total_value_low_eur= a.total_value_low_eur + toInt(line.contract_total_value_low_eur),
a.total_value_high_eur= a.total_value_high_eur + toInt(line.contract_total_value_high_eur)
;
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.contract_operator_official_name IS NOT NULL
MERGE (a:OPERATOR {official_name: line.contract_operator_official_name})
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_slug) <> "" THEN [1] ELSE [] END | SET a.slug = line.contract_operator_slug)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_town) <> "" THEN [1] ELSE [] END | SET a.town = line.contract_operator_town)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_address) <> "" THEN [1] ELSE [] END | SET a.address = line.contract_operator_address)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_postal_code) <> "" THEN [1] ELSE [] END | SET a.postal_code = toInt(line.contract_operator_postal_code))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_country) <> "" THEN [1] ELSE [] END | SET a.country = line.contract_operator_country)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_phone) <> "" THEN [1] ELSE [] END | SET a.phone = line.contract_operator_phone)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_email) <> "" THEN [1] ELSE [] END | SET a.email = line.contract_operator_email)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_url) <> "" THEN [1] ELSE [] END | SET a.url = line.contract_operator_url)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_operator_fax) <> "" THEN [1] ELSE [] END | SET a.fax = line.contract_operator_fax)
;
//-----------------------
//Import appeal body
//-----------------------
CREATE CONSTRAINT ON (a:APPEAL_BODY) ASSERT a.official_name IS UNIQUE;
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.contract_appeal_body_official_name IS NOT NULL
MERGE (a:APPEAL_BODY {official_name: line.contract_appeal_body_official_name})
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_country) <> "" THEN [1] ELSE [] END | SET a.country = line.contract_appeal_body_country)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_slug) <> "" THEN [1] ELSE [] END | SET a.slug = line.contract_appeal_body_slug)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_phone) <> "" THEN [1] ELSE [] END | SET a.phone = line.contract_appeal_body_phone)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_postal_code) <> "" THEN [1] ELSE [] END | SET a.postal_code = toInt(line.contract_appeal_body_postal_code))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_fax) <> "" THEN [1] ELSE [] END | SET a.fax = line.contract_appeal_body_fax)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_email) <> "" THEN [1] ELSE [] END | SET a.email = line.contract_appeal_body_email)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_town) <> "" THEN [1] ELSE [] END | SET a.town = line.contract_appeal_body_town)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_address) <> "" THEN [1] ELSE [] END | SET a.address = line.contract_appeal_body_address)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_body_url) <> "" THEN [1] ELSE [] END | SET a.url = line.contract_appeal_body_url)
;
//-----------------------
//Import "the on behalf of"
//-----------------------
CREATE CONSTRAINT ON (a:DELEGATE) ASSERT a.official_name IS UNIQUE;
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.contract_on_behalf_official_name IS NOT NULL
MERGE (a:DELEGATE {official_name: line.contract_on_behalf_official_name})
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_on_behalf_address) <> "" THEN [1] ELSE [] END | SET a.address = line.contract_on_behalf_address)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_on_behalf_country) <> "" THEN [1] ELSE [] END | SET a.country = line.contract_on_behalf_country)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_on_behalf_town) <> "" THEN [1] ELSE [] END | SET a.town = line.contract_on_behalf_town)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_on_behalf_postal_code) <> "" THEN [1] ELSE [] END | SET a.postal_code = toInt(line.contract_on_behalf_postal_code))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_on_behalf_slug) <> "" THEN [1] ELSE [] END | SET a.slug = line.contract_on_behalf_slug)
;
//-----------------------
//Creating indexes to speed up the creation of relationships
//-----------------------
CREATE INDEX ON :AUTHORITY(official_name);
CREATE INDEX ON :APPEAL_BODY(official_name);
CREATE INDEX ON :DELEGATE(official_name);
CREATE INDEX ON :OPERATOR(official_name);
CREATE INDEX ON :CONTRACT(id);
//-----------------------
//Relationships between operators and authorities
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
MATCH (b:AUTHORITY {official_name: line.contract_authority_official_name})
MATCH (a:OPERATOR {official_name: line.contract_operator_official_name})
MERGE (a)-[r:IS_CONTRACTED_BY]->(b)
ON CREATE SET r.contract_id = line.contract_id
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_doc_no) <> "" THEN [1] ELSE [] END | SET r.contract_doc_no = line.contract_doc_no)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_month) <> "" THEN [1] ELSE [] END | SET r.contract_contract_award_month = toInt(line.contract_contract_award_month))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_type_contract) <> "" THEN [1] ELSE [] END | SET r.contract_type_contract = line.contract_type_contract)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_title) <> "" THEN [1] ELSE [] END | SET r.contract_contract_award_title = line.contract_contract_award_title)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_lot_number) <> "" THEN [1] ELSE [] END | SET r.contract_lot_number = toInt(line.contract_lot_number))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_location_nuts) <> "" THEN [1] ELSE [] END | SET r.contract_location_nuts = line.contract_location_nuts)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_offers_received_meaning) <> "" THEN [1] ELSE [] END | SET r.contract_offers_received_meaning = line.contract_offers_received_meaning)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_procedure) <> "" THEN [1] ELSE [] END | SET r.contract_appeal_procedure = line.contract_appeal_procedure)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_month) <> "" THEN [1] ELSE [] END | SET r.contract_notice_dispatch_month = toInt(line.contract_notice_dispatch_month))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_index) <> "" THEN [1] ELSE [] END | SET r.contract_index = toInt(line.contract_index))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_electronic_auction) <> "" THEN [1] ELSE [] END | SET r.contract_electronic_auction = line.contract_electronic_auction)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_year) <> "" THEN [1] ELSE [] END | SET r.contract_notice_dispatch_year = toInt(line.contract_notice_dispatch_year))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_day) <> "" THEN [1] ELSE [] END | SET r.contract_contract_award_day = toInt(line.contract_contract_award_day))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_number) <> "" THEN [1] ELSE [] END | SET r.contract_contract_number = line.contract_contract_number)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_relates_to_eu_project) <> "" THEN [1] ELSE [] END | SET r.contract_relates_to_eu_project = line.contract_relates_to_eu_project)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_day) <> "" THEN [1] ELSE [] END | SET r.contract_notice_dispatch_day = toInt(line.contract_notice_dispatch_day))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_additional_information) <> "" THEN [1] ELSE [] END | SET r.contract_additional_information = line.contract_additional_information)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_year) <> "" THEN [1] ELSE [] END | SET r.contract_contract_award_year = toInt(line.contract_contract_award_year))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_offers_received_num) <> "" THEN [1] ELSE [] END | SET r.contract_offers_received_num = toInt(line.contract_offers_received_num))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_title) <> "" THEN [1] ELSE [] END | SET r.contract_contract_title = line.contract_contract_title)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_location) <> "" THEN [1] ELSE [] END | SET r.contract_location = line.contract_location)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_file_reference) <> "" THEN [1] ELSE [] END | SET r.contract_file_reference = line.contract_file_reference)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_description) <> "" THEN [1] ELSE [] END | SET r.contract_contract_description = line.contract_contract_description)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_cpv_code) <> "" THEN [1] ELSE [] END | SET r.contract_cpv_code = toInt(line.contract_cpv_code))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_activity_type) <> "" THEN [1] ELSE [] END | SET r.contract_activity_type = line.contract_activity_type)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_currency) <> "" THEN [1] ELSE [] END | SET r.contract_initial_value_currency = line.contract_initial_value_currency)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_cost_eur) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_cost_eur = toInt(line.contract_total_value_cost_eur))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_vat_included) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_vat_included = toInt(line.contract_contract_value_vat_included))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_cost) <> "" THEN [1] ELSE [] END | SET r.contract_initial_value_cost = toInt(line.contract_initial_value_cost))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_currency) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_currency = line.contract_contract_value_currency)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_cost) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_cost = toInt(line.contract_contract_value_cost))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_vat_included) <> "" THEN [1] ELSE [] END | SET r.contract_initial_value_vat_included = toInt(line.contract_initial_value_vat_included))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_cost_eur) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_cost_eur = toInt(line.contract_contract_value_cost_eur))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_cost) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_cost = toInt(line.contract_total_value_cost))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_vat_included) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_vat_included = toInt(line.contract_total_value_vat_included))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_cost_eur) <> "" THEN [1] ELSE [] END | SET r.contract_initial_value_cost_eur = toInt(line.contract_initial_value_cost_eur))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_currency) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_currency = line.contract_total_value_currency)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_vat_rate) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_vat_rate = line.contract_total_value_vat_rate)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_vat_rate) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_vat_rate = line.contract_contract_value_vat_rate)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_vat_rate) <> "" THEN [1] ELSE [] END | SET r.contract_initial_value_vat_rate = line.contract_initial_value_vat_rate)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_low_eur) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_low_eur = line.contract_contract_value_low_eur)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_high) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_high = line.contract_contract_value_high)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_high_eur) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_high_eur = line.contract_contract_value_high_eur)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_low) <> "" THEN [1] ELSE [] END | SET r.contract_contract_value_low = line.contract_contract_value_low)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_low) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_low = line.contract_total_value_low)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_high) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_high = line.contract_total_value_high)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_low_eur) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_low_eur = line.contract_total_value_low_eur)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_high_eur) <> "" THEN [1] ELSE [] END | SET r.contract_total_value_high_eur = line.contract_total_value_high_eur)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_gpa_covered) <> "" THEN [1] ELSE [] END | SET r.contract_gpa_covered = line.contract_gpa_covered)
;
//-----------------------
//Global cashflow between operators and authorities
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
MATCH (b:AUTHORITY {official_name: line.contract_authority_official_name})
MATCH (a:OPERATOR {official_name: line.contract_operator_official_name})
MERGE (a)-[r:CASH_FLOW]->(b)
ON CREATE SET r.number_of_contract = 1,
r.total_value_cost_eur= toInt(line.contract_total_value_cost_eur),
r.contract_value_vat_included= toInt(line.contract_contract_value_vat_included),
r.initial_value_cost= toInt(line.contract_initial_value_cost),
r.value_cost= toInt(line.contract_contract_value_cost),
r.initial_value_vat_included= toInt(line.contract_initial_value_vat_included),
r.value_cost_eur= toInt(line.contract_contract_value_cost_eur),
r.total_value_cost= toInt(line.contract_total_value_cost),
r.total_value_vat_included= toInt(line.contract_total_value_vat_included),
r.initial_value_cost_eur= toInt(line.contract_initial_value_cost_eur),
r.value_low_eur= toInt(line.contract_contract_value_low_eur),
r.value_high= toInt(line.contract_contract_value_high),
r.value_high_eur= toInt(line.contract_contract_value_high_eur),
r.value_low= toInt(line.contract_contract_value_low),
r.total_value_low= toInt(line.contract_total_value_low),
r.total_value_high= toInt(line.contract_total_value_high),
r.total_value_low_eur= toInt(line.contract_total_value_low_eur),
r.total_value_high_eur= toInt(line.contract_total_value_high_eur)
ON MATCH SET r.number_of_contract = a.number_of_contract +1,
r.total_value_cost_eur= a.total_value_cost_eur + toInt(line.contract_total_value_cost_eur),
r.contract_value_vat_included= a.contract_value_vat_included + toInt(line.contract_contract_value_vat_included),
r.initial_value_cost= a.initial_value_cost + toInt(line.contract_initial_value_cost),
r.value_cost= a.value_cost + toInt(line.contract_contract_value_cost),
r.initial_value_vat_included= a.initial_value_vat_included + toInt(line.contract_initial_value_vat_included),
r.value_cost_eur= a.value_cost_eur + toInt(line.contract_contract_value_cost_eur),
r.total_value_cost= a.total_value_cost + toInt(line.contract_total_value_cost),
r.total_value_vat_included= a.total_value_vat_included + toInt(line.contract_total_value_vat_included),
r.initial_value_cost_eur= a.initial_value_cost_eur + toInt(line.contract_initial_value_cost_eur),
r.value_low_eur= a.value_low_eur + toInt(line.contract_contract_value_low_eur),
r.value_high= a.value_high + toInt(line.contract_contract_value_high),
r.value_high_eur= a.value_high_eur + toInt(line.contract_contract_value_high_eur),
r.value_low= a.value_low + toInt(line.contract_contract_value_low),
r.total_value_low= a.total_value_low + toInt(line.contract_total_value_low),
r.total_value_high= a.total_value_high + toInt(line.contract_total_value_high),
r.total_value_low_eur= a.total_value_low_eur + toInt(line.contract_total_value_low_eur),
r.total_value_high_eur= a.total_value_high_eur + toInt(line.contract_total_value_high_eur)
;
//-----------------------
//Relationships between appeal body and authorities
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
MATCH (b:AUTHORITY {official_name: line.contract_authority_official_name})
MATCH (a:APPEAL_BODY {official_name: line.contract_appeal_body_official_name})
MERGE (a)-[r:IS_APPEAL_BODY_OF]->(b)
ON CREATE SET r.contract_id = line.contract_id;
//-----------------------
//Relationships between "on behalf" and authorities
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
MATCH (b:AUTHORITY {official_name: line.contract_authority_official_name})
MATCH (a:DELEGATE {official_name: line.contract_on_behalf_official_name})
MERGE (a)-[r:ACTS_ON_BEHALF_OF]->(b)
ON CREATE SET r.contract_id = line.contract_id
;
//-----------------------
//Import contracts
//-----------------------
CREATE CONSTRAINT ON (a:CONTRACT) ASSERT a.id IS UNIQUE;
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.contract_id IS NOT NULL
MERGE (a:CONTRACT {id: line.contract_id})
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_doc_no) <> "" THEN [1] ELSE [] END | SET a.contract_doc_no = line.contract_doc_no)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_month) <> "" THEN [1] ELSE [] END | SET a.contract_contract_award_month = toInt(line.contract_contract_award_month))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_type_contract) <> "" THEN [1] ELSE [] END | SET a.contract_type_contract = line.contract_type_contract)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_title) <> "" THEN [1] ELSE [] END | SET a.contract_contract_award_title = line.contract_contract_award_title)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_lot_number) <> "" THEN [1] ELSE [] END | SET a.contract_lot_number = toInt(line.contract_lot_number))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_location_nuts) <> "" THEN [1] ELSE [] END | SET a.contract_location_nuts = line.contract_location_nuts)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_offers_received_meaning) <> "" THEN [1] ELSE [] END | SET a.contract_offers_received_meaning = line.contract_offers_received_meaning)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_appeal_procedure) <> "" THEN [1] ELSE [] END | SET a.contract_appeal_procedure = line.contract_appeal_procedure)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_month) <> "" THEN [1] ELSE [] END | SET a.contract_notice_dispatch_month = toInt(line.contract_notice_dispatch_month))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_index) <> "" THEN [1] ELSE [] END | SET a.contract_index = toInt(line.contract_index))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_electronic_auction) <> "" THEN [1] ELSE [] END | SET a.contract_electronic_auction = line.contract_electronic_auction)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_year) <> "" THEN [1] ELSE [] END | SET a.contract_notice_dispatch_year = toInt(line.contract_notice_dispatch_year))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_day) <> "" THEN [1] ELSE [] END | SET a.contract_contract_award_day = toInt(line.contract_contract_award_day))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_number) <> "" THEN [1] ELSE [] END | SET a.contract_contract_number = line.contract_contract_number)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_relates_to_eu_project) <> "" THEN [1] ELSE [] END | SET a.contract_relates_to_eu_project = line.contract_relates_to_eu_project)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_notice_dispatch_day) <> "" THEN [1] ELSE [] END | SET a.contract_notice_dispatch_day = toInt(line.contract_notice_dispatch_day))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_additional_information) <> "" THEN [1] ELSE [] END | SET a.contract_additional_information = line.contract_additional_information)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_award_year) <> "" THEN [1] ELSE [] END | SET a.contract_contract_award_year = toInt(line.contract_contract_award_year))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_offers_received_num) <> "" THEN [1] ELSE [] END | SET a.contract_offers_received_num = toInt(line.contract_offers_received_num))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_title) <> "" THEN [1] ELSE [] END | SET a.contract_contract_title = line.contract_contract_title)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_location) <> "" THEN [1] ELSE [] END | SET a.contract_location = line.contract_location)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_file_reference) <> "" THEN [1] ELSE [] END | SET a.contract_file_reference = line.contract_file_reference)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_description) <> "" THEN [1] ELSE [] END | SET a.contract_contract_description = line.contract_contract_description)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_cpv_code) <> "" THEN [1] ELSE [] END | SET a.contract_cpv_code = toInt(line.contract_cpv_code))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_activity_type) <> "" THEN [1] ELSE [] END | SET a.contract_activity_type = line.contract_activity_type)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_currency) <> "" THEN [1] ELSE [] END | SET a.contract_initial_value_currency = line.contract_initial_value_currency)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_cost_eur) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_cost_eur = toInt(line.contract_total_value_cost_eur))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_vat_included) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_vat_included = toInt(line.contract_contract_value_vat_included))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_cost) <> "" THEN [1] ELSE [] END | SET a.contract_initial_value_cost = toInt(line.contract_initial_value_cost))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_currency) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_currency = line.contract_contract_value_currency)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_cost) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_cost = toInt(line.contract_contract_value_cost))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_vat_included) <> "" THEN [1] ELSE [] END | SET a.contract_initial_value_vat_included = toInt(line.contract_initial_value_vat_included))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_cost_eur) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_cost_eur = toInt(line.contract_contract_value_cost_eur))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_cost) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_cost = toInt(line.contract_total_value_cost))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_vat_included) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_vat_included = toInt(line.contract_total_value_vat_included))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_cost_eur) <> "" THEN [1] ELSE [] END | SET a.contract_initial_value_cost_eur = toInt(line.contract_initial_value_cost_eur))
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_currency) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_currency = line.contract_total_value_currency)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_vat_rate) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_vat_rate = line.contract_total_value_vat_rate)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_vat_rate) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_vat_rate = line.contract_contract_value_vat_rate)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_initial_value_vat_rate) <> "" THEN [1] ELSE [] END | SET a.contract_initial_value_vat_rate = line.contract_initial_value_vat_rate)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_low_eur) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_low_eur = line.contract_contract_value_low_eur)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_high) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_high = line.contract_contract_value_high)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_high_eur) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_high_eur = line.contract_contract_value_high_eur)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_contract_value_low) <> "" THEN [1] ELSE [] END | SET a.contract_contract_value_low = line.contract_contract_value_low)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_low) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_low = line.contract_total_value_low)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_high) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_high = line.contract_total_value_high)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_low_eur) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_low_eur = line.contract_total_value_low_eur)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_total_value_high_eur) <> "" THEN [1] ELSE [] END | SET a.contract_total_value_high_eur = line.contract_total_value_high_eur)
FOREACH(ignoreMe IN CASE WHEN trim(line.contract_gpa_covered) <> "" THEN [1] ELSE [] END | SET a.contract_gpa_covered = line.contract_gpa_covered)
;
//-----------------------
//Relationships between operators and contracts
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
MATCH (b:CONTRACT {id: line.contract_id})
MATCH (a:OPERATOR {official_name: line.contract_operator_official_name})
MERGE (a)-[r:IS_OPERATOR_OF]->(b);
//-----------------------
//Relationships between appeal body and contracts
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
MATCH (b:CONTRACT {id: line.contract_id})
MATCH (a:APPEAL_BODY {official_name: line.contract_appeal_body_official_name})
MERGE (a)-[r:IS_APPEAL_BODY_OF]->(b);
//-----------------------
//Relationships between "on behalf" and contracts
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
MATCH (b:CONTRACT {id: line.contract_id})
MATCH (a:DELEGATE {official_name: line.contract_on_behalf_official_name})
MERGE (a)-[r:IS_DELEGATE_OF]->(b);
//-----------------------
//Relationships between authorities and contracts
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/ted-contracts-2015.csv" AS line
FIELDTERMINATOR ','
MATCH (b:CONTRACT {id: line.contract_id})
MATCH (a:AUTHORITY {official_name: line.contract_authority_official_name})
MERGE (a)-[r:IS_AUTHORITY_OF]->(b);
@ciccio995

This comment has been minimized.

Copy link

commented Jun 12, 2019

Could you tell me where to get this csv file?
I tried to download it from this link(https://data.europa.eu/euodp/en/data/dataset/ted-csv) but it's not the right one.

@jvilledieu

This comment has been minimized.

Copy link
Owner Author

commented Jun 13, 2019

The address should be correct. You can find more information about this graph here: https://linkurio.us/blog/exploring-e1-3-trillion-in-public-contracts-with-graph-visualization/

@ciccio995

This comment has been minimized.

Copy link

commented Jun 13, 2019

Thanks for the answer. The link you mentioned is the reference link for my project. I downloaded from here the CSV:
https://data.europa.eu/euodp/en/data/dataset/ted-csv ,
but all the version of 2015, are incompatible with the script...What can I do?

@darius-randomness

This comment has been minimized.

Copy link

commented Jul 7, 2019

I Have the same problems, ciccio, do you resolve it? can you post the correct csv file for this project?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.