Create a Neo4j database from Crunchbase data in Excel

crunchbase-to-neo4j.cql

//-----------------------
//Import the cities
//-----------------------

CREATE CONSTRAINT ON (e:CITY) ASSERT e.name IS UNIQUE;

//Create the companies' cities
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/Companies.csv" AS line
FIELDTERMINATOR ';'
MERGE (:CITY { name: line.city });

//Create the investors' cities
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/Investments.csv" AS line
FIELDTERMINATOR ';'
MERGE (:CITY { name: line.investor_city });



//-----------------------
//Import the markets
//-----------------------

CREATE CONSTRAINT ON (e:MARKET) ASSERT e.name IS UNIQUE;

//Create the companies' markets
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/Companies.csv" AS line
FIELDTERMINATOR ';'
MERGE (:MARKET { name: line.market });

//Create the investors' markets
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/Investments.csv" AS line
FIELDTERMINATOR ';'
MERGE (:MARKET { name: line.investor_market });



//-----------------------
//Import the companies
//-----------------------

CREATE CONSTRAINT ON (a:COMPANY) ASSERT a.permalink IS UNIQUE;

//Create the companies

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/Companies.csv" AS line
FIELDTERMINATOR ';'
CREATE (a:COMPANY {
  name: line.name,
  permalink: line.permalink,
  homepage_url: line.homepage_url,
  funding_total: toInt(line.funding_total_usd),
  funding_rounds: toInt(line.funding_rounds),
  founded_at: line.founded_at,
  founded_month: line.founded_month,
  founded_quarter: line.founded_quarter,
  founded_year: toInt(line.founded_year),
  first_funding_at: line.first_funding_at,
  last_funding_at: line.last_funding_at,
  category: line.category_list,
  market: line.market,
  status: line.status,
  country: line.country_code,
  state: line.state_code,
  region: line.region,
  city: line.city,
  url: 'http://www.crunchbase.com' + line.permalink,
  logo: 'http://www.crunchbase.com' + line.permalink + '/primary-image/raw'
});

//Link companies to cities

MATCH (a:COMPANY)
MATCH (b:CITY { name: a.city })
CREATE (a)-[:HAS_CITY]->(b);

//Link companies to markets

MATCH (a:COMPANY)
MATCH (b:MARKET { name: a.market })
CREATE (a)-[:HAS_MARKET]->(b);



//-----------------------
//Import the investments
//-----------------------

CREATE CONSTRAINT ON (a:INVESTOR) ASSERT a.permalink IS UNIQUE;

//Create the investors

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/Investments.csv" AS line
FIELDTERMINATOR ';'
MATCH (a:COMPANY { permalink: line.investor_permalink })
SET a:INVESTOR;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/Investments.csv" AS line
FIELDTERMINATOR ';'
MERGE (a:INVESTOR { permalink: line.investor_permalink })
ON CREATE SET a.name = line.investor_name,
  a.category = line.investor_category_list,
  a.market = line.investor_market,
  a.country = line.investor_country_code,
  a.state = line.investor_state_code,
  a.region = line.investor_region,
  a.city = line.investor_city,
  a.logo = 'http://www.crunchbase.com' + line.permalink + '/primary-image/raw',
  a.url = 'http://www.crunchbase.com' + line.investor_permalink;


//Link investors to cities

MATCH (a:INVESTOR)
MATCH (b:CITY { name: a.city})
MERGE (a)-[:HAS_CITY]->(b);

//Link investors to companies

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/Investments.csv" AS line
FIELDTERMINATOR ';'
MATCH (a:INVESTOR { permalink: line.investor_permalink })
MATCH (b:COMPANY  { permalink: line.company_permalink })
CREATE (a)-[:INVESTED_IN {
  funded_at: line.funded_at,
  funded_month: line.funded_month,
  funded_quarter: line.funded_quarter,
  funded_year: toInt(line.funded_year),
  raised_amount_usd: toInt(line.raised_amount_usd),
  funding_round_code: line.funding_round_code,
  funding_round_type: line.funding_round_type,
  permalink: line.funding_round_permalink,
  url: 'http://www.crunchbase.com' + line.permalink
}]->(b);



//-----------------------
//Import the acquisitions
//-----------------------

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/Acquisitions.csv" AS line
FIELDTERMINATOR ';'
MATCH (a:COMPANY { permalink: line.acquirer_permalink })
MATCH (b:COMPANY { permalink: line.company_permalink })
CREATE (a)-[:ACQUIRED {
  acquisition_date: line.acquired_at,
  month: line.acquired_month,
  quarter: line.acquired_quarter,
  year: line.acquired_year,
  price_amount: toInt(line.price_amount),
  currency: line.price_currency_code
}]->(b);



