Support > Repository > CSV/Excel input/output > Upload update
ja | en

This function is assumed to be used together with the download function . Edit the file output by downloading and upload it to perform batch update processing.

In the upload update function, the header (item name) is included in the first linePremise.The header is output by the download function.

update

  • You can change the order of the columns.Please change the order including the header part.
  • If you delete a specific column, you can exclude that column from being updated.(The value of the database is maintained as it is.Even if the value of the database is null, it will remain null.)
  • UploadIf there is data whose primary key is included in the data, update.If it does not exist, a new registration.
  • If the data to be uploaded is blank, it is handled in the same way as entering as blank in the new registration screen or update screen.If it is a mandatory item it will result in an error.If it is a non-essential item, it is overwritten with null.
  • When order is applied to the primary key of the target model at the time of new registration, the primary key can be set automatically.Please read the following explanation for details.

sign up

If the primary key of the target data is set to use the order, the order is applied by setting the primary key field of the data to "-1".(Automatic numbering process)

Figure 1 Specify "-1" as the primary key item

Delete

To delete data, add the column "<Status>" to the file to be uploaded. In Figure 2, we specify that data with primary key 1007 should be deleted.

Either upper case letters or lower case letters can be used for identification characters.

Figure 2 Adding the <Status> column
The <Status> column is not automatically granted.Please give it manually if necessary.

In the <Status> column, you can set a character (command) for identifying data to be newly registered, updated, or deleted.

Identification character processing Contents
i sign up Register new data.
u update Update the data.If there is no data corresponding to the primary key, an error occurs.
d Delete It deletes data.
n None It does not process anything.
Empty string The same processing as usual If there is data corresponding to the primary key, update it, otherwise do new registration.

Check the input

In the upload update, the input check (including "warning") specified in the repository is applied.

If a data format error (such as mandatory checking, nonnumeric setting where a numeric value should be entered, etc.) is found, the line number of the error data and an error message are displayed on the screen.Data is not updated (registered).Only error-free data is updated (registered).

This check also applies when "Warning" is specified.That is, the corresponding data is handled as a failure in the upload update processing.

Handling of read-only items

Read only items are upload updatedExclude from targetIt will be.(It can be changed by setting.)

Handling of child model

When a child model is tied to the target model and you want to refer to the value of the child model by an automatic calculation formula or input check,In the upload update, consider "no child model"It will be processed.

This is a measure to affect performance by calling all related child model information from the database every time processing one data.

Handling model reference items

In the download function setting "Also download the primary key of the model reference itemIf you activate it, you can update it with the primary key of the referenced model.

There are some notes on updating by model reference.Details are explained in the troubleshooting section.

If both the id column and the item column are set, the item column (content part) takes precedence.When updating with the value of the id column, delete the item column (manually).

In the case where the reference model of the model reference item is a compound key 7.8

  1. The above "Also download the primary key of the model reference itemEnable ".
  2. Download a file containing both the id column ("item name #id") and the item column.
  3. Delete the item column.It is for updating with the id column.
  4. Set the complex key by concatenating "$ SEP $" with the value of the id column.For example, if the value of pkey 1 is 1000 and the value of pkey 2 is ABC in the model that composes a compound key with the items pkey 1 and pkey 2, write the value of the id column of the uploaded file as "10000 $ SEP $ ABC".
For versions before R7.8, this delimiter is "$".

Filter applied to the content part of model reference item

Upload update (as well as screen input)filterWill be applied. In the content part character string of the model reference item, the reference itemAheadFilter settings are used.

For example, when "customer name" refers to the customer model, the filter set to "customer name" of the customer model is used.

Restriction: Unmatched type

If the referring destination of the reference linked type item is repeated, and it is a check box, it is not supported.(It will not be updated.)

Definition method

You can specify the page title and description.(Figure 3)

Figure 3 Settings related to upload update screen

Example of operation

Figure 4 shows the screen generated by the setting shown in Figure 3.

Figure 4 Example of setting page title and description

I will explain the details of upload update setting.

Figure 5 Settings related to upload update function

Upload file type

The upload file is defined by the following ruleAutomatic judgmentIt will be.

File extension Description
csvProcess as CSV (comma separated).
tsvProcess it as TSV (tab delimited).
Xls or xlsxProcess it as a file in Excel format.
Other than those aboveIt interprets it as the method specified by "upload file type" and processes it.

That is,When it can not be determined by the extensionIt decides either csv or tsv and interprets it.

Data lock type

There are two ways to lock up upload update.

method Description
Whole model While this process is executed, all data of the target model will be locked.
  • This function can not be executed when there are users who are updating data.
  • All users can update and delete target models after this process is completed.
