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
Datablist is a great CSV viewer and editor. Use CSV and Excel files to import data into your collection. 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.
Create Properties on empty collection
When starting with an empty collection, the File Import assistant lists the columns from your file.
Datablist analyses the data from your file to detect each column's data type. Property types are guessed based on the first 100 items read on the file. For example:
- Columns with valid emails (such as email@example.com) 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
To change the Data Type or to import column as another name, click the Data Type button.
Map columns if collection has properties
On an existing collection with at least one property, you will be asked to map the file columns with your collection properties. 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.
If a column doesn't have a matching property, you can create new properties directly based on the file columns. To create a new property to match your file column, 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 settings are accessible during the File Import process:
- Property name
- Property type
- Do not allow duplicate values - 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 creating or 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.
Loading a big CSV file takes time. Expect 5 minutes to load a 1 million lines CSV file. To cancel the loading process before the end, click the "Stop Import" button. It will stop the import process.
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
Anonymous users have a 10,000 items import limit. Create a free account to remove the limit.
For registered users, import works with CSV and Excel files up to 1,5 million lines (1 million in free plan).