Zoomed Image
USA
800 691 9120
UK
01225 704844

How to Import Data from a CSV File

xAssets Configuration Guide
How To

How to Import Data from a CSV File

This page explains how to import data from a CSV or tab-separated text file into xAssets using the transformation system. This is the standard method for bulk data loading from spreadsheets and external systems.

Prerequisites

  • You must have Configuration-level access or membership of a user group with transformation editing permissions.
  • Your data file must be in CSV (comma-separated) or TXT (tab-separated) format. Excel files cannot be used directly -- save as CSV or tab-separated text first.
  • Know which xAssets table the data should be imported into (e.g., Asset, Custodian, Contract).

Step 1: Prepare the Data File

  1. Open your data in a spreadsheet application.
  2. Ensure the first row contains column headers that clearly describe each field.
  3. Clean the data: remove blank rows, fix inconsistent values, and ensure required fields are populated.
  4. Save the file as CSV (comma-separated values) or Tab-separated text (.txt).

Step 2: Create a New Transformation

  1. Navigate to Admin > Transformations.
  2. Click the New button to create a new transformation, or clone an existing import transformation that targets the same table.
  3. Name the transformation descriptively (e.g., "Import Assets from CSV").

Step 3: Configure the Data Source

  1. In the Transformation Editor, open the Data Source tab.
  2. Set the source type to Upload File if users will upload the file through the browser, or Server File if the file is already on the server.
  3. For Upload File, no further source configuration is needed -- the user will be prompted to select a file when the transformation runs.
  4. For Server File, enter the full file path on the server (e.g., C:\imports\assets.csv).

Step 4: Configure the Data Destination

  1. Open the Data Destination tab.
  2. Set the destination to the target xAssets table (e.g., Asset, Custodian).
  3. Choose the operation mode:
    • Insert -- create new records only.
    • Update -- update existing records only (requires a key field to match records).
    • Insert or Update -- create new records if they do not exist, update existing ones if they do.

Step 5: Map Source Fields to Destination Fields

  1. Open the Data Mapping tab.
  2. For each column in your CSV file, map it to the corresponding destination field in the xAssets table.
  3. Use the data mapping interface to pair source column names with destination field names.
  4. For fields that need transformation (e.g., date format conversion, value lookups), configure the appropriate mapping rules.
  5. Set a key field if you are using Update or Insert/Update mode. The key field is used to match incoming records with existing records in the database.

Step 6: Test the Import

  1. Save the transformation.
  2. Run the transformation manually from the Transformation Editor or from its menu item.
  3. If using Upload File, you will be prompted to select your CSV file.
  4. Review the results:
    • Check the batch job status at Admin > Batch > Batch Jobs for any errors.
    • Verify the imported records in the target table by running a query.
    • Confirm data quality -- check that mapped fields contain the expected values.

Step 7: Create a Menu Item (Optional)

If this import will be run regularly by other users:

  1. In the Transformation Editor, open the Menus tab.
  2. Create a menu item that runs this transformation.
  3. Set the menu category and sort code to place it in the appropriate location in the menu.
  4. Configure user group menu access to control who can run the import.

Tips

  • Always test with a small sample file first before importing the full dataset.
  • If you need to import from Excel, save as tab-separated text first. Copy-and-paste import is also available using the Paste Data source type.
  • For recurring imports from external systems, consider using the Server File source type and scheduling the transformation to run automatically. See How to Set Up a Scheduled Batch Job.
  • Browse the built-in system transformations at Admin > Transformations for examples of import patterns you can clone and modify.