Data Import using CSV format
This topic describes fast data imports using CSV files.
General information
- Before import, your Tables need to be deployed.
- CSV files only support comma separated values, if in doubt, it might be a good idea to use a linter like CSVlint to check the file.
- A well formatted file has a header with names of columns available for the given type, and one or more rows of data.
- If you test your imports and need to clean Instance data, use the
pos-cli data clean <env>
command. Note, that this command will work only on staging Instances. - Import will save data as is, without any validation. It means, that if for example there exists a record with id 1 and it belongs to Table "Post", and your zip file includes records.csv with a row that defines
id
=1 as a "Comment", you will move this record to different Table. - The
id
field is not required - if omitted, the record will be inserted (see examples at the end of the article) - Import will only create or update records, nothing will be deleted
- The
id
field is used as a unique key and determines whether record will be created or updated (overwritten)
Data Import with the CLI
With pos-cli data, you can export/import Instance data (information entered by the users).
platformOS CLI provides the pos-cli data import
command for uploading and importing data from a JSON file (deprecated) or ZIP archive, which should include CSV files. It accepts one option:
- --path (short: -p): A path to a JSON or ZIP files to be imported
Under the hood, the CLI uses the GraphQL import API that's described in the next section. However, using the CLI provides some major benefits:
- Uploading file from disk
- Leveraging the CLI's authentication mechanism (i.e. you don't need to manually send your authentication token)
Zip format
The required structure of a zip file is as follows:
data.zip
├── records.csv
├── users.csv
└── activity_steams.csv
For larger amount of data, you can split it across multiple files:
data.zip
├── records.0.csv
├── records.1.csv
├── records.2.csv
├── users.csv
└── activity_steams.csv
To import data.zip, the following command can be used:
pos-cli data import --path=data.zip --zip
Example file for Records
id,user_id,created_at,updated_at,properties,model_schema
1,,2020-03-13 11:22:54.825836,2020-03-17 05:32:41.477453,"{""name"": ""Buy milk"", ""done"": ""false""}",todo
2,,2020-03-13 11:27:26.236759,2020-03-17 05:39:34.765109,"{""name"": ""Do code reviews"", ""done"": ""true""}",todo
3,,2020-03-13 11:27:26.236759,2020-03-17 05:39:34.765109,"{""name"": ""Fix a tpyo"", ""done"": ""false""}",todo
This file will import Records matching the Table for: "todo", which definition app/schema/todo.yml
could look like:
name: todo
properties:
- name: name
type: string
- name: done
type: boolean
If you notice, there's a typo in the object with id=3, to fix it you can edit the file like this:
id,user_id,created_at,updated_at,properties,model_schema
1,,2020-03-13 11:22:54.825836,2020-03-17 05:32:41.477453,"{""name"": ""Buy milk"", ""done"": ""false""}",todo
2,,2020-03-13 11:27:26.236759,2020-03-17 05:39:34.765109,"{""name"": ""Do code reviews"", ""done"": ""true""}",todo
3,,2020-03-13 11:27:26.236759,2020-03-17 05:39:34.765109,"{""name"": ""Fix a typo"", ""done"": ""true""}",todo
And now importing will just update the data of matching rows. Even if you add new rows, only they will be added, existing rows will not be deleted.
Example file for Users
id,email,encrypted_password,created_at,updated_at,name,authentication_token,slug,time_zone,first_name,middle_name,last_name,external_id,properties
1,api@example.com,abcdefghijaklmnoprst111,2020-03-11 05:44:13.302432,2020-03-11 05:44:13.302432,api@example.com,xyz123fhfhfh,api-example-com,,John,A,Smith,af56f347-ecdd-4636-8cf6-76aa58f89b61,{}
It is important to note that encrypted_password
will only work if you are importing data previously exported from our system through other means. So setting passwords this way will not work: they will be saved but the system will not accept them when checking through application code. It would be unsafe to set passwords this way so it's better to use a random value here and require users to change their passwords after migration.
Example file for [DEPRECATED] UserProfiles
id,user_id,proile_type,created_at,updated_at,properties,instance_profile_type_name
1,2,"buyer",2020-03-11 05:44:13.302432,2020-03-11 05:44:13.302432,"{""name"": ""Our Services""}","modules/menu/buyer"
Importing images/files using CLI
In order to import images/files to platformOS, you need to have them on your disk. For example, if you have an existing file seed/images.zip
:
seed
└── images.zip
└── sample_item_photo
├── photo1.jpg
└── photo2.jpg
You can use the following command to upload those two sample photos:
pos-cli uploads push --path=seed/images.zip
Typically, you would want to use data import as well to be able to use uploaded images/files.
Let's say you have a Table called photo defined in app/schema/photo.yml
:
name: photo
properties:
- name: object_uuid
- name: photo_type
- name: photo
type: upload
options:
versions:
- name: sm
output:
format: jpeg
quality: 70
resize:
width: 200
height: 200
fit: inside
without_enlargement: true
You could then upload the following records.csv in order to be able to use imported images:
id,created_at,updated_at,properties,model_schema,deleted_at
4115,2021-11-03 12:32:01,2021-11-03 12:32:01,"{""photo"": {""path"": ""sample_item_photo/photo1.jpg"", ""versions"": {""sm"": ""sample_item_photo/photo1.jpg""}, ""extension"": "".jpg"", ""file_name"": ""photo1.jpg""}, ""photo_type"": ""photo"", ""object_uuid"": ""05fb03b9-1b9f-40e6-bc92-182773fb6662""}",photo,
4116,2021-11-03 12:32:01,2021-11-03 12:32:01,"{""photo"": {""path"": ""sample_item_photo/photo2.jpg"", ""versions"": {""sm"": ""sample_item_photo/photo2.jpg""}, ""extension"": "".jpg"", ""file_name"": ""photo2.jpg""}, ""photo_type"": ""photo"", ""object_uuid"": ""05fb03b9-1b9f-40e6-bc92-182773fb6663""}",photo,