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.

Detailed 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

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.

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

Download a file containing both the id column ("item name #id") and the item column.

Delete the item column.It is for updating with the id column.

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.)

Set upload update screen

Definition method

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

Figure 3 Settings related to upload update screen

An example

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

Figure 4 Example of setting page title and description

Setting upload update function

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

csv

Process as CSV (comma separated).

tsv

Process it as TSV (tab delimited).

Xls or xlsx

Process it as a file in Excel format.

Other than those above

It 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).

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.

Handling of processing count

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.

Action on error

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.

Confirm update result report

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

Success

Represents success or failure of processing."Success" in the case of success, "Error" in the case of failure is output.

Process

It outputs "Insert" for new registration, "Update" for update, "Delete" for deletion, "None" if nothing is done.

Message

Error 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.

Upload your own prepared files

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

The first item (the first line) describes the Japanese item name.Item name isDownload functionIn the form to get inThe same thingwill do.If different item names are used, it will not be updated properly.

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.

Compress and send multiple files

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.

Specify numerical format when using Excel R7.10

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 "

Definition 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.)

Trouble shooting

Certain data can not be processed

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.

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.

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

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.