Import a CSV file with Electronic Reporting (II)

<- Part 1

This article is the last of the series about importing CSV files into the system. We will explore the format, the format mapping, escaping characters and how to test the development.

Once we have the model and the mapping with the status completed, we can continue by creating an import format under our model:

Let’s create a file in the root, using UTF-8 without BOM, as this is the standard encoding.

Now we can add the rest of the nodes. The first sequence will use the ‘Special character’ New Line (CRLF) as a delimiter, and the second will use a ‘custom delimiter’ comma ‘,’ as its delimiter. Additionally, set the ‘Multiplicity’ to ‘ZeroMany’ because it represents a list of records. Finally, the field for the date should have the format ‘ddMMyy’ without quotes, and in the enum ‘DateTime Type’, set the type to ‘Date’.

Let’s create the format mapping by selecting the top button labeled ‘Map Format to Model’. After creating a mapping, click on ‘Designer’.

We can bind the format with the model. Ensure that empty fields are assigned a null value (0, 0.0, “”, NullDate(), etc.) instead of being left empty. This is crucial because if these fields are used in the mapping, leaving them empty will result in an error during execution.

Now we can test if our format and format mapping are correct. Go back to the previous screen and run the test. If everything is OK, we’ll receive an XML with the fields we’ve mapped.

Let’s complete the format and run it from the mapping node we created in the previous article (not from the format mapping). If we had more than one format and wanted to run it from the mapping, we would select one as the default:

Now that our CSV import ER is complete, we can continue using it from the ER workspace or ask a programmer to integrate it into the UI.

And our CSV importer will work happily ever after… unless a CSV contains a value in a column with a symbol that will break our ER:

Escaping characters in CSV

To handle these cases, we need to adjust how we parse the CSV files. Simply select “All” in the quotation application and use '"' for the quotation mark.