Getting Neo.DatabaseError.General.UnknownError while loading data from csv file

[akshat_mittal]

Hi team,

Please suggest how to solve the below error in neo4j.

Error while loading data-
Neo.DatabaseError.General.UnknownError: At /home/jioapp/neo4j-enterprise-3.5.5/import/super.csv @ position 129 - 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: '"e'

CSV file content-

[root@jmngd1bae70v12 import]# cat super.csv
ename,ip,circle
rrh001,10.1.1.1,E
rrh002,10.1.1.2,W
rrh003,10.1.1.3,W
eer004,10.1.1.3,S
eeer05,11.1.2.4,N
"eer007",10.2.3.4,E
""eer008"",10.2.3.5,W

Query used to load data
load csv with headers from "file:///super.csv" as row with row
MERGE (a:rambo {ename:REPLACE(row.ename,'""','"')})
ON CREATE SET
a.ip=row.ip,
a.circle = row.circle
RETURN a.ename

Please help in troubleshooting this issue.

Regards
Akshat

[stefan_armbrust]

Your csv file seems to be not compliant to https://tools.ietf.org/html/rfc4180.

Try to fix the csv e.g. using https://csvkit.readthedocs.io/en/latest/scripts/csvclean.html.

[akshat_mittal]

Hello Stefan,

Thanks a lot for your inputs !!

Actually , problem is we are getting csv files from another system and consuming them in Neo4j. In short , We dont have a control over in-coming data in csv file.
So Can we use some cypher query in neo4j to handle this type of data issue?

Regards
Akshat

[stefan_armbrust]

You can try if apoc.load.csv is more tolerant, but I doubt. The ultmative solution is to ensure you get clean data from the producer, the other option is to include a call to csvclean into your data loading pipeline.

[akshat_mittal]

Hello Stefan ,

Thanks a lot for your much needed-help.

I will try to use the given options.

BTW ,
what is wrong in the below query to handle that bad records?
MERGE (a:rambo {ename:REPLACE(row.ename,'""','"')})

""eer008"",10.2.3.5,W

Regards
Akshat

[stefan_armbrust]

The error happens during parsing, you cannot capture this on a cypher level.

[akshat_mittal]

Thanks a lot Stefan !!

Stay Safe!

[yyyguy]

You may want to look at a data cleansing tool like OpenRefine to deal with the CSV format issue.

-yyyguy