The CSV & Excel Scanner is one of the source connectors available in migration-center starting with version 3.9. It scans data from CSV and MS Excel files and creates corresponding objects in the migration-center database. If the scanned data also contains links to content files, the CSV & Excel scanner can link those files to the created objects as well.
When scanning Excel files the “Write Attributes” permission is required otherwise the scanner will throw an “Access is denied” error.
When attempting to scan Excel files with a large number of rows and/or columns the UI might freeze until the following error is thrown:
ERROR executing job: Error was: java.lang.OutOfMemoryError: GC overhead limit exceeded
This is a limitation of the Apache POI API, and the recommendation is to convert the excel file into a CSV file.
To create a new CSV & Excel Scanner job click on the New Scanner button and select “CSV/Excel” from the adapter type dropdown list. Once the adapter type has been selected, the parameters list will be populated with the CSV & Excel Scanner parameters.
The Properties window of a scanner can be accessed by double-clicking the scanner in the list or selecting the Properties button or entry from the toolbar or context menu.
Configuration parameters | Values |
Name | Enter a unique name for this scanner Mandatory |
Adapter type | Select the “CSV/Excel” connector from the list of available connector Mandatory |
Location | Select the Job Server location where this job should run. Job Servers are defined in the Jobserver window. If no Job Server was selected, migration-center will prompt the user to define a Job Server Location when saving the scanner. Mandatory |
Description | Enter a description for this scanner (optional) |
Configuration parameters | Values |
filePath* | The full path to the CSV or MS Excel file to scan. Since the job will be executed on the job server machine, you must provide a valid path on that machine. Mandatory |
sourceIdColumn* | The name of the column in the CSV or Excel file that contains the source ID of the object. Note that the values in this column must be unique. Mandatory |
contentPathColumn | The name of the column in the CSV or Excel file that contains the path to the corresponding content file of the object. |
versionIdentifierColumn | The name of the column in the CSV or Excel file that identifies all objects that belong to a specific version tree. Mandatory when scanning versions. |
versionLevelColumn | The name of the column in the CSV or Excel file that specifies the position of an object in the version tree. Mandatory when scanning versions. |
scanUpdates | Flag indicating if the modified objects will be scanned as updated objects or will be ignored. |
enrichMetadataForScannerRun | The run number of the scan run that you want to enrich. Mandatory when using enrichment mode. |
enrichMetadataPrefix | Optional prefix for the columns that will be added to the objects when running in enrichment mode. |
multivalueFields | The names of the columns that will have multi-values separated by a delimiter. |
multivalueDelimiter | The delimiter will be used to separate the values of multi-value columns. |
loggingLevel* | Sets the verbosity of the log file. Values: 1 - logs only errors during scan 2 - is the default value reporting all warnings and errors 3 - logs all successfully performed operations in addition to any warnings or errors 4 - logs all events (for debugging only, use only if instructed by fme product support since it generates a very large amount of output. Do not use in production) Mandatory |
The CSV & Excel scanner supports two operation modes: normal and enhancement mode. In normal mode, you can scan objects from a CSV or Excel file and those objects are saved in the corresponding scan run as new, distinct objects in the MC database. In enhancement mode, you can scan data from a CSV or Excel file that is added to an existing scan run, i.e. it enhances an existing scan run with additional data.
In normal operation mode, you can scan objects without content, objects with content, and objects with versions from a CSV or Excel file.
Scan objects without content
In order to scan objects without content, you just specify the path to the CSV or Excel file and the name of the column that contains the unique source ID of the objects. For example:
The screenshot below shows an excerpt of CSV file you would like to scan.
You would then enter the path to the CSV file in the “filePath” parameter and enter “id” as value in the “sourceIdColumn” parameter (because the column named “id” contains the unique IDs of the objects in the CSV file).
Scan objects with content
If your source file contained a column with a file path, as seen in the next screenshot, you can enter that column name (“profile_picture” in the example) in the “contentPathColumn” in order to scan that content file along with the metadata of the object.
Scan objects with versions
In order to scan objects with versions, your source file needs to contain two additional columns: one column that identifies all objects that belong to a specific version tree (“versionIdentifierColumn”) and another column that specifies the position of an object in the version tree (“versionLevelColumn”).
Starting with version 3.16, the scanner allows adding new versions to an existing version tree (scanned in a previous run) just by setting the specific root id.
Note: If the last version was updated, the new scanned versions will be related to the updated object.
In the example below, the source file contains metadata of documents that consist of several versions each. Each document has a “title” and a “document_id”. Each version has a “version_id” and a “content_file”. The combination of “document_id” and “version_id” must be unique. Since the content file path is unique for each version in this example, you could use that column as “sourceIdColumn”.
A valid configuration to scan the example source file could look like this:
There are many document management applications, which reference data in third-party-systems. If you want, for example, to archive documents from such an application, it might be useful to store that referenced data also in the archive records, in order to have independent, self-contained archive records. To achieve this, you could (1) scan your document management system with the appropriate scanner, (2) export the data from the third-party-system into a CSV file, and (3) enhance the scan run from step (1) with the data from the CSV file using the CSV & Excel scanner in enhancement mode.
The source file for an enhancement mode scan needs a column that stores the source ID of the corresponding object, i.e. the object that will be enhanced with the data in the source file, in the specified scan run.
For example, the following table shows an excerpt of an existing scan run. The source ID of the objects is stored in the column “Id_in_source_system”. In this case, the ID is the full path of the files that were scanned by the scan run.
The source CSV or Excel file for the enhancement scan should contain all the data that you would like to add to the previously scanned objects and a column with the source ID of the corresponding object, as shown in the following table:
In order to run the scanner in enhancement mode, you need to enter a valid scan run ID in the parameter “enrichMetadataForScanRun”. You can find that ID in the -History- view of a scanner:
The “sourceIdColumn” parameter should contain the name of the column with the source ID. That would be “source_id” in the example above.
You can provide an optional prefix for the columns that will be added to the scan run in the “enrichMetadataPrefix” parameter. This is helpful in the case when your source file contains columns with names that already exist in the specified scan run.
Of course you can enhance a certain scan run several times with different source files.
The scanner allows splitting values from one or multiple columns in multiple distinct values based on a delimiter.
For example, in this particular case, if you want to split the values of columns Record_Series and Classification, all you need to do is to add the column names in the multivalueFields parameter and set the "|" delimiter in multivalueDelimiter parameter:
If the delimiter between the values will not correspond with the one that was insered in the multivalueDelimiter attribute, the cell value will be no longer considered a multi-value so it will be stored as single-value.
Limitations
The user cannot set as input for multivalueFields the value from fields sourceIdColumn, versionIdentifierColumn, or versionLevelColumn because those are single-value fields.
If the parameter multivalueFields is set then parameter multivalueDelimiter must be set as well.
Starting with 3.16 of migration-center, the CSV/Excel scanner is able to scan existing objects as updates. When the scanUpdates parameter is checked, the scanner compares the attribute values of the previously scanned object with the ones in CSV/Excel. If they are not exactly the same then the object will be scanned as an update. If they are the same, the object is ignored and an info message will be added in the report log. When the scanUpdates parameter is not checked, the modified objects will be ignored and an info message will be logged.
