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, you will first need to get a kintone App ready with some record data already filled in. You can use whatever App you have in your environment, but in this particular example, we will be using the Customer Database App that is available in the kintone Marketplace.
After adding the Customer Database App to your 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 your App - field codes are listed in the exported data, rather than the field name.
Does your App now have several records inside? Let's go through the steps on how to use cli-kintone to get record data from your kintone App. If you don't have cli-kintone ready at your hands, we suggest preparing it by going through this article.
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:
You will be prompted to enter a password. After entering your password, all of the field data of the records in your App will be displayed in the command line in a CSV format as shown below.
When entering the password, your 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, you can use the shortcut CTRL+C to cancel the current process that's running.
STEP 2: Use an API Token to display data
You can input and output data without entering a password if you use 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 this page on how to generate an API token for your kintone App. You will need to configure the permissions of the API token based on which actions you want to take 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 your 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. We will use API Token authentication 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 you do not specify the directory to output the CSV file to, the file will be saved in the same directory as the executable file of the Command Line Tool (Due to this, you may encounter the "Access denied" error if you are running cli-kintone 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 your directory to see if the files were exported.
Note that if you preform the same action using User authentication like in STEP 1, you will not be prompted to enter a password. A CSV file though is created, with the string "Password" inside. This is because we are using the redirection operator > 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 allows you 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
You can specify the filter conditions and sort order by using the -q option. Refer to this page on 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 you may need to use escape sequences in your query.
The results are as follows:
STEP 6: Export table data
In this last step, we will check how record data containing a table is exported. The format in which records with tables are exported is a little confusing at first.
In the following example, we will be using an 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 your 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, you can 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.
We hope this tutorial has helped you get familiar with exporting 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 your data.