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

Neo4j Graph Platform Cypher
1

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

2

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
3

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

4

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

1 Like
5

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
6

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
7

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.

8

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

9

Sure. I'm using this dataset: Identifying Influential Bloggers: Techcrunch | Kaggle, 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.

10

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)

11

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?

12

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
13

Awkward but my neo4j.conf doesn't seem to contain this kind of path. This is my neo4j.conf:

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.
14

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
15

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
16

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