Supported Formats for Adding/Updating Records
This article describes how data should be formatted in your file for import when you bulk add or update records in an existing app by importing an Excel or a CSV file into it.
For your reference, this article provides a sample file that shows the appropriate formatting for each field type. To check the appropriate formatting for each field type, refer to the following and download the file.
Sample file with appropriate formatting for each field type
Formatting for field names
It is recommended that you use field names for the column names (the first row) in your file for import. The file can be imported even if you do not specify field names in it; however, in that case, you will be required to manually map the correspondence between columns in the file and fields in the app when you import the file. Specifying field names in the first row of your file is beneficial since, by doing so, kintone will automatically map the correspondence between columns and fields.
While you can directly enter field names in the first row of your file, it is recommended that you first export app data using the Export to File menu option on the app's "View" screen to export field names to a file. The Export to File menu option allows you to export field names to a file no matter whether there are any records in the app. This is less troublesome than directly entering field names in your file, and you can also prevent input errors. Hence, it is recommended that you first export app data to a file and use the exported file as a base for editing data.
Exporting Data to a File
Formatting for record data
In your file for import, each record should be stored in a single row.
However, if records contain a table, use as many rows as there are in the table to enter data of each record. For details, refer to the following page:
Preparing a File for Adding/Updating Records with Tables
Formatting for each field type
Each field type has specific formatting requirements that must be met in order for data in a file to be imported into fields in an app. Referencing the information provided below, specify data in your file in accordance with the format required by each field type.
Text / Text area
- Enter the text you want to import into the field.
- If you want to import multiline text into a "Text area" field, enter multiple lines of text in your file using line breaks.
Number
Enter numerical values in any of the following formats:
- Integer
- Decimal
- Negative number
Example: "-1.23" - Exponential number
Example: "1.20E+03" in the file is registered as "1200" in the app.
Rich text
Enter data for the field in HTML format.
Check box / Multi-choice
- Use separate columns for each option and enter "Field name [Option name]" in the first row of each column.
Example: If the app has a "Traveled By" field with the options "Train" and "Bus", "Traveled By [Train]" and "Traveled By [Bus]" should be entered in the first row of the corresponding columns. - Enter '1' in the cell for the option to be selected. The cell for the option that is not to be selected should be left empty.
Radio button / Drop-down
Use one column per field, and enter the name of the option to be selected.
Date
- If your file for import is an Excel file, data for this field type can be entered in any date format that is recognized as a date in Excel.
- If your file for import is a CSV file, enter data for this field type in any of the following formats:
- YYYY-MM-DD (Example: 2022-4-1)
- YYYY/MM/DD (Example: 2022/4/1)
- YYYYMMDD (Example: 20220401)
- YY/MM/DD (Example: 22/4/1)
- MM/DD/YYYY (Example: 4/1/2022)
- MM/DD/YY (Example: 4/1/22)
- DD/MM/YYYY (Example: 1/4/2022)
- DD/MM/YY (Example: 1/4/22)
- Single-digit months and days can be entered in either the single- or double-digit format. For example, you can enter either "4" or "04" to indicate April.
However, when entering values in the "YYYYMMDD" format in CSV files, use the double-digit format. For example, enter "04" to indicate April. - You can enter dates between 00:00 (UTC) January 1, 1000 and 23:59 (UTC) December 31, 9999.
- If the day part is omitted, it is assumed that 1 is specified as the day.
Example: "2022/01" is converted into "2022/01/01" when the file is imported into the app. - If the month and day are omitted, it is assumed that January 1 is specified as the month and day.
Example: "2022" is converted into "2022/01/01" when the file is imported into the app.
Time
- If your file for import is an Excel file, data for this field type can be entered in any time format that is recognized as time in Excel.
- If your file for import is a CSV file, enter data for this field type in any of the following formats:
- HH:MM AM/PM
Example: "11:02 PM" - HH:MM
Example: "23:02"
- HH:MM AM/PM
- Single-digit hours and minutes can be entered in either the single- or double-digit format. For example, you can enter either "8" or "08" to indicate eight o'clock.
- The seconds part will not be imported even if it is included in the data.
Example: "11:01:25" is converted into "11:01". - If the minutes part is omitted, it is assumed that 0 is specified as the minutes.
Example: "11" is converted into "11:00".
Date and time
- If your file for import is an Excel file, data for this field type can be entered in any date and time format that is recognized as date and time in Excel.
- If your file for import is a CSV file, enter a date followed by a half-width space and then time.
Example: "2022/01/11 11:01" - The seconds part will not be imported, even if it is included in the data.
- If the time part is omitted, it will be assumed that 00:00 is specified as the time.
Example: "2022/01/11" is converted into "2022/01/11 00:00" when the file is imported into the app. - If date and time in your CSV/TXT/TSV file contains time zones, time zones you select on the "Import from File" screen will be ignored.
- If your file for import is a CSV file, enter data for this field type in any of the following formats:
- YYYY-MM-DD HH24:NN
Example: "2022-12-20 23:20" - YYYY-MM-DDTHH24:NN
Example: "2022-12-20T23:20" - YYYY-MM-DDTHH24:NNZ
Example: "2022-12-20T23:20Z" - YYYYMMDDHH24NN
Example: "202212202320" - YYYY/MM/DD HH12:NN AM/PM
Example: "2022/12/20 11:20 PM" - YYYY/MM/DD HH24:NN
Example: "2022/12/20 23:20" - MM/DD/YYYY HH12:NN AM/PM
Example: "12/20/2022 11:20 PM" - MM/DD/YYYY HH24:NN
Example: "12/20/2022 23:20" - DD/MM/YYYY HH12:NN AM/PM
Example: "20/12/2022 11:20 PM" - DD/MM/YYYY HH24:NN
Example: "20/12/2022 23:20"
- YYYY-MM-DD HH24:NN
Link (URL)
- Enter a URL that begins with "http://" or "https://".
- The URL can contain the following characters:
- Alphabetic characters (uppercase and lowercase)
- Arabic numerals
- The following special characters:
-_.!~*'():@&=+$,;?/#%
Some special characters may not be usable for certain parts of the URL.
Link (Telephone number)
- Enter a telephone number.
- Telephone numbers can be entered with or without hyphens.
Link (E-mail address)
- Enter an E-mail address in the following format: "local-part@domain".
- The local part can contain the following characters:
- Alphabetic characters (uppercase and lowercase)
- Arabic numerals
- The following special characters:
.!#$'%&*+/=?^_-`{}|~
- The domain can contain the following characters:
- Alphabetic characters (uppercase and lowercase)
- Arabic numerals
- The following special characters:
.-
User selection
- Enter the login name of the user to be selected.
- If you want multiple users to be selected in the field, enter their login names with line breaks between each login name.
- If you are editing your file for import using Excel or other spreadsheet software, enter the users' login names in a single cell and separate them using line breaks.
- If you are editing your CSV file for import using a text editor, enter the users' login names as a single value (field) in the file and separate them using line breaks.
- If you want to register a guest user, enter "guest/" at the beginning of the guest user's e-mail address (login name).
Department selection
-
Enter the department code of the department to be selected.
-
If you want multiple departments to be selected in the field, enter their department codes with line breaks between each department code.
- If you are editing your file using Excel or other spreadsheet software, enter the department codes in a single cell and separate them using line breaks.
- If you are editing your CSV file for import using a text editor, enter the department codes as a single value (field) in the file and separate them using line breaks.
Group selection
-
Enter the group code of the group to be selected.
-
If you want multiple groups to be selected in the field, enter their group codes with line breaks between each group code.
- If you are editing your file using Excel or other spreadsheet software, enter the group codes in a single cell and separate them using line breaks.
- If you are editing your CSV file for import using a text editor, enter the group codes as a single value (field) in the file and separate them using line breaks.
Lookup
- Enter values of the field that is set as Key Field in the "Lookup" field setting in the app.
Example: If you want to import values into a field called "Employee Name" that has a field from a different app called "Name" specified as the Key Field in its settings, enter values from the "Name" field in your file for import. - When your file is imported, values for the fields specified under Field Mappings in the "Lookup" field setting are retrieved again. Therefore, you do not need to enter the values in the file.
Record number
- If you plan to set record numbers as Key to Bulk Update, enter the record number of records whose data you want to update.
- Do not enter a record number in the row for records to be newly added.
- Even if an app code is set for the app, enter only the record number; do not enter the app code together with the number.
Setting an App Code
Category
- Enter the name of the category to be selected.
- If you want multiple categories to be selected, enter the category names with line breaks between each category name.
- If you are editing your file using Excel or other spreadsheet software, enter the category names in a single cell and separate them using line breaks.
- If you are editing your CSV file for import using a text editor, enter the category names as a single value (field) in the file and separate them using line breaks.
Table
- Use separate columns for each field inside the table.
- Use as many rows as there are in the table to enter data for fields inside the table.
- Enter data in the first column of your file in the following manner:
- In the first row, enter "New Record Flag".
- Enter an asterisk (*) in the row where each record's data begins, and leave the other rows blank. The data from a row with an asterisk (*) to the row above the next row with an asterisk (*) is recognized as the data of a single record.
- In the scenario where you update existing record data, if you want to add rows to an existing table in a record, insert rows in the file and enter in these rows the data to be registered in the fields of new table rows.
If the inserted rows are not the first row of the record, leave the first column (the "New Record Flag" column) blank. - When you update table data in existing records, your file for import must contain data of all the fields in the table including the field data that is not to be updated. As an exception, your file for import does not need to contain field data that will be re-calculated or retrieved again.
Created by / Updated by
-
Enter one user login name.
-
Data in a file can be imported into "Created by" and "Updated by" fields only when new records are created. Even if your file for import contains values for "Created by" and "Updated by" fields of existing records, the field values will not be updated.
-
You cannot enter the following:
- A value that is not a login name
- Multiple users
- A user who does not exist
- An inactive or a deleted user
- A user who is not using kintone
- A user in the department for which you do not have view permission
- (If the destination app of the file import belongs to a guest space) A guest user who is not the member of the guest space
Created datetime / Updated datetime
- If your file for import is an Excel file, data for this field type can be entered in any date and time format that is recognized as date and time in Excel.
- If your file for import is a CSV file, enter a date followed by a half-width space and then time.
Example: "2022/1/12 11:26" - Data in a file can be imported into "Created datetime" and "Updated datetime" fields only when new records are created. Even if your file for import contains values for "Created datetime" and "Updated datetime" fields of existing records, the field values will not be updated.
- Enter the date and time that is before the date and time at which your file is imported.
- The seconds part will not be imported, even if it is included in the data.
- If the time part is omitted, it will be assumed that 00:00 is specified as the time.
Example: "2014/01/11" is converted into "2014/01/11 00:00" when the file is imported into the app. - If date and time in your CSV/TXT/TSV file contains time zones, time zones you select on the "Import from File" screen will be ignored.
- If your file for import is a CSV file, enter data for this field type in any of the following formats:
- YYYY-MM-DD HH24:NN
Example: "2022-12-20 23:20" - YYYY-MM-DDTHH24:NN
Example: "2022-12-20T23:20" - YYYY-MM-DDTHH24:NNZ
Example: "2022-12-20T23:20Z" - YYYYMMDDHH24NN
Example: "202212202320" - YYYY/MM/DD HH12:NN AM/PM
Example: "2022/12/20 11:20 PM" - YYYY/MM/DD HH24:NN
Example: "2022/12/20 23:20" - MM/DD/YYYY HH12:NN AM/PM
Example: "12/20/2022 11:20 PM" - MM/DD/YYYY HH24:NN
Example: "12/20/2022 23:20" - DD/MM/YYYY HH12:NN AM/PM
Example: "20/12/2022 11:20 PM" - DD/MM/YYYY HH24:NN
Example: "20/12/2022 23:20"
- YYYY-MM-DD HH24:NN
If an error appears and you cannot import your file
Of various possible causes, this section describes some of the common ones.
- Cause: Although a field has the Required field check box selected in its field setting, values for the field are not entered in your file for import.
Solution: If the Required field check box is selected for some fields in the app, make sure to enter values for these fields in your file for import. - Cause: A value other than a numerical value (e.g., "10,000,000 yen") is entered as a value to be imported into a "Number" field.
Solution: Enter only numerical values as the values to be imported into "Number" fields. The fields do not accept commas and units. - Cause: A value other than an option set for a "Drop-down" or "Radio button" field is entered as a value to be imported into the field.
Solution: When entering values to be imported into a "Drop-down" or "Radio button" field, enter only the options set for the field in the app. - Cause: A value other than a login name is entered as a value to be imported into a "Created by" field.
Solution: When entering values to be imported into a "Created by" field, enter only user login names. You can find user login names in the following locations:- Departments & Users in Users & System Administration (Accessible only by Users & System Administrators)
- In the URL of individual users' People page or profile details
In addition to the above, there are other possible causes of the error. For details, refer to What to Do If Adding/Updating Records in Bulk Failed.
Sample file with appropriate formatting for each field type
For your reference, this article provides a sample file that shows the appropriate formatting for each field type. To check the appropriate formatting for each field type, download the following file with samples for all of the fields (Only available in Japanese).
sample_all_fields_app.csv