Datablist has three ways to import data:
- Using external files
- With copy/pasting from spreadsheet tools or tabular data
- Or using Datablist API
Import from a CSV or Excel file
You can easily import external data using CSV or Excel files. Select the collection where your data will be imported or create a new collection if needed.
On the first step, click the load zone to select a file from your computer, or drag and drop a file to get started. Both CSV and Excel files are accepted:
- CSV files must have a
- Excel files must have one of the following file extensions:
Notes on importing an Excel file:
- Cells with formulas are imported with computed values
- Empty rows are skipped. If headers are on the second row, it will work.
Map columns or create properties
After loading your file, you will be asked to map the file columns with your collection properties. When you already have collection properties configured, simply map the properties. If not, you can create new properties directly based on the file columns.
When listing your file columns, Datablist will try to auto map the columns with existing collection properties.
Notes: The auto-mapping algorithm works by removing all non-alphanumeric characters and comparing columns with property names in lower case
You can map one of your columns with the built-in createdAt property to set the items created timestamps. To do so, add the built-in property to your collection. Then during the import process, map your file column to the property.
When starting with an empty collection, you will want to create new properties to match your file columns. To do so, click the + button next to the property mapping select.
A modal will open to create a property. The name is set based on the column and a property type is suggested.
Property types are guessed based on the first 100 items read on the file. For example:
- Columns with valid emails (such as firstname.lastname@example.org) are defined as Email
- Columns with full URLs (with HTTP, HTTPS or FTP protocol) are defined as URLs
- Columns with text longer than 70 characters are defined as Long Text
- Columns with valid ISO 8601 DateTime formats are defined as DateTime
- Columns with
Checkedare detected as Checkboxes
- Columns with valid numbers are detected as Number
Importing with unique value property - When one of your collection properties has a "Unique Values option", a Merging Option step will be shown. Please check the bulk updates documentation page to know more.
Converting to data types
After mapping your columns, Datablist will parse and convert your lines according to the property data types.
- Checkbox -
Checked(case insensitive) are converted to checkbox values
- DateTime - Must respect the ISO 8601 format. If not, data might be converted to some weird dates.
- Number - Numeric values must be written with numerical digits. Ex:
one hundredis not valid. Decimal must use a dot separator.
If imported data can't be converted to data type, data is passed as a Text. Review your data to detect any error and then click Import items to run the import process.
Run the import
Once ready, run the import to create the items. When the import is finished, a report is available with the number of items created.
Import with copy/pasting
To help you import data from any spreadsheet tools to Datablist, you can use copy/pasting. This feature allows you to import data from Google Sheets, Microsoft Excel, Apple Numbers, etc.
In Datablist, select the collection where your data will be imported or create a new collection if needed. Then paste your data by selecting "Edit -> Paste" in your browser menu bar or using the Ctrl + v keyboard shortcut. A modal will open to configure the data import.
First, if your pasted data include headers/columns, set "First row contains headers" to true. The first row will be skipped and used to map or create properties.
If your collection has properties, map the columns with them. If you need to create new properties that match the columns, click the + button.
When your pasted data contains headers, a property will be automatically created using your header name. You will be able to edit it later.
For data without headers, clicking the + button will show an input to enter a property name.
Once ready, click Import to run the import process.
Note: Pasting data containing a single line will overwrite selected cell text
Currently, a collection has a 10,000 items limit. CSV files with more than 10k items are truncated to the first 10k lines.