Per record This function can be performed at the same time even when there is a user who is updating data.
  • During upload update,Update/delete data that is not subject to upload updatecan do.
  • When another user tries to update the data being updated with the upload updateLock error occurredTo do.The process of uploading that data will fail.You can check which data failed to update due to error processing result.

In either method, browsing systems such as searching, listing and detailed display, and new registration can be done (even during this processing is in progress).

Character encoding of CSV file 7.10

For each model you can specify the character encoding of the upload file.When this value is blank, "Environment> Application> Initial Parameter> Character Encoding of CSV FileThe value of "is used.

For example, if you want to output with UTF-8, write utf-8 in this column.

Also save reference linkage items

Model "Save reference linked item to own model"If the settings you are making are included, activate this setting and you can enable the self-model saving function even in upload update.

Check existence of primary key column

This function can mix new registration data and update data in the upload file.Therefore, we check the existence of the primary key column.Specifically, it operates as follows.

  • A primary key value is not set, but a value is set in any other column.It is an error.The message "Required item" is displayed on the screen.
  • The primary key column itself does not exist.It is an error.The message "The header line of the CSV file is invalid" is displayed.
  • When all column values ​​are not set (blank line).It is not an error.Skip this line of processing.

If you do not update all upload data with new registration data, you can prevent this primary key existence check.

Please note: If this setting is invalid (main key existence check not done)Please make sure to apply artificial key (sequence) as the primary key item.

Updating "updated user" information of data at upload update

When this setting is valid, "Fixed value - Update userWill be updated (to the user who processed the upload update).Please cancel this setting when you do not update this.

However, if you allow "update read-only items at upload update" and "UPDATE USER" is included in the upload file, update the data in the upload file as "positive" .

Updating "update time" information of data at upload update

When this setting is valid, "Fixed value - Update timeIs updated (at the date and time the upload update was processed).Please cancel this setting when you do not update this.

However, if you allow "update read-only items at upload update" and "update time" is included in the upload file, update the data in the upload file as "positive" .

Update read-only items when updating uploads

When this setting is enabled, it will be updated even if either the registration screen or the update screen is read-only.The choices are as follows.

Setting field Description
(Blank)Follow the setting of the project definition (Standard: "× (not permitted)")
Allow updating
×Do not allow updating
The standard is "x".Only "○" should be added if the read-only item is included in the item to be modified in the upload update.

Handle deletion in script

Include in upload update7.9.2

You can individually specify whether to include each item as uploaded or not.

Controls "to be processed" of "model item detailed definition> input control> upload update".It is blank in the standard.

Figure 6 Specifying the target of upload update processing
Specify processing target Description
(Blank) ※ Standard Use the value of "Output control - Other - Output to CSV - CSV".[To be described later]
It becomes the processing target item of upload update.The above "Output to CSV" value is not taken into consideration.
× It is not an item to be processed for upload update.The above "Output to CSV" value is not taken into consideration.

Setting of CSV output

When the above setting field is "blank", it is controlled by "output to CSV" of "model item detail definition> output control> others".This is enabled by default.

Figure 7 Output setting of item
In other words, "to be processed" is blank in the standard, and "output to CSV" is effective so it is interpreted as upload update subject.In addition, you can specify whether to enable downloading and updating uploads, respectively.

The number of processing corresponds to the number of lines of the read file.This is a number containing header lines and ignored lines.

When updating an Excel file,Rows containing ruled lines are judged as data.If data is not actually included, it is counted as "Ignored Row".That is, lines containing only ruled lines are included in the final processing count.

For specific row only error

If an error is included in a specific line, processing is skipped only for that line.Continue processing to the next line.

In case of a fatal error

If a fatal error (file corruption, double quotation is inconsistent) is encountered, processing stops at that point.At this time, it will be updated before the error line.Processing will be skipped after the error line.

By downloading "download all processing result" and "download error processing result" link, you can download each result as a file.

Figure 8 Process results can be downloaded

Confirm processing result report

You can download the file by clicking the "Download all processing results" link. The file name is "process_ model name", and its contents are text files.Figure 9 shows an example opened using Excel.

Figure 9 Example of processing results report opened in Excel

Uploaded dataAbout all casesYou can check the results with the "Success" "Process" "Message" column.They have the following meanings.

Column name Description
SuccessRepresents success or failure of processing."Success" in the case of success, "Error" in the case of failure is output.
ProcessIt outputs "Insert" for new registration, "Update" for update, "Delete" for deletion, "None" if nothing is done.
MessageError contents are output.If there are multiple errors, they are output with a line feed.

The second line (red frame) in Fig. 9 is "information indicating which row was processed"."○" is displayed in the column to be processed.(※note)

Check error handling report

You can download the file by clicking the "download error processing result" link. The file name is "error_ model name", and the contents are text files.An example of opening using Excel is shown in Fig. Since there are no errors here, the second and subsequent lines are empty lines.

Figure 10 Example of opening error report in Excel (no error)

