1. Home
  2. Account and Permissions Mapping
  3. Account Mapping – Metadata Job Preparation

Account Mapping – Metadata Job Preparation

Before you can actually configure your metadata job, you must do two things:

  1. Generate a .csv file of all metadata on the source
  2. Create a metadata template on the target

This article describes how to complete these two tasks.

Generate a .csv file

The .csv file that you create for your metadata must be in the following format:

First column header must be ‘Source Path.’  This column will list the path for each of the files in your job that contain metadata.

The paths must be absolute, and match what is in your job configuration.  For example, if your files reside in //myserver/Customers, a file invoice.pdf in //myserver/Customers/12345 should be represented in your .csv with a path of //myserver/Customers/12345/invoice.pdf. If you look at the way your file paths are represented in the analysis report and make sure that the paths in your .csv file match those in the analysis report, you’ll be in good shape.

The other columns to the right of the Source Path will list your metadata.  So the .csv will look like this:

SOURCE PATH CUSTOMER ID INVOICE STATUS SALES ID
//myserver/Customers/12345/invoice.pdf 12345 Paid 7890
//myserver/Customers/12345/bill.pdf 12345 Received 7890
//myserver/Customers/56789/invoice.pdf 56789 Overdue 3456

Pre-validating your metadata file

If you are doing a metadata migration of any size, you’ll have a csv file that may be tens of thousands of lines in length or more; one line for each file that has metadata.  Every line of that file will need to be validated to ensure that the data is in the correct format for the fields in the target template, and if it fails, you’ll need to fix the error in the file and re-run the validation.  You can save yourself a lot of time if you pre-validate the csv file using standard Excel techniques:

  • date fields:  make sure that all of your dates conform to the same format.  The following tool will help you ensure that all dates conform to a single format: https://www.extendoffice.com/documents/excel/3289-excel-convert-date-to-yyyy-mm-dd-format.html.  It’s also a good idea to do a filter on the column to verify that all cells in a date column do in fact contain a date and not some other non-date value such as NO DATE.
  • number fields:  the ISNUMBER() function can help identify non numeric values, but will be true for dates.  Unless you absolutely need a numeric value for a number field in your template, consider using strings instead, which are far more tolerant in validation.
  • enum fields in template:  all values listed in the csv must have a matching value in the enum field.  So if your options in the enum field are red, green, blue, then a value of yellow in the csv will cause your validation to fail.  A simple filter of the column for the enum data will enable you to quickly see all of the values listed for that column, and you can then cross-reference that list against what’s in your template.
  • blank cells will validate.

Build a Target Template

1: We will cover building a template in Box. Sign in as an admin and go to the Admin Console. Select Work Processes from the nav bar:

2: Select the Metadata menu and then select Create New:

3: Click on ‘Name Your Template’ to name it:

4: Click on the ‘Name Your Attribute’ field and name it the same way.  In this example, we are mapping Customer ID field on the source to Customer No. on the target.  Field names on the source can match field names on the target, but they don’t have to.

Select a format for your field. In this example, we are selecting Number.  If one of the Customer ID’s in our data set contains letters, that will generate an error when we try to move it to the target, so data format must be selected carefully. See Account Mapping – Metadata Job Setup for additional information on data format.

5: Click on ‘New Attribute’ to add other fields to your template.

When you are done with your template, save it.

NOTE: CFP will transfer metadata to templates that are hidden. You won’t see those values, of course, until the template is made visible.

Updated on February 22, 2021

Related Articles