Handling null values while parsing Excel files

I'm using APOC to import the contents of an Excel file. Where I'm having issues is when a NULL value is encountered during the import. For example if the data looked like this in Excel:

NAME DATE COMPANY
WIndows 10 2015-01-01 Microsoft
Windows 7 null (empty cell) Microsoft
RHEL 6 2016-05-01 Red Hat

When parsing the file, I perform a date conversion (using date()) but it will fail due to the null value in the second entry. I've tried various conditional attempts with CASE, NOT NULL, etc., but still have issues.

Basically, if the cell does NOT have a null value then process the contents otherwise process the next cell. Do I need to do a multiple pass to make this work instead of trying to handle things on a single pass?

Use coalesce() when working with nulls.

You have a common issue a lot of people have; so usually what people will do is replace all nulls with some default value like an empty string -- this effectively eliminates nulls and lets you treat them as if they are some default string of your choosing.

Example:

UNWIND batch AS event
MERGE (r:Record { id: event.id })
   SET r.date = date(coalesce(event.date, "1970-01-01"))

Here, event refers to any line of data you've got. If event.date is null, you'll get January 1st 1970

Thanks for the example. I haven't used the coalesce() function before but it looks to be very useful.

So for a follow-up on your solution. If the date was null, then the default is applied, could I then use the REMOVE function to then delete the properties where the default is applied, just to keep things clean?

So how can I trap a null in order to MERGE a new node and apply the relationship? For example, if the spreadsheet had a Jira ticket number in some of the rows, I would like to create a new node with the Jira info (ID, URL, etc.), but that needs to be done conditionally (only if a ticket entry is present). A ticket may apply to more than one row in this case.

Thanks for the help!

there are two ways of doing that; the CASE method (which it looks like you already looked into) and also WITH/WHERE

For example:

WITH event
WHERE event.jiraTicket IS NOT NULL
MERGE (....)

You can always say "WITH (this and that) WHERE (apply condition to filter) .... do other stuff"

1 Like