Importing Data Using Data Transfer

Importing data (the Transfer In action) imports a comma-separated values (CSV) or an Excel (XLS) file from the client computer or from a server file. When importing data, you can select to generate logs (Comparison reports) of inserted and updated records. If there are any errors, error logs are always generated.

When transferring data in you would typically:

Generate the logs that compare your import file to the data in the database. See Generating Comparison Reports.
Review the logs to preview the changes.
If there are errors, you can open the error log in Excel, fix the errors and then re-import the error log file with the corrections.
Import the data by selecting the Transfer In action. See How To Import Data.

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

Note: If you use Data Transfer to export your data to CSV format and then work with the CSV file in Excel, you might encounter issues when importing the files back into ARCHIBUS. See Working with CSV Files in Excel.

Importing Markup

If you are exporting and importing markup into and out of the project, be aware of the following:

In order to access the markup when you import it back into the project, both the activity_log record and the afm_redline record must be exported. If only the activity log record is exported , when the record is imported back in, the Edit Markup button will not show in the view; the view will show only a button for Create Markup, and this button will not show the markup.

Views from which data transfer is available

Data Transfer is available from both the Smart Client and from Web Central. For information on the specific views from which data transfer is available, see Views from which data transfer is available.

Local versus Server Files

When transferring data in, you can select a file on your client computer to upload (by selecting Local File), or you can upload the last file output for this table by this user on the server (by selecting Server File). Use local files when it is more convenient to work with files on your local machine without needing to move the file back to the server to import it. Use server files if you are transferring data and do not need to work with the data locally before importing it. For example, if you are transferring building, floor, and room data between two projects that are running on your server, you can log into one project, transfer out the data, sign into the second project, and transfer the data in without downloading the data to your local workstation.

Preparing the Import File

You might use data transfer to transfer data from one project database to another. In these cases, you are importing the file that was exported from ARCHIBUS, so the file has the proper schema information, enumeration values, and date formats. In other cases, you are reading information from a spreadsheet file into ARCHIBUS. In these cases you will need to ensure that the spreadsheet file has the proper schema information and formatting, as described in this section.

Transfer files store data in a form designed to be reliably validated and reread by the program, regardless of the language and locale for the user who exported the data. For this reason, import files must:

Use ISO format for dates and times, for example, "2008-12-02". See How to Import Date Data from Excel.
Use stored values for enumerations, that is, the value that the program stores in the database. For example, Work Request Status would have values, such as "I", "CO, and "CL", rather than Incomplete, Completed, and Closed.

For XLS files, the first row provides the table information. The second row contains the schema header information in table.field format, for example, rm.bl_id, rm.fl_id, rm. rm_id.

For CSV files, the first line of the file is a comment holding the schema header information. This is a list of import fields in table.field format, for example, rm.bl_id, rm.fl_id, rm. rm_id.

Alternately, you can use the Data action button to export an XLS or CSV file from ARCHIBUS that has the table and columns of data that you would like to import. See step 1 of How to Read Information from a Spreadsheet File.

Formatting Date Data in Excel

The Data Transfer feature requires all date fields, in a file that will be transferred in, to be in the format of 'yyyy-mm-dd'. When you export date data to Excel using ARCHIBUS data transfer, the date is exported using the ISO date standard (yyyy-mm-dd). However, if you make changes to dates or enter new dates in the file using Excel, the dates are saved using your Windows Regional Date setting. If you want to change the dates and re-import them into ARCHIBUS, you can take one of two approaches:

Change your regional date settings to yyyy-mm-dd. The year has to be first and you have to use dashes not slashes.
Alternately, leave your Regional Date Setting as they are, and use Excel's Format command to format the column in "YYYY-MM-DD" format. Select the column, right-click the column, and from the context menu, select Format Cells. From the Number tab, select the Custom category, type in the format, and click OK.

Tracking the Status of Fields when Importing

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.

How to Read Information from Another Spreadsheet File

To read information from another spreadsheet file:

Use the Data action button to export an XLS or CSV file from ARCHIBUS that has the table and columns of data that you would like to import. This step creates a file that contains the proper table and column heading information needed to match the spreadsheet data to your ARCHIBUS database project. Alternately, you can create your own CSV or XLS file with the proper formatting and schema information. See Preparing the Import File.
Load the file in your spreadsheet program and delete any data.
Query or copy and paste the data into the appropriate columns of the spreadsheet.
Save the spreadsheet as either a CSV or XLS file.
Load the view that has the data you want to import, and click the Data action button. When working from Smart Client, you use the View / Transfer command on the Ribbon. For information on the views from which you can transfer data, see Which views are available or data transfer.
On the Data Transfer screen, select the Transfer In action and click Continue.
Select Local File and Browse to select the spreadsheet file, and, optionally, select Generate Comparison Reports? to generate insert and update logs that show the added and changed records. See Generating Comparison Reports.
Click Begin Transfer.
The Transfer Progress tab shows the number of records that are processed and the percentage complete for the job. The progress bar indicates when the import has completed. If you have generated Comparison reports, the URL for the reports appear in the lower section of the screen. If there are any errors, an error log is generated.

How to Import Data

You can import data by transferring in a file exported from another database or by transferring in a local file.

Note: If you are importing or exporting document fields, see Working with Document Fields Using Data Transfer.

To import data:

Prepare the import file. See Preparing the Import File
Load the view that has the data you want to import, and select the Data action button. From the Smart Client, run the View / Transfer command from the Ribbon.
On the Transfer Action tab, select the Transfer In action, and click Continue.
On the File to Import tab, select one of the following for the file to be transferred in:
- Server File: The system finds the last file output for this table by this user on the server. The file is always named after the table so the file name is predetermined.
- Local File: Select this to upload a file on your local workstation. Use the Browse button to locate the file.
Select the check box for Generate Comparison reports?. This will generate insert and delete logs for the import. If you do not select this check box, only an error log will be generated if there are any errors to report.
Click Begin Transfer.
The Transfer Progress tab appears. From this tab, you can view the progress of the job, and load the Comparison reports (insert and update logs) when the transfer completes if you selected to generate them. The lower section of the screen provides a link to these logs and to the error log if any errors occurred.
If there is a data transfer status field for the table holding the import, that field is updated for each record. See Data Transfer Status Field.

Note: The database does not have records that failed insertion, for instance because they had a validation error. These records are recorded only in the error log for your information.

Fixing Errors

When importing, the error log shows the following errors:

Fields that were in the import file, but are not part of the database. If the field in the import file is an error, correct the value for the field in the import file. If the field in the import file is correct, add the field to the database and then re-import the file.
The table's primary keys must be included in the import list. If one or more of the primary key fields for the table are missing, the import is stopped. The solution is to enter all required primary key fields for the table. For example, the Rooms table requires the Building Code, (bl.bl_id), the Floor Code (fl.fl_id), and the Room Code (rm.rm_id).