3.3.6. LOAD CSV

LOAD CSV is used to import data from CSV files.

Configuration settings for file URLs
dbms.security.allow_csv_import_from_file_urls
This setting determines if Cypher will allow the use of file:/// URLs when loading data using LOAD CSV. Such URLs identify files on the filesystem of the database server. Default is true. Setting dbms.security.allow_csv_import_from_file_urls=false will completely disable access to the file system for LOAD CSV.
dbms.directories.import
Sets the root directory for file:/// URLs used with the Cypher LOAD CSV clause. This must be set to a single directory on the filesystem of the database server, and will make all requests to load from file:/// URLs relative to the specified directory (similar to how a unix chroot operates). The default value is import. This is a security measure which prevents the database from accessing files outside the standard import directory. Setting dbms.directories.import to be empty removes this security measure and instead allows access to any file on the system. This is not recommended.

File URLs will be resolved relative to the dbms.directories.import directory. For example, a file URL will typically look like file:///myfile.csv or file:///myproject/myfile.csv.

See the examples below for further details.

There is also a worked example, see Section 3.3.14, “Importing CSV files with Cypher”.

3.3.6.1. CSV file format

The CSV file to use with LOAD CSV must have the following characteristics:

  • the character encoding is UTF-8;
  • the end line termination is system dependent, e.g., it is \n on unix or \r\n on windows;
  • the default field terminator is ,;
  • the field terminator character can be change by using the option FIELDTERMINATOR available in the LOAD CSV command;
  • quoted strings are allowed in the CSV file and the quotes are dropped when reading the data;
  • the character for string quotation is double quote ";
  • the escape character is \.

3.3.6.2. Import data from a CSV file

To import data from a CSV file into Neo4j, you can use LOAD CSV to get the data into your query. Then you write it to your database using the normal updating clauses of Cypher.

artists.csv. 

"1","ABBA","1992"
"2","Roxette","1986"
"3","Europe","1979"
"4","The Cardigans","1992"

Query. 

LOAD CSV FROM 'https://neo4j.com/docs/developer-manual/3.1/csv/artists.csv' AS line
CREATE (:Artist { name: line[1], year: toInt(line[2])})

A new node with the Artist label is created for each row in the CSV file. In addition, two columns from the CSV file are set as properties on the nodes.

Result. 

+-------------------+
| No data returned. |
+-------------------+
Nodes created: 4
Properties set: 8
Labels added: 4

3.3.6.3. Import data from a CSV file containing headers

When your CSV file has headers, you can view each row in the file as a map instead of as an array of strings.

artists-with-headers.csv. 

"Id","Name","Year"
"1","ABBA","1992"
"2","Roxette","1986"
"3","Europe","1979"
"4","The Cardigans","1992"

Query. 

LOAD CSV WITH HEADERS FROM 'https://neo4j.com/docs/developer-manual/3.1/csv/artists-with-headers.csv' AS line
CREATE (:Artist { name: line.Name, year: toInt(line.Year)})

This time, the file starts with a single row containing column names. Indicate this using WITH HEADERS and you can access specific fields by their corresponding column name.

Result. 

+-------------------+
| No data returned. |
+-------------------+
Nodes created: 4
Properties set: 8
Labels added: 4

3.3.6.4. Import data from a CSV file with a custom field delimiter

Sometimes, your CSV file has other field delimiters than commas. You can specify which delimiter your file uses using FIELDTERMINATOR.

artists-fieldterminator.csv. 

"1";"ABBA";"1992"
"2";"Roxette";"1986"
"3";"Europe";"1979"
"4";"The Cardigans";"1992"

Query. 

LOAD CSV FROM 'https://neo4j.com/docs/developer-manual/3.1/csv/artists-fieldterminator.csv' AS line FIELDTERMINATOR ';'
CREATE (:Artist { name: line[1], year: toInt(line[2])})

As values in this file are separated by a semicolon, a custom FIELDTERMINATOR is specified in the LOAD CSV clause.

Result. 

+-------------------+
| No data returned. |
+-------------------+
Nodes created: 4
Properties set: 8
Labels added: 4

3.3.6.5. Importing large amounts of data

If the CSV file contains a significant number of rows (approaching hundreds of thousands or millions), USING PERIODIC COMMIT can be used to instruct Neo4j to perform a commit after a number of rows. This reduces the memory overhead of the transaction state. By default, the commit will happen every 1000 rows. For more information, see Section 3.3.15, “USING PERIODIC COMMIT”.

Query. 

USING PERIODIC COMMIT
LOAD CSV FROM 'https://neo4j.com/docs/developer-manual/3.1/csv/artists.csv' AS line
CREATE (:Artist { name: line[1], year: toInt(line[2])})

Result. 

+-------------------+
| No data returned. |
+-------------------+
Nodes created: 4
Properties set: 8
Labels added: 4

3.3.6.6. Setting the rate of periodic commits

You can set the number of rows as in the example, where it is set to 500 rows.

Query. 

USING PERIODIC COMMIT 500
LOAD CSV FROM 'https://neo4j.com/docs/developer-manual/3.1/csv/artists.csv' AS line
CREATE (:Artist { name: line[1], year: toInt(line[2])})

Result. 

+-------------------+
| No data returned. |
+-------------------+
Nodes created: 4
Properties set: 8
Labels added: 4

3.3.6.7. Import data containing escaped characters

In this example, we both have additional quotes around the values, as well as escaped quotes inside one value.

artists-with-escaped-char.csv. 

"1","The ""Symbol""","1992"

Query. 

LOAD CSV FROM 'https://neo4j.com/docs/developer-manual/3.1/csv/artists-with-escaped-char.csv' AS line
CREATE (a:Artist { name: line[1], year: toInt(line[2])})
RETURN a.name AS name, a.year AS year, length(a.name) AS length

Note that strings are wrapped in quotes in the output here. You can see that when comparing to the length of the string in this case!

Result. 

+--------------------------------+
| name           | year | length |
+--------------------------------+
| "The "Symbol"" | 1992 | 12     |
+--------------------------------+
1 row
Nodes created: 1
Properties set: 2
Labels added: 1