This article introduces how to use the Kintone Command Line Tool (cli-kintone) to get record data from a Kintone App. This tutorial series will be followed later with articles on how to add, delete and update data, and on how to upload/download attachment files with cli-kintone.
For this tutorial, a Kintone App with record data is needed. Feel free to use any created App. This tutorial, will use the Customer Database App that is available in the Kintone Marketplace.
After adding the Customer Database App to the Kintone environment, add some record data to the App so that there's data for cli-kintone to work with. Also set some simple field codes for the fields in the App - field codes are listed in the exported data, rather than the field name.
Next, use cli-kintone to get record data from the Kintone App. If cli-kintone is not already set up, go through Preparing the Command Line Tool to set up the tool first.
The cli-kintone tutorials introduced here will be demoed on a Windows environment.
STEP 1: Use Username and Password to display data
In the following example, the domain name sample.kintone.com, App ID 777, and login name Kelvin are used:
The user will be prompted to enter a password. After entering the password, all of the field data of the records in the App will be displayed in the command line in a CSV format as shown below.
When entering the password, the input will not be displayed on the command line for security reasons.
Notice that the field code, and not the field name, will be displayed on the first line of the CSV.
Specify -o json at the end of the command to display the result in JSON format instead of the CSV format.
JSON format data may be easier to use when using Kintone data with other services.
If at any time cli-kintone does not finish it's process, the shortcut CTRL+C can be used to cancel the current process that's running.
STEP 2: Use an API Token to display data
It is possible to input and output data without entering a password by using an API token. Using an API token has the following benefits:
- The authentication is unaffected by a password change of a user (unlike password authentication)
- The scope of the API token is limited to one App
- The scope of the actions the API token can take can be easily controlled
Refer to Using API Token authentication for information on how to generate an API token for the current Kintone App. You will need to configure the permissions of the API token based on which actions will be taken with cli-kintone.
All actions made with API token authentication will be logged as actions run by the user name Administrator.
Use the API token in the command with the -t option:
STEP 3: Export to a CSV File in UTF-16
In this step, data will be exported to a file instead of being displayed on the command line. API Token authentication will be used for this step.
Kintone outputs string data in UTF-8 by default. To output data in other character codes, use the -e option.
In this example, data will be exported to the file sample.csv in UTF-16 format.
If the directory to output the CSV file to is not specified, the file will be saved in the same directory as the executable file of the Command Line Tool (Due to this, an "Access denied" error may occur if cli-kintone is running right under the C drive due to system administrator permissions. In this case, make sure to specify the path of the directory to output the file to).
To save the file in the directory where the executable file is located:
To save the file by specifying a directory (saving on the desktop in this example):
Check the directory to see if the files were exported.
Note that if the same action is performed using User authentication like in Step 1, the password prompt will not come up. A CSV file is created though, with the string "Password" inside. This is because the redirection operator > is being used, that writes the command output to a file instead of the command line. Therefore, the string "Password" is exported into the file.
To export data into a file with User authentication, use the -p option together with the -u option to specify the password.
STEP 4: Specify the fields to export
The –c option makes it possible to specify the fields to export. In this example, $id (the record number), company name, and contact name are exported.
Make sure to specify the field codes, and not the field names.
The results are as follows:
STEP 5: Specify filter conditions and sort order
The filter conditions and sort order can be specified by using the -q option. Refer to Get Record for how to write conditional expressions.
In this example, records with department name "Marketing" are exported in the ascending order of record numbers along with the $id (record id), company name, contact name and department.
Remember that escape sequences may need to be used in the query.
The results are as follows:
STEP 6: Export table data
In this last step, check how record data containing a table is exported. The format in which records with tables are exported is a little confusing at first.
The following example uses the Expense Report App.
Add 3 records to the Expense Report App, and make sure to add a few lines to the Expense Details table of each record.
Use the below command in the command line to export the table data to a CSV file:
Below is how the exported result in the CSV file would look like:
Try opening the CSV file in Excel for a better look:
The first row of the exported CSV has an * symbol in the first column followed by field code names. This first shows, that table data exist in this exported CSV.
Row 2, 3 and 6 include an * symbol in the first column. This indicates that these rows, and rows below it that don't contain an * symbol, are data of 1 record of the App.
The absence of the * symbol in some of the rows after the first indicates that they represent data of the second or later row inside the table of the record.
In the above CSV data, see that three records were exported in total.
Records with record ID of 1 and 2 include several lines of table data - record ID 1 has two lines of table data, and record ID 2 has three lines of table data. Record ID 3 also includes table data - there's just only one line of table data inside.
Although the rows with the same record ID seem to have similar data inside, notice that the fields inside tables contain different values.
The following image shows what the table data looks like in each record of the App.
This tutorial explained how to export record data from a Kintone App using cli-kintone. Try using the different options available on cli-kintone to see the various ways to export data.