//-----------------------
//Cleaning up
//-----------------------

MATCH (n:COMPANY)
WHERE n.state = ''
REMOVE n.state;

MATCH (n:COMPANY)
WHERE n.homepage_url = ''
REMOVE n.homepage_url;

MATCH (n:COMPANY)
WHERE n.funding_total = ''
REMOVE n.funding_total;

MATCH (n:COMPANY)
WHERE n.funding_rounds = ''
REMOVE n.funding_rounds;

MATCH (n:COMPANY)
WHERE n.founded_at = ''
REMOVE n.founded_at;

MATCH (n:COMPANY)
WHERE n.founded_month = ''
REMOVE n.founded_month;

MATCH (n:COMPANY)
WHERE n.founded_quarter = ''
REMOVE n.founded_quarter;

MATCH (n:COMPANY)
WHERE n.founded_year = ''
REMOVE n.founded_year;

MATCH (n:COMPANY)
WHERE n.first_funding_at = ''
REMOVE n.first_funding_at;

MATCH (n:COMPANY)
WHERE n.last_funding_at = ''
REMOVE n.last_funding_at;

MATCH (n:COMPANY)
WHERE n.category = ''
REMOVE n.category;

MATCH (n:COMPANY)
WHERE n.market = ''
REMOVE n.market;

MATCH (n:COMPANY)
WHERE n.status = ''
REMOVE n.status;

MATCH (n:COMPANY)
WHERE n.country = ''
REMOVE n.country;

MATCH (n:COMPANY)
WHERE n.state = ''
REMOVE n.state;

MATCH (n:COMPANY)
WHERE n.region = ''
REMOVE n.region;

MATCH (n:COMPANY)
REMOVE n.city;

MATCH (n:INVESTOR)
WHERE n.state = ''
REMOVE n.state;

MATCH (n:INVESTOR)
WHERE n.category = ''
REMOVE n.category;

MATCH (n:INVESTOR)
WHERE n.market = ''
REMOVE n.market;

MATCH (n:INVESTOR)
WHERE n.city = ''
REMOVE n.city;

MATCH (n:INVESTOR)
WHERE n.region = ''
REMOVE n.region;

MATCH (n:INVESTOR)
WHERE n.country = ''
REMOVE n.country;

MATCH (n:INVESTOR)-[r:INVESTED_IN]->(b:COMPANY)
WHERE r.funded_quarter = ''
REMOVE r.funded_quarter;

MATCH (n:INVESTOR)-[r:INVESTED_IN]->(b:COMPANY)
WHERE r.funded_year = ''
REMOVE r.funded_year;

MATCH (n:INVESTOR)-[r:INVESTED_IN]->(b:COMPANY)
WHERE r.raised_amount_usd = ''
REMOVE r.raised_amount_usd;

MATCH (n:INVESTOR)-[r:INVESTED_IN]->(b:COMPANY)
WHERE r.funding_round_code = ''
REMOVE r.funding_round_code;

MATCH (n:INVESTOR)-[r:INVESTED_IN]->(b:COMPANY)
WHERE r.funding_round_type = ''
REMOVE r.funding_round_type;

MATCH (n:INVESTOR)-[r:INVESTED_IN]->(b:COMPANY)
WHERE r.funded_month = ''
REMOVE r.funded_month;

MATCH (n:INVESTOR)-[r:INVESTED_IN]->(b:COMPANY)
WHERE r.funded_at = ''
REMOVE r.funded_at;

MATCH (n:COMPANY)-[r:ACQUIRED]->(b:COMPANY)
WHERE r.acquisition_date = ''
REMOVE r.acquisition_date;

MATCH (n:COMPANY)-[r:ACQUIRED]->(b:COMPANY)
WHERE r.month = ''
REMOVE r.month;

MATCH (n:COMPANY)-[r:ACQUIRED]->(b:COMPANY)
WHERE r.quarter = ''
REMOVE r.quarter;

MATCH (n:COMPANY)-[r:ACQUIRED]->(b:COMPANY)
WHERE r.year = ''
REMOVE r.year;

MATCH (n:COMPANY)-[r:ACQUIRED]->(b:COMPANY)
WHERE r.price_amount = ''
REMOVE r.price_amount;

MATCH (n:COMPANY)-[r:ACQUIRED]->(b:COMPANY)
WHERE r.currency = ''
REMOVE r.currency;

MATCH (n:CITY {name :''})-[r]-()
DELETE n, r;

MATCH (n:MARKET {name :''})-[r]-()
DELETE n, r;

Export French companies:

export-graphml -t -o crunchbase-fr.graphml match (n)-[r]-(a:COMPANY {country:'FRA'}) return n,r,a

Import French companies into Neo4j:

import-graphml -t -i crunchbase-fr.graphml -b 20000