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,[email protected],abcdefghijaklmnoprst111,2020-03-11 05:44:13.302432,2020-03-11 05:44:13.302432,[email protected],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,