Import data from CSV

If you have data in CSV format, there are two ways of import into a running Memgraph instance: using the LOAD CSV Cypher clause or directly through Memgraph Lab using a CSV file import method.

In this section of documentation, we’ll dive deeper into the recommended method for CSV import, using the LOAD CSV Cypher clause. If you’re interested in exploring the CSV file import method, visit the import example under the CSV file import documentation.

LOAD CSV Cypher clause

The LOAD CSV clause reads row by row from a CSV file, binds the contents of the parsed row to the specified variable, and populates the database if it is empty, or appends new data to an existing dataset. Memgraph supports the Excel CSV dialect, as it’s the most common one.

LOAD CSV clause syntax

LOAD CSV FROM "https://example.com/path/to/your-data.csv" WITH HEADER AS row

The syntax of the LOAD CSV clause is:

LOAD CSV FROM <csv-location> ( WITH | NO ) HEADER [IGNORE BAD] [DELIMITER <delimiter-string>] [QUOTE <quote-string>] [NULLIF <nullif-string>] AS <variable-name>

• <csv-location> is a string of the location of the CSV file.
  Without a URL protocol, it refers to a file path. There are no restrictions on where in your file system the file can be located, as long as the path is valid (i.e., the file exists). If you are using Docker to run Memgraph, you will need to copy the files from your local directory into Docker container where Memgraph can access them.
  If using http://, https://, or ftp:// the CSV file will be fetched over the network.

• ( WITH | NO ) HEADER flag specifies whether the CSV file has a header, in which case it will be parsed as a map, or it doesn’t have a header, in which case it will be parsed as a list.

  If the WITH HEADER option is set, the very first line in the file will be parsed as the header, and any remaining rows will be parsed as regular rows. The value bound to the row variable will be a map of the form:

  { ( "header_field" : "row_value" )? ( , "header_field" : "row_value" )* }

  If the NO HEADER option is set, then each row is parsed as a list of values. The contents of the row can be accessed using the list index syntax. Note that in this mode, there are no restrictions on the number of values a row contains. This isn’t recommended, as the user must manually handle the varying number of values in a row.

• IGNORE BAD flag specifies whether rows containing errors should be ignored or not. If it’s set, the parser attempts to return the first valid row from the CSV file. If it isn’t set, an exception will be thrown on the first invalid row encountered.

• DELIMITER <delimiter-string> option enables the user to specify the CSV delimiter character. If it isn’t set, the default delimiter character , is assumed.

• QUOTE <quote-string> option enables the user to specify the CSV quote character. If it isn’t set, the default quote character " is assumed.

• NULLIF <nullif-string> option enables you to specify a sequence of characters that will be parsed as null. By default, all empty columns in Memgraph are treated as empty strings, so if this option is not used, no values will be treated as null.

• <variable-name> is a symbolic name representing the variable to which the contents of the parsed row will be bound to, enabling access to the row contents later in the query. The variable doesn’t have to be used in any subsequent clause.

LOAD CSV clause specificities

When using the LOAD CSV clause please keep in mind:

• The parser parses the values as strings so it’s up to the user to convert the parsed row values to the appropriate type. This can be done using the built-in conversion functions such as ToInteger, ToFloat, ToBoolean etc. Consult the documentation on the available conversion functions.

  If all values are indeed strings and the file has a header, you can import data using the following query:

  LOAD CSV FROM "/people.csv" WITH HEADER AS row
  CREATE (p:People) SET p += row;

• The LOAD CSV clause is not a standalone clause, meaning a valid query must contain at least one more clause, for example:

  LOAD CSV FROM "/people.csv" WITH HEADER AS row
  CREATE (p:People) SET p += row;

  In this regard, the following query will throw an exception:

  LOAD CSV FROM "/file.csv" WITH HEADER AS row;

  Adding a MATCH or MERGE clause before LOAD CSV allows you to match certain entities in the graph before running LOAD CSV, optimizing the process as matched entities do not need to be searched for every row in the CSV file.

  But, the MATCH or MERGE clause can be used prior the LOAD CSV clause only if the clause returns only one row. Returning multiple rows before calling the LOAD CSV clause will cause a Memgraph runtime error.

• The LOAD CSV clause can be used at most once per query, so queries like the one below will throw an exception:

  LOAD CSV FROM "/x.csv" WITH HEADER as x
  LOAD CSV FROM "/y.csv" WITH HEADER as y
  CREATE (n:A {p1 : x, p2 : y});

Increase import speed

The LOAD CSV clause will create relationships much faster and consequently speed up data import if you create indexes on nodes or node properties once you import them:

  CREATE INDEX ON :Node(id);

If the LOAD CSV clause is merging data instead of creating it, create indexes before running the LOAD CSV clause.

You can also speed up import if you switch Memgraph to analytical storage mode. In the analytical storage mode there are no ACID guarantees besides manually created snapshots but it does increase the import speed up to 6 times with 6 times less memory consumption. After import you can switch the storage mode back to transactional and enable ACID guarantees.

