CASE(x) WHEN containts Y THEN replace(x, z, c) error

Hey, I just started learning neo4j and I'm facing one small error. Therefor, I want to CREATE a "table" from a csv file but my csv file containts back slashes and forward slashes. I know that these characters are escape characters but I tried to replace them during the CREATE process. This is my cypher:

LOAD CSV WITH HEADERS FROM "file:///comments.csv" AS row
CREATE (n:Comments)
SET n = row,
SET n.Content = CASE(row.Content) WHEN CONTAINS '\\' THEN replace(row.Content, '\\', '\\\\') ELSE row.Content

What I'm doing here is basically checking if row.Content contains '\' and if it does, replace it with double '\\'. However I get an error, the error says:
Invalid input 'n': expected whitespace, comment, '{', node labels, MapLiteral, a parameter, a parameter (old syntax), a relationship pattern, '(', '.', '=' or "+=" (line 4, column 5 (offset: 94))
"SET n.Content = CASE(row.Content) WHEN CONTAINS '\' THEN replace(row.Content, '\', '\\') ELSE row.Content"

I googled this error but I didn't get any solution for my problem.
Thanks in advance

Hello @anthino12 :slight_smile:

You have several syntax errors in your request :slight_smile:, you forget the END at the end and the () can be removed in the CASE clause (doc):

LOAD CSV WITH HEADERS FROM "file:///comments.csv" AS row
CREATE (n:Comments)
SET n = row,
SET n.Content = CASE row.Content WHEN CONTAINS '\\' THEN replace(row.Content, '\\', '\\\\') ELSE row.Content END

Regards,
Cobra

1 Like

Hey @Cobra

I just edited my cypher but I still get the same error. Is it possible to replace a "" in a string?
Meanwhile I was googling and found out that "" can't be replaced with anything because it can't be recognized as a character.

I want to ask you because you're a certified professional :D

Can you give me an example with the current string and the output you want to get? :slight_smile:

1 Like

Sure. The string I'm trying to insert is:
same here, don’t know why… :\

Notice that emoji at the end. I believe that I should replace "\" with "\\" so it can be inserted but I'm not 100% sure.

Anyway, thank you for your answers. Neo4j sure seems to be a great community :)

1 Like

Did you try to insert data without replacing to see what you get? :slight_smile:

Oh yes, I think we are a great community :slight_smile:

I don't think you need to replace anything to be honest :slight_smile:

1 Like

With this:

LOAD CSV WITH HEADERS FROM "file:///comments.csv" AS row
CREATE (n:Comments)
SET n = row

I get this error:
there's a field starting with a quote and whereas it ends that quote there seems to be characters in that field after that ending quote. That isn't supported. This is what I read: 'same here, don’t know why… :",Daniel Nguyen,2009-06-30,1
Notice how the string ends without that :\ emoji.

You will have an issue if you have a ' or " in your string, can you show us the CSV? or some lines of it?

Sure. I'm using this dataset: https://www.kaggle.com/lakritidis/identifying-influential-bloggers-techcrunch?select=comments.csv, the comments part.

This is a small part of the dataset, some fail (those that contain the backslash character in them, they throw an error) while the others get inserted without any problems.
https://pastebin.com/FmbjcY6L

You only want to keep the comments or you want the id, the author and date as well?

Are you using last version of Neo4j? (>4.0)

I want to keep the whole row. The commentID, postID, content (usually strings here contain backslashes), author, date and the vote.

I just checked, my Neo4j version is 3.5.14. Should I update?

I don't think it will be necessary, I was just wondering if you were not on a very old version:)

In the doc, I saw: if dbms.import.csv.legacy_quote_escaping is set to the default value of true, \ is used as an escape character;

Can you check in neo4j.conf, if dbms.import.csv.legacy_quote_escaping set to true or false? :slight_smile:

1 Like

Awkward but my neo4j.conf doesn't seem to contain this kind of path. This is my neo4j.conf:
https://pastebin.com/hQgDc7D1
I don't know if it matters but I use Linux Ubuntu 18.04

EDIT:
I just ran

CALL dbms.listConfig()
YIELD name, value
WHERE name STARTS WITH 'dbms.import'
RETURN name, value
ORDER BY name
LIMIT 3;
and got that dbms.import.csv.legacy_quote_escaping is set to true.

Try to add this in the neo4j.conf file:

dbms.import.csv.legacy_quote_escaping=false

Can you also try this query?

LOAD CSV WITH HEADERS FROM "file:///comments.csv" AS row
CREATE (n:Comments {commentId: toInteger(row[0]), postId: toInteger(row[1]), content: row[2], author : row[3], date: row[4], vote: toInteger(row[5])})
1 Like

Adding dbms.import.csv.legacy_quote_escaping=false fixed my problem. Thank you so much sir, I wasted a good 6 hours on this.

1 Like

Don't worry, it won't be the last time you will spend time to solve a bug or something but you will know the solution for the future :slight_smile:

Moreover, we will always be here to try to help you :slight_smile:

No problem :smile:

1 Like