Open topic with navigation

ARCHIBUS Smart Client

ARCHIBUS Web Central

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

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.

How to Read Information from Another Spreadsheet File

To read information from another spreadsheet file:

1. 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.
2. Load the file in your spreadsheet program and delete any data.
3. Query or copy and paste the data into the appropriate columns of the spreadsheet.
4. Save the spreadsheet as either a CSV or XLS file.
5. Load the view that has the data you want to import, and click the Data action button.
6. On the Data Transfer screen, select the Transfer In action and click Continue.
7. 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.
8. 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.

To import data:

1. Prepare the import file. See Preparing the Import File
2. Load the view that has the data you want to import, and select the Data action button.
3. On the Transfer Action tab, select the Transfer In action, and click Continue.
4. On the File to Import tab, select one of the following for the file to be transferred in:
   • Server File: This is 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.
5. 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.
6. 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).