You can switch between modes within the session using the following query:

STORAGE MODE IN_MEMORY_{TRANSACTIONAL|ANALYTICAL};

If you use IN_MEMORY_ANALYTICAL mode and have nodes and relationships stored in separate CSV files, you can run multiple concurrent LOAD CSV queries to import data even faster. In order to achieve the best import performance, split your nodes and relationships files into smaller files and run multiple LOAD CSV queries in parallel. The key is to run all LOAD CSV queries, which create nodes first. After that, run all LOAD CSV queries that create relationships.

The LOAD CSV clause can handle CSVs that are compressed with gzip or bzip2. This can speed up the time it takes to fetch and/or load the file.

If you are using on-disk storage mode, consider using Edge import mode to get the best import performance.

Import files WITH and NO HEADER

The goal of this example is to import two CSV files.

One file contains data we will use to create nodes labeled :Person, and the other file will be used to connect those nodes with the :IS_FRIENDS_WITH relationship.

There are also two variations of the files: files with a header and files without a header.

LOAD CSV FROM "/path-to/people_nodes_wh.csv" WITH HEADER AS row
CREATE (p:row.label {id: row.id, name: row.name});

LOAD CSV FROM "/path-to/people_nodes_nh.csv" NO HEADER AS row
CREATE (p:Person {id: row[0], name: row[1]});

CREATE INDEX ON :Person(id);

LOAD CSV FROM "/path-to/people_relationships_wh.csv" WITH HEADER AS row
MATCH (p1:Person {id: row.id_from}), (p2:Person {id: row.id_to})
CREATE (p1)-[:row.type]->(p2);

LOAD CSV FROM "/path-to/people_relationships.csv" NO HEADER AS row
MATCH (p1:Person {id: row[0]}), (p2:Person {id: row[1]})
CREATE (p1)-[:IS_FRIENDS_WITH]->(p2);

MATCH p=()-[]-() RETURN p;

Import data from a single CSV file containing both nodes and relationships

This example will show how to use set of Cypher queries to import data into Memgraph from a single CSV file containing both nodes and relationships. This file has header row.

The people_friendship.csv file is structured to contain both people and their relationships:

• type: Indicates the type of record. It has two possible values: PERSON for individual records and IS_FRIEND for relationships.
• id: Unique identifier for a person. This is filled only for PERSON records.
• name: Name of the person. This is also filled only for PERSON records.
• id_from and id_to: Used to denote relationships. For IS_FRIEND records, id_from is the ID of the person from whom the friendship originates, and id_to is the ID of the friend.

This structure allows representation of both nodes (persons) and their relationships (friendships) within a single CSV file.

type,id,name,id_from,id_to
PERSON,100,Daniel,,
PERSON,101,Alex,,
PERSON,102,Sarah,,
PERSON,103,Mia,,
PERSON,104,Lucy,,
IS_FRIEND,,,100,101
IS_FRIEND,,,100,102
IS_FRIEND,,,100,103
IS_FRIEND,,,101,103
IS_FRIEND,,,102,104

LOAD CSV FROM "/path-to/people_friendship.csv" WITH HEADER AS row
WITH row WHERE row.type = 'PERSON'
CREATE (:Person {id: row.id, name: row.name});

CREATE INDEX ON :Person(id);

LOAD CSV FROM "/path-to/people_friendship.csv" WITH HEADER AS row
WITH row WHERE row.type = 'IS_FRIEND'
MATCH (p1:Person {id: row.id_from}), (p2:Person {id: row.id_to})
CREATE (p1)-[:IS_FRIENDS_WITH]->(p2);

MATCH p=()-[]-() RETURN p;

Import multiple CSV files with distinct graph objects

In this example, the data is split across four files, each file contains nodes of a single label or relationships of a single type. All files have a header.

LOAD CSV FROM "/path-to/people_nodes_wh.csv" WITH HEADER AS row
CREATE (n:Person {id: row.id, name: row.name, age: ToInteger(row.age), city: row.city});

LOAD CSV FROM "/path-to/restaurants_nodes.csv" WITH HEADER AS row
CREATE (n:Restaurant {id: row.id, name: row.name, menu: row.menu});

CREATE INDEX ON :Person(id);

LOAD CSV FROM "/path-to/people_relationships.csv" WITH HEADER AS row
MATCH (p1:Person {id: row.first_person})
MATCH (p2:Person {id: row.second_person})
CREATE (p1)-[f:IS_FRIENDS_WITH]->(p2)
SET f.met_in = row.met_in;

LOAD CSV FROM "/path-to/restaurants_relationships.csv" WITH HEADER AS row
MATCH (p1:Person {id: row.PERSON_ID})
MATCH (re:Restaurant {id: row.REST_ID})
CREATE (p1)-[ate:ATE_AT]->(re)
SET ate.liked = ToBoolean(row.liked);

MATCH p=()-[]-() RETURN p;