How to split columns into array when the columns is unknown?

CALL apoc.load.csv('sport.csv') 
yield map as row 

MERGE (m:Sport{ID: row.ID}) 
ON CREATE SET m += row 
ON MATCH SET m += row
RETURN count(m) as mcount

How can I apply a split() function based on a delimiter ';' to each of the row columns? The "m += row" process all the columns of a row together.

Please post one row of data. When you say map is it a key value format?

It's just regular csv format. For example:

name title description price

In which 'title', 'name', or 'price' columns may all have multiple values:

John;Mary  A good book;A bad book  $7;$20
Smith  A  book  $20;$2

These are fake data, but just illustrate the situation. I actually came up with a solution like this:

CALL apoc.load.csv('Sports.csv') 
yield map as row 
MERGE (m:Coach {ID: row._ID})
ON CREATE SET m += row { .*, alias: split(row.alias, '|'), awards: split(row.awards, '|') }
ON MATCH SET m += row { .*, alias: split(row.alias, '|'), awards: split(row.awards, '|') }
RETURN count(m) as mcount

This requires me to specify the column names that need to be split, 'alias' & 'awards'. In order to do this, for each node type, I defined what possible columns that need this split operation, then in my code I can retrieve those predefined columns and use the cypher like above to do the conversion.

It would be great if I don't need to predefine the columns for this purpose.

Use split function for every column and it works.

Created a csv file:

1|John;Mary|A good book;A bad book|$7;$20

with toInteger(row._ID) as ID, split (, ';') as nme, split(row.title, ';') as title, split(row.price, ';') as price
merge (a:LV {id: ID, name: nme, title: title, price: price})

Screen Shot 2020-11-05 at 11.54.42 PM

1 Like