How to iterate using LOAD CSV, UNWIND/FOREACH and WITH clauses?

[kumar_ais]

I want to run the below query for numbers 1 through 14 i.e quantity1 to quantity14 -

LOAD CSV WITH HEADERS FROM '<path>' AS row 
WITH row WHERE row.quantity1 IS NOT NULL
MATCH (m:main {main_id: row.main}) 
MERGE (q:quantity {quantity : row.quantity1}) 
MERGE (m)-[:main_has_quantity]->(q);

What the command does : The command reads a csv, creates the quantity node if it is not null and if doesn't already exist and then creates a relationship between the quantity node and it's corresponding main node. I want to do this for columns quantity1 through quantity14 in the csv.

I tried to use UNWIND and FOREACH and both commands throw errors :

LOAD CSV WITH HEADERS FROM '<path>' AS row 
UNWIND range(1,14) as i
WITH row WHERE row.quantity+toString(i) IS NOT NULL 
MATCH (m:main {main_id: row.main}) 
MERGE (q:quantity {quantity : row.quantity+toString(i)}) 
MERGE (m)-[:main_has_quantity]->(q);

I got the error "Variable i not defined (line 5, column 37 (offset: 246))
"MERGE (q:quantity {quantity : row.quantity+toString(i)})" "

I used FOREACH in place of UNWIND. That did not work because I cannot use WITH clause inside FOREACH.

How best to achieve the result I am looking for?

P.S I am using the Neo4j community version.

Regards,
AK

[koji]

Hi @kumar.ais

This is my csv file.

main,quantity1,quantity2,quantity3,quantity4,quantity5,quantity6,quantity7,quantity8,quantity9,quantity10,quantity11,quantity12,quantity13,quantity14
1,q1_1,q1_2,q1_3,q1_4,q1_5,q1_6,q1_7,q1_8,q1_9,q1_10,q1_11,q1_12,q1_13,q1_14
2,q2_1,q2_2,q2_3,q2_4,q2_5,q2_6,q2_7,q2_8,q2_9,q2_10,q2_11,q2_12,q2_13,q2_14

I have tested getting a property from a list of properties.

WITH ['quantity1','quantity2','quantity3','quantity4','quantity5',
      'quantity6','quantity7','quantity8','quantity9','quantity10',
      'quantity11','quantity12','quantity13','quantity14'] AS quantity1_14
LOAD CSV WITH HEADERS FROM 'file:///q1to14.csv' AS row
UNWIND quantity1_14 as quantity
RETURN row[quantity]

Each property has been retrieved.
You can use "row[quantity]"

row[quantity]
"q1_1"
"q1_2"
"q1_3"
"q1_4"
"q1_5"
"q1_6"
"q1_7"
"q1_8"
"q1_9"
"q1_10"
"q1_11"
"q1_12"
"q1_13"
"q1_14"
"q2_1"
"q2_2"
"q2_3"
"q2_4"
"q2_5"
...