Data Transfer Overview

From the ARCHIBUS Smart Client or from Web Central, you can transfer ARCHIBUS data in or out of your databases using comma-separated values (CSV) format, or XLS - Excel XML format. When created from ARCHIBUS, each of these file formats contains portions of the ARCHIBUS schema information, so that you can quickly generate, transfer, and read in data changes, even when working from remote sites. ARCHIBUS applies the same data integrity rules to imported data, so that your project database remains consistent. Each extract file can include one table.

Data transfer is used by the Move Management, Condition Assessment, and the Localization applications. You can invoke data transfer from Data action buttons if your system integrator customizes views to include this action. You can see a sample view at Technologies/System Integrator Views/Parts for Reports/Report with DOC, PDF, Excel, and Data Transfer actions.

From Web Central, to help you track changes and fix errors, the Data Transfer feature includes Comparison reports that compare the data in the database to the data in the import file by generating insert, update, and error logs. You can generate Comparison reports before importing data; since no other changes are made to the database, these Comparison reports are useful for reviewing changes before you apply them to your data set. See Comparison Reports.

For certain tables, you can also track the files that are written using the Data Transfer Status field. See The Data Transfer Status Field.

You transfer data and generate Comparison reports using the Data Transfer Wizard. See Data Transfer Wizard.

Note: If you have exported your data to CSV format and then worked with the CSV file in Excel, you might encounter issues when importing the files back into ARCHIBUS. See Working with CSV Files in Excel.

Note: If you are transferring a large amount of data, consider turning your system for performance. Increasing memory, in particular, can have a significant effect on the rate at which Data Transfer processes your data.

Uses

Data Transfer is used in the following applications:

ARCHIBUS Web Central's Condition Assessments application uses data transfer to transfer data from a hand held device to your ARCHIBUS project database.
The Localization application uses data transfer to read and write data from translatable tables and files to the localization tables.
From the Move management application, you can invoke data transfer to transfer data to XLS format so that you can edit the data in Excel. You can then transfer in the edited data to update the move project.
Note: Data Transfer from Move Management does not include comparison reports or export to CSV format.

Some of the typical uses of the Data transfer feature invoked from Data action buttons include the following:

Import into ARCHIBUS data that administrative staff developed offline in Excel.
Integrate changes made by parallel teams or remote personnel into the common project database.
Transfer data from regional, specific-purpose, or prototype databases to your production database.
Move Process Navigator changes, work flow rules, or background data, such as cost categories, between projects.
Send updated list of standards, such as room standards, account codes, division and department lists, to remote offices in a form they can apply to their ARCHIBUS.
Include extracts of your data in Excel analyses.

Data Transfer Process

From Web Central, data transfer is invoked from Data button, included on some views.

From the Smart Client, data transfer is invoked by selecting the View/Transfer command from the Ribbon.

You can transfer data from a local file that you have updated, or you can transfer files from a server without loading the files to your local drive. The following describes the data transfer process when you edit a file locally:

Load an ARCHIBUS Web view, restrict it to the data you want to work with, and select the Data button to transfer out the data to CSV or XLS format. The file is exported to the server. See Exporting Data Using Data Transfer.
Save the file to you local drive, and open it in Excel or other spreadsheet program to enter changes and add new records.
Generate and review Comparison reports to compare the modified export file to the database data, and to review any errors. See Generating Comparison Reports.
After reviewing the Comparison reports, write changes made in the extract file back into the database. You can transfer in an exported file after you have edited it, or you can transfer in a log file (Comparison report) that you have reviewed or updated. See Importing Data Using Data Transfer.

Supported Formats

ARCHIBUS supports the following formats for data transfer:

Excel (XLS) - Excel XML Format: The first two rows of the XLS file are commented lines. The first row contains the table name, for example, # Data Transfer Results for Table 'rm'. The second row contains the schema header information, for example, rm.bl_id, rm.fl_id, rm. rm_id.

Comma-separated values (CSV) format: CSV is a popular format for exchanging spreadsheets.

CSV uses the following rules:

Fields are separated by commas.
Fields of data that include commas are surrounded by double quotes.
Quoted fields that include embedded quotes use a double quote ("") to mark the embedded quote character.
Commas are not used to separate thousands, so that numbers can be easily reread by the Transfer In action.

ARCHIBUS adds the following rules when generating CSV files:

The first two lines begin with a pound sign (#) and are comments.
- The first line of the file is a comment holding the schema header information. This is a list of fields in table.field format, for example, rm.bl_id, rm.fl_id, rm. rm_id.
- The second comment line has the total number of records, for example, "32427 records written".

See Preparing the Import File.

Data Transfer Permissions

To use the Transfer In action, you must belong to a security group that has the right to bulk edit data (userGroupAllowedEditInBulk). By default, this is specified by membership in the PROCESS OWNER security group.

You can edit these permissions in the preferences-per-site.xml file found in security.properties under WEB-INF\config.

The following excerpt from the preferences-persite.xml file shows the editInBulk assignment to the Process Owner role.

<util:map id="securityGroups">

<entry key="editCanonicViews" value="SYS-USRMGR"/>

<entry key="editAnalysisViews" value="SYS-USRMGR"/>

<entry key="editInBulk" value="PROCESS OWNER"/>

<entry key="alterSchema" value="SYS"/>

</util:map>

The Transfer Out action does not require membership in a security group.

The Data Transfer Status Field

If there is a Data Transfer Status field in the table that will hold the import data, the program updates the value of that field with the status of each record. The Data Transfer Status field provides the final data transfer status for each record for the import. You can filter on this Data Transfer Status field to see the records that were newly added, updated, or missing in the transfer.

In V.19.1-and-later databases, the Rooms table and all of the “afm_” system tables have a Data Transfer Status field.

Note: If you generate Comparison reports without importing data (Compare action from the Data Transfer Wizard) , the Data Transfer Status field is not updated, as this field is only updated when an import is processed.

The following describes the possible values for the Data Transfer Status values:

Inserted: The last transfer in added this record. That is, the record was in the import file, but not in the database, and so the record was inserted into the database.
Updated: The last transfer in changed the record. That is, the record was in the import file and in the database, but there were different field values in the import file than in the database. For this reason, the record was updated in the database.
No Change: The last transfer in did not affect any of this record's values. That is, the record was in both the import file and the database, but none of the fields of data included in the import file differed from those same fields in the database.
Missing: The last transfer in found this record in the database, but not in the import file. If you are importing a subset of all records, such as all employees for your department, your import will be missing employees from other departments, although the database has those records. In this case, missing records are expected. If missing records represent records that are no longer relevant, you can manually delete them from the database. That is, if the import file represents the complete list of records for this table, the missing records should be deleted.
Pending: The user canceled the last transfer before the action considered this record.
Error: This record was in the import file, but there was an error, such as a validation error, when updating the database. See Fixing Errors.

About File Deletion from the Server

Files, such as paginated reports, data transfer files, and data transfer log files (Comparison reports) remain on the server until they are deleted by the PerUserFileExpirations application parameter edited via the System Administration / ARCHIBUS System Administration / Business Process Owner / Edit Application Parameters view. The application parameter is:

AbSystemAdministration>perUserFileExpirations.

By default, files are deleted every 6 hours. Files are available in later Web Central sessions only if Web Central is not running on a clustered environment.

Saving Export Files

If you want to work further with the export file, you can save it to your local workstation. When you click the link to open the file, you are given the option to save the file. You can also save an export file by right-clicking the file's link in the Result View or File section, and select Save Target As to load the file to your local workstation.