If there is an error, "Error" is set in the "Success" column.Error contents are output to "Message".

Items for which the narrowing down (check box) is set will not take into account the value of the narrowing down item.Judge by looking at all the model reference data.
When conditional authority is specified, the condition part is not used for judgment.Consider only the authority part.

It was not acquired by the download function of Wagby,Use original CSV file for upload updatecan do.Please apply the following rule.

By using the "save in CSV format" function provided by Excel, you can prepare a CSV file based on the above-mentioned rule (correctly considering the quotation mark "").
Depending on the settings of the repository, the item name of the header line and the column name of the database may not be the same.
Therefore, if you prepare your own upload update file, execute the download function once and become upload update targetConfirm whether the item name completely matchesPlease do.

You can compress multiple csv (or tsv) format files into one zip file and upload it.The following rules apply.

  • Same modelCan be divided into multiple CSV files and registered.
  • Processed in alphabetical order by file name.
  • If there is no file that can be processed in the zip file, display an error message and exit.
  • If there is an error, describe the name of the file to be processed at the beginning of the error message.This makes it possible to know which file caused an error.
Excel files can not be included in the zip file.(Excel files are ignored.)

It is also possible to mix csv format and tsv format file in zip file.It determines by file extension.

Upload using Excel file When updating, you can specify the numerical format to be used for numerical item analysis. If not specified, the fixed format "

Setting method

Specify "Model control" input control> upload update> numeric format (Excel) ".In the following example, it is set to enter up to 17 decimal places.

##0.#################
Figure 11 Specifying numeric format (Excel)
This setting does not affect upload updates using CSV files.(In the upload update using the CSV file, the input value is used as it is.)

Certain data can not be processed

Please confirm the following points.

  • Does the data contain invalid characters (special characters, external characters, etc.)?
  • Does the data violate the "unique constraint check"?
  • File downloaded in CSV formatHave you opened it in Excel and then saved it? In the specification of Excel, "0" will be deleted for data including "0" in the front.For example, if the actual data is "000123", open this CSV file in Excel and save it "123".If this value is the primary key, there is a problem that the corresponding data can not be found.

Certain columns can not be processed

If only certain columns are not processed correctly, please check whether "Column name" in the first line of the uploaded file and "Column name" in the design information are correctly matched.If the column names do not exactly match, the column will not be processed.

[Case study] New registration is not handled correctly

I uploaded the following file for the model using the order as the primary key.The primary key is assumed to be started from 1000 and no data has been registered at the time of processing.

Pkey, name 1000, aaa 1002, bbb 1001, ccc

At this time, the operation will be as follows.

  1. Since no data is registered, the first line is newly registered.Here, the value "1000" is not used and it is automatically numbered from the order.By chance, the same "1000" will be issued, registration on the first line will succeed.
  2. The second line will also be newly registered.Here, the value "1002" is not used, "1001" is called from the order and registered.The contents of the database at this point are as follows.
    1000, aaa 1001, bbb
  3. Since the data "1001" has already been registered in the third line,Update treatment.Therefore, the contents of the database are rewritten as follows.
    1000, aaa 1001, ccc

Countermeasure (1): In order to prevent such malfunctions, it is advisable to set the primary key part to "-1" for registration to the model using order.

Countermeasure (2): The data to be uploaded should be sorted in advance with the primary key.(For example, when acquiring data with the download function, you can avoid it beforehand by specifying rules that use data sorted by primary key.)

[Case Study] In the update of the model reference item, the name of the reference destination was different

For example, although data referring to the "company" model is described as "Yamada Shoten" in the company name, if this data does not exist in the actual company model, this item itself is not updated Hmm.

[Case] ​​An attempt was made to update invalid data by updating the model reference item

"Option model"Invalidate choicesSuppose you have invalidated (logical deletion) of certain data in the setting.Here, when updating to invalid data, an update error occurs.

[Case] ​​Model reference (check box) Required input check specified for type field

If uploading is done without checking all, it will result in an error.If you check one or more and upload it, it will not result in an error.

[Example] The same content (data) was set for different codes

If such data exists, it will be ignored or treated as error.For example, suppose you set the following data in the "gender" model.

code value
1Man
2woman
3Man Duplicate content

At this time, updating the upload from "Woman" to "Men" will result in an error.It is because I can not tell which code 1 or 3 to use.

The following error message is displayed on the screen.

A problem occurred with the value YYY specified for field XXX.Multiple values ​​are defined in the same code.Therefore, we can not identify which code to update.

[Example] When uploading data, unrelated models are updated at the same time

If the upload target is a "child model", the parent model may also be updated if "screen> others> relevance of model> update parent model when changing" is valid.

In addition, if the parent model has relationships with other models (for example, there are other child models and referring to each other), the relevant models may also be updated as necessary.