Import a CSV File

The Xata CLI provides the capability to import records from a local CSV file into a Xata database table.

A CSV file is a regular text file with field content separated by commas (,), usually saved with the extension .csv. The first line in the file works a header that specifies the field names, while the next lines contain the actual data that will be written as records into Xata.

You can find the options relevant to importing records from a CSV file with the command xata import csv --help.

The CLI tool can optionally create your table and its schema, or you can use an already existing table in Xata.

Import Data with an Autogenerated Schema

Here we will go over an example in which the table and schema are created by the Xata CLI import tool.

After following the Getting Started guide to install and login with the CLI, create a Workspace, a Database and a branch (or use the default main branch), so we can have a place in Xata to create our tables.

For the purpose of this example, we assume that the Workspace name is my_workspace, the database name is my_database and the branch is main.

This is some example content for our file which we name file.csv:

1
2
name,team,contributions,is_member
Mary,Development,1000,true

In order to ingest the content of this file we will run the following command:

xata import csv file.csv --create --table my_first_table

The interactive menu will allow us to select the workspace, database and branch in which the table my_first_table will be created:

? Select a workspace ›
❯   my_workspace - my_workspace-q5tboj

? Select a database ›
❯   my_database

? Select a branch ›
❯   main

Upon success, the CLI provides the number of rows that were processed and notifies us that processing completed:

1 rows processed
Finished

By running the CLI command xata schema dump and selecting the same workspace, database and branch, we will retrieve the Schema which was automatically generated by Xata for our table. This is done by Xata figuring out the appropriate column types based on the shape of our values. The columns name and team are created as string, while the contributions column is identified as int and the is_member column is created as bool.

xata schema dump
✔ Select a workspace › my_workspace
✔ Select a database › my_database
✔ Select a branch › main
{
  "tables": [
    {
      "name": "my_first_table",
      "columns": [
        {
          "name": "name",
          "type": "string"
        },
        {
          "name": "team",
          "type": "string"
        },
        {
          "name": "contributions",
          "type": "int"
        },
        {
          "name": "isMember",
          "type": "bool"
        }
      ]
    }
  ]
}

Now that we have a table we can continue inserting more records to it, following the same schema without using the create option:

xata import csv another_file.csv --table my_first_table

Import Data with an Explicitly Defined Schema

It is also possible to instruct the CLI to create specific column types with the combination of --columns and --types arguments, in the order they appear in the CSV file.

Using the following command we could, for example, instead create the team column as a text field instead of string:

xata import csv file.csv --create --table my_second_table --columns=name,team,contributions,is_member --types=string,text,int,bool

For more details on the available column types, you can refer to the Data Model page.

Once more, we can verify the resulting Schema for this table in the output of xata schema dump. It is also possible to skip importing certain columns from our CSV file, by not specifying them in the columns option.

In case our CSV file also contains an id column, such as:

1
2
id,name,team,contributions,is_member
user1,Mary,Development,1000,true

Then the CSV import will automatically use this column to set record ids, instead of leaving it to Xata to autogenerate them. In our example, since we are explicitly defining the column names and types to be used in Xata, we will include the id column of string type in the import command's parameters:

xata import csv my.csv --create --table my_second_table --columns=id,name,team,contributions,is_member --types=string,string,text,int,bool

With this method, each record's unique id will be set using the id column from our CSV file. You should make sure that the id values are unique in the CSV file, otherwise the rows with the same id will be overwritten in the order they are read from the file.

Tips for Working with CSV Files

• We keep enriching the Xata CLI with new features and improvements so make sure you install the latest version.
• As different solutions often make their own choices and conventions, it may be possible that a CSV export from another database or tool requires some processing and adjustments to work with the Xata CLI.
• Check the help output xata import csv --help for the list of available options (configurable batch size, custom delimiter and more).
• If you have any questions, don't hesitate to reach out to us!