How to turn string Date/Time into something usable

[BuilderGuy1]

I have a Date/time as a string and I need to turn it into something usable.
This is an example of what I have:

29 September 2019, 8:50 PM

I have been trying to get this to work looking at other examples and this is what I have (doesn't work).

LOAD CSV WITH HEADERS FROM 'file:///myfile.csv' AS line
WITH line, SPLIT(line.Time, ' ') AS date
CREATE (t:Time {DateTime: TOINT(Line.Time) })
SET t.day = TOINT(date[0]),
t.month = date[1],
t.year = TOINT(date[2]),
t.hour = TOINT(date[3]),
t.ampm = date[4]
;

The error I get is:
Variable Line not defined (line 4, column 33 (offset: 176))
"CREATE (t:Time {DateTime: TOINT(Line.Time) })"

The pointer is under the "L" in the word "Line".

What is the proper way to Split and assign appropriate values to the date and time?

Jason

[BuilderGuy1]

OK, changed "Line" to "line" and it imported data but the data did not import properly.
I got this:
{
"month": "September",
"ampm": "PM",
"day": 29
}
Looks like the comma and the colon are messing things up.
How do I deal with those?

Jason

[Thomas_Silkjaer]

I haven't tested it myself, but look at apoc.date.parse (http://neo4j-contrib.github.io/neo4j-apoc-procedures/3.5/utilities/datetime-conversions/)

[BuilderGuy1]

Thanks ! I'll check this out!
Jason

[BuilderGuy1]

This won't work. apoc.date.parse appears to need a DATE value as input. I have a STRING.
I need to pull apart this STRING somehow and then put it back together as a usable DATE.

Problems I'm having with this:
-SPLIT can not manage more than one delimiter. The comma and colon remain.
-How do I convert "September" to a value that can be saved into a DATE?
-TOINT can't strip out non-numbers
-Once I do have integer values, how do I properly recombine them?
-apoc examples are of no value here.

I am looking at text functions in apoc to see what I can do to remove the comma.

[BuilderGuy1]

I changed this line:
t.year = TOINT(date[2]),

Into:
t.year = TOINT(apoc.text.clean(date[2])),

The apoc.text.clean(text) function will strip anything that isn't alpha numeric so I thought it would strip the comma. Nope. I guess comma's are alpha numeric.
Oddly, the Year now appears in my data !!!
...but it has a comma! It appears as: 2019,
That's one weird INT!

[nielsson4711 · Accepted solution]

The LOAD CVS parsing capabilities provide a basic, but usually sufficient set of tools for your purpose. The crucial ETL step seems to bea decent decomposition of the input along its variety of separators.
Lets have a look:

input "myfile.csv"

date,time
29 September 2019, 8:50 PM
30 September 2019, 7:15 AM

cypher test 01:

LOAD CSV WITH HEADERS FROM 'file:///myfile.csv' AS line FIELDTERMINATOR ','
RETURN line.time, line.date

returns

" 8:50 PM" "29 September 2019"
" 7:15 AM" "30 September 2019"

cypher test 02:

LOAD CSV WITH HEADERS FROM 'file:///myfile.csv' AS line FIELDTERMINATOR ','
WITH SPLIT(trim(line.date), " ") as theDate, SPLIT(trim(line.time), " ") AS theTime
RETURN theDate, theTime

returns

theDate, theTime
["29", "September", "2019"] ["8:50", "PM"]
["30", "September", "2019"] ["7:15", "AM"]

cypher test 03:

LOAD CSV WITH HEADERS FROM 'file:///myfile.csv' AS line FIELDTERMINATOR ','
WITH SPLIT(trim(line.date), " ") as theDate, SPLIT(trim(line.time), " ") AS theTime
WITH theDate, split(theTime[0], ':') AS theTimeUnits, theTime[1] AS theTimeFlag
RETURN theDate, theTimeUnits, theTimeFlag

returns

theDate, theTimeUnits theTimeFlag
["29", "September", "2019"] ["8", "50"] "PM"
["30", "September", "2019"] ["7", "15"] "AM"

[BuilderGuy1]

This is awesome thank you!
Jason

[ameyasoft]

Here is another solution;

If your string is: "29 September 2019, 8:50 PM" then use this script

WITH SPLIT("29 September 2019, 8:50 PM", ',') AS dt1
WITH dt1, SPLIT(dt1[0], ' ') AS dt2, SPLIT (trim(dt1[1]), ' ') As dt3

return toInteger(dt2[0]), dt2[1], dt2[2], dt3[0], dt3[1]

result:

"29", "September", "2019", "8:50", "PM"

[BuilderGuy1]

Thank you everyone! This has been very helpful!

I'm now trying to get the hour to be in 24 hour format.
The problem I'm having is that I don't know how to ADD two numbers together.
I end up concatenating them together.

I'm using this line in a CASE statement:
WHEN 'PM' THEN toInteger(dt4[0]) + 12
This results in:
"2019-09-29T812:51"

I have been scouring the Cypher manual but I can't find a way to do this.
Any thoughts?

J