Import a CSV file with ER (II)

This article is the second in a series of three about importing CSV files into the system. We will explore the format, the format mapping, and how to test the development. In a future article, I will cover a topic that requires more advanced knowledge of Electronic Reporting and programming logic in general for addressing a possible issue with the standard pattern.

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.

If you’re working in the Spanish language, you might encounter a funny mistranslation by Microsoft where “BOM” (Byte Order Mark) has been translated to “LMAT” (Lista de Materiales, which actually means Bill of Materials).

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:

For these cases, we’ll need to change how we parse the CSVs using a non-standard method. We’ll explore this in a future article.







Leave a Reply

Your email address will not be published. Required fields are marked *