[go: up one dir, main page]
More Web Proxy on the site http://driver.im/

Loading Excel (XLS)

Library Requirements

For loading XLS we’re using the Apache POI library, which works well with old and new Excel formats, but is quite large. That’s why we decided not to include it into the apoc jar, but make it an optional dependency.

These dependencies are included in apoc-xls-dependencies-4.3.0.12.jar, which can be downloaded from the releases page. Once that file is downloaded, it should be placed in the plugins directory and the Neo4j Server restarted.

Alternatively, you can download these jars from Maven Repository (putting them into plugins directory as well):

For XLS files:
Additional for XLSX files:

Usage

The usage of apoc.load.xls is similar to apoc.load.csv with the main difference the ability to select a worksheet or a range from a sheet to load.

You can either select the sheet by name like 'Kids', or offset like 'Results!B2:F3'

CALL apoc.load.xls({url}, {Name of sheet}, {config})

The {config} parameter is a map

name description

mapping

{mapping:{'<sheet>':{type:'<type>', dateFormat: '<format>', dateParse: [<formats>]}}}

<sheet>

name of the sheet

<type>

Default String, The type of the conversion requested (STRING, INTEGER, FLOAT, BOOLEAN, NULL, LIST, DATE, DATE_TIME, LOCAL_DATE, LOCAL_DATE_TIME, LOCAL_TIME, TIME)

dateFormat: <format>

Convert the Date into String (only String is allowed)

dateParse: [<formats>]

Convert the String into Date (Array of strings are allowed)

In dateParse the first format matched return the date formatted, otherwise it will return an error

In format config you can use the pattern describe as the Temporal functions: temporal functions

Examples for apoc.load.xls

CALL apoc.load.xls('file:///path/to/file.xls','Full',{mapping:{Integer:{type:'int'}, Array:{type:'int',array:true,arraySep:';'}}})
apoc.load.xls 
CALL apoc.load.xls('http://bit.ly/2nXgHA2','Kids')

Some examples with type/dateFormat and dateParse:

CALL apoc.load.xls('test_date.xlsx','sheet',{mapping:{Date:{type:'String'}}})
apoc.load.xls 1
Figure 1. results
CALL apoc.load.xls('test_date.xlsx','sheet',{mapping:{Date:{type:'String',dateFormat:'iso_date'}}})
apoc.load.xls 2
Figure 2. results
CALL apoc.load.xls('test_date.xlsx','sheet',{mapping:{Date:{type:'String',dateParse:["wrongPath", "dd-MM-yyyy", "dd/MM/yyyy", "yyyy/MM/dd", "yyyy/dd/MM", "yyyy-dd-MM'T'hh:mm:ss"]}}})
apoc.load.xls 3
Figure 3. results