Preparing a File for Registering/Updating Records
This article provides instructions on how to prepare a file suitable for registering or updating records via file import.
If you have already prepared a file for import, refer to the information in this article and check that your file is suitable for file import.
If you have not yet prepared a file for import, it is recommended that you first export record data from an app to a file and use the exported file as a base for editing data. Referencing the information this article provides, finish up editing data in the exported file.
The following file formats (file extensions) are supported for file import.
- Excel format (xlsx), CSV format (csv), TXT format (txt), TSV format (tsv)
Files in Excel format (xlsx) can be created with Microsoft Excel (hereinafter called "Excel").
If an Excel file contains multiple worksheets, data on the first worksheet is imported. Data in hidden cells and merged cells is also imported. Unhide or unmerge these cells and check beforehand that the content of the file is correctly specified.
A file in CSV format (csv),TXT format (txt), or TSV format (tsv) can be created with spreadsheet software (e.g. Excel and Open Office Calc), text editors, and so on.
The upper limit on the file size and total number of rows or columns
- Excel format (xlsx)
Up to 1 MB, 1,000 rows
- CSV format (csv), TXT format (txt), TSV format (tsv)
Up to 100 MB, 100,000 rows
Irrespective of the file format, only the first 1,000 columns in the file can be associated with the fields in the destination app.
If the size of your file for import or the total number of rows/columns in the file exceeds the limit, split the file into smaller files or change the file format (if the file is in Excel format).
For the upper limit on the total number of records or fields that can be registered in an app, refer to List of Limits.
How to format data of each record
In your file for import, specify data of each record in the format suited for file import.
There are three points for formatting data of each record in a file for import.
- Point 1: In the first row of the file, specify item names.
If you use field names in the destination app for item names in the file, file columns are automatically associated with the corresponding fields in the app when you import the file.
- Point 2: Specify data of each item in the second and subsequent rows.
Specify data of each item in the format supported by the corresponding field. For details on how to format data for each field type, refer to Supported Data Formats for Each Field Type.
- Point 3: Specify data of one record per row.
However, if records contain tables, use one or multiple rows to specify data of each record depending on the number of table rows. For details on how to format data of records that contain tables, refer to Preparing a File for Registering/Updating Records with Tables.
How to format data for each field type
In your file for import, specify data of each field in the format suited for file import.
If you import a file that contains empty values, either empty values or default values (if default values are set for the fields) are imported into the corresponding fields in the destination file.
Details on the formatting for each field type are explained in Supported Formats for Registering/Updating Records.
In this section, you will find information about the fields that require special formatting when you specify data in your file for import.
"User selection," "Department selection," and "Group selection" fields
Use login names, department codes, and group codes respectively to specify data of these fields.
Note that, if you specify data of these fields using their display names, empty values or default values (if default values are set for the fields) are imported into the corresponding fields.
If you want to register multiple values for a field, enter all the values in a single cell with line breaks between each value.
"Radio button" and "Drop-down" fields
Specify an option from the options selectable for the field. If you specify a value that is not included in the options of the field, an error occurs when you import the file.
"Check box" and "Multi-choice" fields
Use different columns for each option. Specify a name for each column in such a way that you can tell which option of which field is represented by each column. (E.g. "Field name [Option name]")
Specify '1' in the column for the option to be selected, and do not specify any values in the column for the option that is not to be selected.
"Record number" field
If you are to specify the "Record number" field as Key to Bulk Update when updating record data via file import, specify the record number of the record whose data you want to update in your file for import. If you specify a value that does not match any of the record numbers in the app, an error occurs when you import the file.
You cannot assign a record number of your choice to records when you register records via file import. Also, you cannot change the record number of existing records when you update record data via file import.
Fields for which the Required field option is enabled
If there are any empty cells in the columns for these fields, an error occurs. Check that your file for import does not have any empty cells in the columns for these fields.
Fields for which the Prohibit duplicate values option is enabled
Check the values in the columns for these fields, and make sure that there are no duplicate values within each column and there are no values that overlap with a value in existing records.
Note that, if the Prohibit duplicate values option is enabled for a "Text" or "Link" field, up to 64 full-width or half-width characters can be used for specifying the data to be imported into the field. (If a limit to the number of characters is set in the field setting, the setting takes precedence.)
Check the data specified in your file for import and make sure that the number of characters used for the data does not exceed the limit.
Reference: Basic operations to create and edit a file in Excel
This section explains the basic steps to create and edit a file in Excel, from opening a file in Excel to saving it.
Note that the menu option names and operations may vary depending on your Excel version.
Creating a file from scratch
- Open Excel and specify record data on the first worksheet.
- Select the File tab and click Save As.
- Specify the save location and file name.
- In the Save as type list, select Excel Workbook or CSV (Comma delimited).
- Click Save.
Editing an exported file
- Export record data to a CSV file and then download the file.
Exporting Data to a File
- Open the downloaded CSV file in Excel and edit the file.
If you want to register new records, add rows in the file and specify data in them.
- Select the File tab and click Save.