I tried to read and output CSV with Outsystems

Introduction

I will post my second article on the 2019 Advent Calendar, which is a member of the company's IT members! This is the article I wrote last time, which also serves as a self-introduction. https://qiita.com/tom-k7/items/d8ef19dccb42891a0698

I will write about Outsystems this time as well. Right now, I'm developing a CSV conversion tool (web application) with Outsystems, so I will share how to implement CSV reading and output with Outsystems, and the wall that I encountered during development.

Thing you want to do

What I want to do with the tool I'm developing now is CSV files downloaded from various other companies' sites I want to convert it to a CSV file in a format that can be imported into the system used in-house. So, read the downloaded CSV file and Outsystems makes it possible to convert and output a new CSV file. Forge Use the Forge component of CSVUtil to read and output CSV files. https://www.outsystems.com/forge/component-overview/636/csvutil

Download it, install it in Service Studio and add it to Dependency.

Screen development

I will make a screen before doing CSV processing.

CSV file upload

First of all, you need to upload the CSV file to be read. There are two ways. ① Use Upload ② Use RichWidgets \ Popup_Upload

This time, ① is better, so I will use Upload.

• By the way, please note that the Upload widget does not work in a list like List Records.

Place a button to call the server action that performs the conversion process

Specify the Convert server action to perform conversion processing in the Destination of the Convert button, Perform the conversion process within that server action.

Implementation of CSV reading process

Now, from here on, it's the CSV processing of the main subject. First, implement reading of CSV file.

LoadConfig settings

Define a CSVLoadConfig type local variable for setting when reading CSV, and set it with Assign. The main settings are as follows.

--Encode: Character code of CSV file. Set utf-8, shift-jis, etc. --IsSkipHeader: True if the CSV file has a header line, False otherwise. --IsIgnoreColumnChange: ~~ False if an error occurs when the number of columns does not match, True if no error occurs. ~~ I tried it and it was different. .. This field is a mystery "(-" "-)" --FieldDelimiter: Delimiter between columns. Multiple characters can be entered, but only the first character is applied. --IsDisableDoubleQuote: False if the column is enclosed in double quotes, True otherwise.

Define RecordList for reading

Define a list of Entity / Structure Records that stores the result of reading CSV with local variables. This time, I made a RecordList of Structure called SourceCSV that I made.

• It will not work unless it is a List of Structure Record instead of a Structure List.

Call Extension processing

Call the LoadCSVRecordList server action defined in Forge's CSVUtil and The arguments are the Upload Content created on the screen and the CSVLoadConfig local variable. Set the result of converting RecordList of Entity / Structure with ToObject (). This completes loading.

Implementation of CSV output processing

Next, we will implement the CSV output as well.

ExportConfig settings

Define a CSVExportConfig type local variable for setting at the time of CSV output, and set it with Assign. The main settings are as follows.

--IsShowHeader: True if you want to include the header line in the CSV file, False otherwise. --FieldDelimiter: Delimiter between columns. Multiple characters can be entered, but only the first character is applied. --EncodeMode: Whether to enclose the column in double quotes. Set one of auto / quote / noquote / noquote_nocheck as a character string. --LineSeparator: Line feed code. Set Chr (13) for CR, Chr (10) for LF, and Chr (13) + Chr (10) for CRLF.

Define RecordList for output

Define a list of Entity / Structure Records for storing CSV data to be output with local variables. The image is a RecordList of Structure called OutputCSV that I made.

Convert and set the data read into RecordList for output

For the output RecordList defined in ↑, set all the records of the RecordList containing the loaded data. At that time, you can convert the values, combine them, discard unnecessary ones, and set them for output. In the image, ListAppendAll is used to simply combine the items, but in reality, various conversions are performed here. If you want to do something quite complicated, you may loop in For Each.

Call Extension processing

Call the ExportRecordList2CSV server action defined in CSVUtil Set the result of converting RecordList of Entity / Structure with ToObject () and the local variable of CSVExportConfig in the argument respectively.

Output (download) CSV file after conversion

Finally, pass the CSV data created by ExportRecordList2CSV to the Download widget and finish.

Trap caught during development

The above is the implementation method, but I would like to share other walls that I hit using CSVUtil.

It is difficult to understand how to enclose it in double quotes when outputting

The method of enclosing with double quotation marks in "Export Config Settings" has already been described, It took me quite a while to get there. Because the name of the item is EncodeMode. .. I think it's a character code. I don't know. .. It was a bittersweet ww

By the way, if you specify "quote" in CSVExportConfig.EncodeMode, it will be enclosed in double quotation marks!

NullReferenceException in Export process

I got this error when calling the ExportRecordList2CSV server action of CSVUtil. Object reference not set to an instance of an object. NullReferenceException. It's a nullpo in Java. Since only the basic type is used as a CSV item, is there Null in the basic type in Outsystems? It was like that.

So this error doesn't log in any more detail, so I was at a loss as a result of trying various things. I have no choice but to look inside the Extension! It became. I downloaded the extension with, opened sln in Visual Studio, and followed the source, but I couldn't figure out the cause. By the way, I made the data that I actually passed on C # and executed it! (^^)! (This was sober and annoying)

As a result of trying, it turned out that a NullReferenceException occurred because one column of the passed data was missing. I was wondering what that column was, but it was a column that did something special when converting data.

I'm sorry if the explanation doesn't make sense <m (__) m> For example, if the value of the read data is "1", define something like "graduate school" in the JSON file as a Key / Value pair after conversion. Import the JSON file into the module as Resources.

Read the JSON file with JSONDeserialize, use Forge's HashTable to store it in memory as a Key / Value pair, At the time of conversion, the process was to set the value obtained from the HashTable.

Forge Hashable: https://www.outsystems.com/forge/component-overview/21/hashtable

Since it was in the CSV that data that is not in the Key defined in JSON (such as "5" or empty string in ↑ JSON) is included, the result of get becomes Null, It was a NullReferenceException because I passed it to ExportRecordList2CSV.

Null is not included in Text type when implemented normally in Outsystems, I learned that Null can be entered via Extension, and I thought it was a learning experience.

Finally

Explained CSV reading / output in Outsystems. I hope it will help those who use CSVUtil and those who hit the same wall from now on.