Data clean-up before import

Neo4j Graph Platform Cypher
1

Dear Everyone,

I have a situation where I have a field with a price in it, but it also has the euro sign in the field (See attachment)/ What I would like to do is import the data from that JSON key value pair, but remove the euro sign, remove the empty space between the euro sign and price and also convert remaining information to integer (toInteger function).

I have been trying trim string function, replace string function on the field, but it doesn't work.
I tried to replace with a blank space then checked to see if using a letter "e" would be allowed (debugging). Only got errors

I read all over the Neo4J manual to see if there was a function, but to no avail. The strange thing is I have never seen anyone cleanup data before entering it into the database in any of the courses or youtube tutorials. I was even thinking to use regEx, but once I have the pattern, I do see how I can store just the selected result into some sort of variable or have that as a output for the relationship property.

39
Screenshot 2019-10-28 at 21.26.39.png1406×734 68.2 KB

2

Hey,

I tried more or less the same thing as you but the replace does work for me. See below:

image
image.png1812×836 33.4 KB 

call apoc.load.json("file:///foo.json")
YIELD value
RETURN replace(value.value, "€", "")
3

Hello Mark,

What about the issue with the back ticks being in there. Does that cause an issue?
In one of my attempts I did try Replace, but maybe I missed something.

Greetings

4

We can still use the same approach. See the example below:

image
image.png871×863 36.9 KB 

call apoc.load.json("file:///foo.json")
YIELD value
RETURN replace(value.`value with spaces`, "€", "")
5

Here is my version that I did, but I must be missing something.

41
Screenshot 2019-10-29 at 14.42.41.png1749×1065 113 KB

6

I just learned something. It has to be after the Return statement. So does this mean that Replace always need a place to put it's output?

Before I attempt to put it in a property in a database. I must ask. How does replace work in general?
I read the manual multiple times while trying the last days, and nowhere did it mention this gotcha about having to be before the return clause, or even how it is to be used in conjunction with other things.

It worked!!!!

But can you explain the above question about the best practices with replace?

Greetings,
Jeffrey

09
Screenshot 2019-10-29 at 14.51.09.png1749×528 21.3 KB

7

Trying to turn it into an integer gave the following incorrect end result

58
Screenshot 2019-10-29 at 14.53.58.png1749×1035 81.3 KB

8

There was a space after the euro sign, so the toInterger was having problems with it, and also I needed a float. This finally gave me the end result I wanted.

See attachment

30
Screenshot 2019-10-29 at 14.59.30.png1749×1035 91.9 KB

9

Mark,

How do I deal with more complex combinations that redEx normally can take of?
Say for instance that the euro sign was at the end.

Thanks for all your help thus so far.

10

Hi,

I think it might be worth looking at APOC, which is Neo4j's standard library, if you haven't used it yet. There are a bunch of useful functions and procedures in there, one of which is handy for doing text wrangling:

RETURN toInteger(apoc.text.replace('€4000€', '€', ''))

https://neo4j.com/docs/labs/apoc/current/misc/text-functions/#text-functions-regex

You can find installation instructions for the library here - https://neo4j.com/docs/labs/apoc/current/introduction/#installation

1 Like