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 image.png ② Use RichWidgets \ Popup_Upload image.png

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

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. image.png

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. image.png 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. image.png

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 (). image.png 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. image.png 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. image.png

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. image.png 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. image.png

Output (download) CSV file after conversion

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

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. image.png

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.

Recommended Posts

I tried to read and output CSV with Outsystems
I started MySQL 5.7 with docker-compose and tried to connect
I tried to interact with Java
I tried to get started with WebAssembly
I tried to implement ModanShogi with Kinx
I tried to measure and compare the speed of GraalVM with JMH
I tried to make Basic authentication with Java
I tried to manage struts configuration with Coggle
I tried to manage login information with JMX
I tried to link grafana and postgres [docker-compose]
I also tried WebAssembly with Nim and C
I tried to output multiplication table in Java
I tried to link JavaFX and Spring Framework.
I tried to break a block with java (1)
Memo: [Java] Process the read csv (extract, change according to the conditions) and output
I tried to convert JavaBean and XML with Jackson formatter XML in this era
I tried what I wanted to try with Stream softly.
I tried to implement file upload with Spring MVC
I tried to implement TCP / IP + BIO with JAVA
[Java 11] I tried to execute Java without compiling with javac
I tried to integrate AWS I oT button and Slack
I tried to get started with Spring Data JPA
I tried to draw animation with Blazor + canvas API
I tried to implement Stalin sort with Java Collector
I want to transition screens with kotlin and java!
I tried to chew C # (reading and writing files)
roman numerals (I tried to simplify it with hash)
I tried to create a shopping site administrator function / screen with Java and Spring
I called YouTube video from DB with haml and tried to embed and display it
csv file output with opencsv
I tried DI with Ruby
CSV output with Apache Commons CSV
I tried node-jt400 (IFS read)
I tried UPSERT with PostgreSQL.
I tried BIND with Docker
I tried to verify yum-cron
I tried to make an introduction to PHP + MySQL with Docker
I tried to create a java8 development environment with Chocolatey
I tried to modernize a Java EE application with OpenShift.
I tried to increase the processing speed with spiritual engineering
I tried to collect and solve Ruby's "class" related problems.
I tried to summarize the basics of kotlin and java
[Rails] I tried to create a mini app with FullCalendar
I tried to verify this and that of Spring @ Transactional
I want to make a list with kotlin and java!
I want to make a function with kotlin and java!
I tried to make Java Optional and guard clause coexist
I tried to link chat with Minecraft server with Discord API
I tried to summarize personally useful apps and development tools (development tools)
[Rails] I tried to implement batch processing with Rake task
Even in Java, I want to output true with a == 1 && a == 2 && a == 3
I tried to automate LibreOffice Calc with Ruby + PyCall.rb (Ubuntu 18.04)
I tried to summarize personally useful apps and development tools (Apps)
I want to implement various functions with kotlin and java!
[Rails] I tried to implement "Like function" using rails and js
I tried to create a padrino development environment with Docker
I tried to get started with Swagger using Spring Boot
I tried upgrading from CentOS 6.5 to CentOS 7 with the upgrade tool
[Java] I want to test standard input & standard output with JUnit
I tried to be able to pass multiple objects with Ractor
Easy to make LINE BOT with Java Servlet Part 2: I tried image messages and templates