Cypher - Multiple mapping when loadiing CSV files

Question

I'm a beginner with Cypher language (Neo4J) and I have try to find an answer to my problem without success. I have loaded successfully nodes for Year(year), Month(month) and Day(day) with Has_Month and Has_Day relationship. I also load successfully Airports(airportCode) node.

Now, I have a CSV file with these data:

Origin,YearDeparture,MonthDeparture,DayDeparture
AGP,2018,02,16
CDG,2017,05,24
...

I want to create a relationship between Day and Origin (which is an Airport already created). I need to do something like this pseudocode:

if YearDeparture = Year.year AND
MonthDeparture = Month.month AND
DayDeparture = Day.day AND
Origin = Airport.airportCode then
CREATE (d)-[:HAS_AIRPORT]->(a)
(where d is a Day and a is an airport)

I have try this code without success:

LOAD CSV WITH HEADERS FROM "File:///AirportLinkedToDay.csv" AS csvLine
WITH csvLine WHERE NOT csvLine.Origin IS NULL
MATCH (y:Year {year: csvLine.YearDeparture})
MATCH (m:Month {month: csvLine.MonthDeparture})
MATCH (d:Day {day: csvLine.DayDeparture})
MATCH (a:AIRPORT {airportCode: csvLine.Origin })
MATCH (y)-[:HAS_MONTH]->(m)-[:HAS_DAY]->(d)
MERGE (d)-[:HAS_AIRPORT]->(a)

Many thanks for your help. And don't hesitate if something is unclear. M.

Update: Code to create hierarchical dates parts (Year,Month,Day nodes):

WITH range(2012, 2025) AS years, range(1,12) as months
FOREACH(year IN years | 
 MERGE (y:Year {year: year})
 FOREACH(month IN months | 
   CREATE (m:Month {month: month})
   MERGE (y)-[:HAS_MONTH]->(m)
   FOREACH(day IN (CASE 
                     WHEN month IN [1,3,5,7,8,10,12] THEN range(1,31) 
                     WHEN month = 2 THEN 
                       CASE
                         WHEN year % 4 <> 0 THEN range(1,28)
                         WHEN year % 100 <> 0 THEN range(1,29)
                         WHEN year % 400 = 0 THEN range(1,29)
                         ELSE range(1,28)
                       END
                     ELSE range(1,30)
                   END) |      
     CREATE (d:Day {day: day})
     MERGE (m)-[:HAS_DAY]->(d))))

Update: Code to create Airport Nodes:

 LOAD CSV WITH HEADERS FROM "File:///Airports.csv" AS csvLine
 CREATE (p:AIRPORT { airportCode: csvLine.Code })

Are your relationship types Has_Month/Has_Day or HAS_MONTH/HAS_DAY? Capitalization is significant. — cybersam
Also, if you only have 31 Day nodes, then (d)-[:HAS_AIRPORT]->(a) is going to be very ambiguous. For example, there would be no way to know which month or year is intended for the Day node with day value 1 -- since all months and years will have a "day 1". — cybersam
Yes, HAS_MONTH and HAS_DAY are the right syntax. My error when I wrote the first paragraph — Marty_007
I don't have only 31 days. I have all days from 2012/01/01 to 2025/12/31 with a relation YEAR->MONTH->DAY — Marty_007
This is exactly what I'm searching for. How to link not only the day but also the month and the year corresponding to the date in the CSV — Marty_007

cybersam cybersam · Accepted Answer · 2018-10-03T05:14:08

The main issue was caused by the fact that LOAD CSV treats all input values as strings. In order to match against integer values, you need to convert input strings to integers via the TOINTEGER() function. For example:

LOAD CSV WITH HEADERS FROM "File:///AirportLinkedToDay.csv" AS row
WITH row WHERE NOT row.Origin IS NULL
MATCH (y:Year {year: TOINTEGER(row.YearDeparture)})
MATCH (m:Month {month: TOINTEGER(row.MonthDeparture)})
MATCH (d:Day {day: TOINTEGER(row.DayDeparture)})
MATCH (a:AIRPORT {airportCode: row.Origin })
MATCH (y)-[:HAS_MONTH]->(m)-[:HAS_DAY]->(d)
MERGE (d)-[:HAS_AIRPORT]->(a)

Also, your query will be more performant if you created indexes on:

:Year(year)
:AIRPORT(airportCode)

Cypher - Multiple mapping when loadiing CSV files

1 Answers