Import CSV data using CLI

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

In the CLI, you can find the options relevant to importing records from a CSV file using 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.

You can use the Xata CLI import tool to create a table and schema. After following the getting started guide to install and log in with the CLI, create a Workspace, a database and a branch (or use the default main branch).

In the following example, assume that the Workspace name is my_workspace, the database name is my_database and the branch is main.

The following is example content for a file which is named file.csv:

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

To ingest the content of this file, run the following command:

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

The interactive menu will allow you 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

The CLI provides the number of rows that were processed and notifies you that processing has completed:

1 rows processed
Finished

By running the CLI command xata schema dump and selecting the same workspace, database and branch, you will retrieve the schema which was automatically generated by Xata for the table. This is done by Xata figuring out the appropriate column types based on the shape of your 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 you have a table, you 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

It's 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 you 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.

You 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 your CSV file, by not specifying them in the columns option.

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

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 you are explicitly defining the column names and types to be used in Xata, you'll 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 ensure 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.

To link records across tables, specify the link column type. The name of the column must match the name of an existing table and the column's values must exist as record IDs in the linked table.

xata import csv my.csv --create --table my_second_table --columns=id,my_first_table --types=string,link

• Xata continues to enrich 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!