Want to find the answer quick?
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!