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

[anthino12]

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

[Cobra]

Hello @anthino12

You have several syntax errors in your request , 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

[anthino12]

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 😄

[Cobra]

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

[anthino12]

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 🙂

[Cobra]

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

Oh yes, I think we are a great community

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

[anthino12]

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.

[Cobra]

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

[anthino12]

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

[Cobra]

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)

[anthino12]

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?

[Cobra]

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?

[anthino12]

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.

[Cobra · Accepted solution]

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])})

[anthino12]

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

[Cobra]

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

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

No problem