E9.2 Forcing a UBEs CSV output to have quoted variables (text delimeter) "column1", "column2"

[JohnDanter2]

Hi folks

I have to write a bank payments file and CSV is a format they can take, so to save time I've just made a CSV type UBE, but.....

I've noticed in notepad the output is like this, but they need the values surrounded in quotes

BULKCSV,GLOBAL TURNKEY (SINGAPORE),BANTABCDEFWF,KXXSXYZKC,20240507,SG9678240507161152.CSV,TEST

If I wrap the text in quotes in the UBE itself I end up with this mess

"""BULKCSV""","""GLOBAL TURNKEY (SINGAPORE)""","""BANTABCDEFWF""","""KXXSXYZKC""","""20240507""","""SG9678240507162150.CSV""","""TEST"""

Short of me making a CSV file myself using a long concat srting and output to flat file BSFNs (something I was hoping to avoid), how do I get E1 to make a CSV with quotes around each column, like this

"BULKCSV","GLOBAL TURNKEY (SINGAPORE)","BANTABCDEFWF","KXXSXYZKC","20240507","SG9678240507162150.CSV","TEST"

Thanks

John

 

[BOster]

I don't have a good or easy answer... really any answer... for you but you have triggered my soapbox. Properly formatted CSV. There is a specification (RFC 4180) that no one, not Oracle, not MS, no one, can seem to follow although as far as data format standards go its not very complicated. Sounds like your bank wants a CSV that actually follows the standard. None of this "just use pipe delimiter and everything will be ok" nonsense. I would NOT try to roll your own by using string functions - although you may get luck and it may work most of the time for the data you dealing with. You will find the standard will quickly make rolling your own an impossible task in ER code if you truly do try to follow the standard - you have to properly escape text qualifiers for example. This would even be a difficult C BSFN task with with a lot better string manipulation tools at your disposal.

As an example (I know I put quotes in your data that was not there originally but this is to illustrate):

GLOBAL "TURNKEY" (SINGAPORE)

would be:

"GLOBAL ""TURNKEY"" (SINGAPORE)"

That was from memory so I may have got that wrong... that's why I...

...found a good open source library that could parse and output CSV data that follows RFC 4180 and I put a JDE C API wrapper on that so I had a JDE centric API I could use. Whenever I needed to parse or output a CSV files that is what I have used for about 10-15 years. So it wasn't a standard out of the box solution.

I am NOT a BI Pub guru but there might be a solution there?????? Outside of that possibly Orchestrator - calling all Orch gurus???????? If I was looking for a solution today and I didn't have my custom "legacy solution" I got now I would almost certainly look to Orchestrator. Even if they don't have an out of the box solution certainly Groovy/Ruby/Jython has libs that can create a well formed CSV file so you could effectively do what I did in C but in Orchestrator.

 

[JohnDanter2]

Awesome. Good man Brian. I'll take a better look at that tomorrow

Such a shame the UBE doesnt have a tick box somewhere to do this.
CSV is the only option and it comes out as I've posted.

I may try and research and after the fact option too. Some sort of parsing or manipulation I can do after E1 has made the CSV file

 

[Kim Schmidt]

John, if the UBE is creating the CSV as strings accessible with ER code, you could possibly use the C BSFN Replace String With A String (sp?), converting every occurrence of doublequote-doublequote to doublequote . That function is also useful when you need to parse an incoming delimited file that has consecutive delimiter characters.
Related: It's annoying that the E1 function Parse Delimited String ignores consecutive delimiters which may have been intended to surround a null string token. Replace comma-comma with comma-space-comma, then the string can be tokenized correctly.

 

[maguirem]

I don't have a good or easy answer... really any answer... for you but you have triggered my soapbox. Properly formatted CSV. There is a specification (RFC 4180) that no one, not Oracle, not MS, no one, can seem to follow although as far as data format standards go its not very complicated. Sounds like your bank wants a CSV that actually follows the standard. None of this "just use pipe delimiter and everything will be ok" nonsense. I would NOT try to roll your own by using string functions - although you may get luck and it may work most of the time for the data you dealing with. You will find the standard will quickly make rolling your own an impossible task in ER code if you truly do try to follow the standard - you have to properly escape text qualifiers for example. This would even be a difficult C BSFN task with with a lot better string manipulation tools at your disposal.

As an example (I know I put quotes in your data that was not there originally but this is to illustrate):

GLOBAL "TURNKEY" (SINGAPORE)

would be:

"GLOBAL ""TURNKEY"" (SINGAPORE)"

That was from memory so I may have got that wrong... that's why I...

...found a good open source library that could parse and output CSV data that follows RFC 4180 and I put a JDE C API wrapper on that so I had a JDE centric API I could use. Whenever I needed to parse or output a CSV files that is what I have used for about 10-15 years. So it wasn't a standard out of the box solution.

I am NOT a BI Pub guru but there might be a solution there?????? Outside of that possibly Orchestrator - calling all Orch gurus???????? If I was looking for a solution today and I didn't have my custom "legacy solution" I got now I would almost certainly look to Orchestrator. Even if they don't have an out of the box solution certainly Groovy/Ruby/Jython has libs that can create a well formed CSV file so you could effectively do what I did in C but in Orchestrator.

I have written more than a dozen roll-your own CSV files as extracts from UBE's using ER. Attention to detail and lots of testing. Better control over the file name and destination too. The standard CSV output is only fit for extracting to Excel

 

[JohnDanter2]

I've given up and decided to make a text file lol

 

[BOster]

As long as your text data dosen't contain your text qual character it should probably work unless you do undertake the extra effort to properly escape them.

It's a shame that UBEs (and Excel for that matter) don't simply adhere to the standard and we are left to roll our own like what you now have to do. I will say it is much much easier to output CSV than parse it which is what drove me to find a library to begin with.

 

[Kevin Long]

I don't have a good or easy answer... really any answer... for you but you have triggered my soapbox. Properly formatted CSV. There is a specification (RFC 4180) that no one, not Oracle, not MS, no one, can seem to follow although as far as data format standards go its not very complicated. Sounds like your bank wants a CSV that actually follows the standard. None of this "just use pipe delimiter and everything will be ok" nonsense. I would NOT try to roll your own by using string functions - although you may get luck and it may work most of the time for the data you dealing with. You will find the standard will quickly make rolling your own an impossible task in ER code if you truly do try to follow the standard - you have to properly escape text qualifiers for example. This would even be a difficult C BSFN task with with a lot better string manipulation tools at your disposal.

As an example (I know I put quotes in your data that was not there originally but this is to illustrate):

GLOBAL "TURNKEY" (SINGAPORE)

would be:

"GLOBAL ""TURNKEY"" (SINGAPORE)"

That was from memory so I may have got that wrong... that's why I...

...found a good open source library that could parse and output CSV data that follows RFC 4180 and I put a JDE C API wrapper on that so I had a JDE centric API I could use. Whenever I needed to parse or output a CSV files that is what I have used for about 10-15 years. So it wasn't a standard out of the box solution.

I am NOT a BI Pub guru but there might be a solution there?????? Outside of that possibly Orchestrator - calling all Orch gurus???????? If I was looking for a solution today and I didn't have my custom "legacy solution" I got now I would almost certainly look to Orchestrator. Even if they don't have an out of the box solution certainly Groovy/Ruby/Jython has libs that can create a well formed CSV file so you could effectively do what I did in C but in Orchestrator.

You can use the Apache CSVWriter from Groovy. I have used it for producing csv files, mostly in Excel format, but it also supports several other csv formats including RFC4180. You can also define the header record, delimiters, etc. with it.

Here is a code sample from ChatGPT (@DaveWagoner, what have you done to me???!!!).

import org.apache.commons.csv.CSVFormat
import org.apache.commons.csv.CSVPrinter

// Define the data to export
def data = [
["Name", "Age", "City"],
["John", 30, "New York"],
["Alice", 25, "Los Angeles"],
["Bob", 35, "Chicago"]
]

// Define the file path
def filePath = "output.csv"

// Create CSV Printer
def writer = new FileWriter(filePath)
def printer = new CSVPrinter(writer, CSVFormat.RFC4180)

// Write data to CSV
data.each { row ->
printer.printRecord(row)
}

// Close the printer
printer.close()

println "CSV file exported to $filePath"

You can find the documentation here: https://commons.apache.org/proper/commons-csv/

 

[DaveWagoner]

I'm telling you!! I bow to our GPT overlords. hopefully you told it "thanks" afterwards.

I'll present on this at next InFocus conference, perhaps with one other TechSIG board member. We'll go over how to "plug it in" to orch, what to do if you error, etc.

 

[Kevin Long]

For me, it is the next evolution of search. For things I don’t do everyday, I was always going back to google and trying to find the source documentation that I used the last time it came up. AI is very good at retrieving exactly what I am looking for very quickly. For example, I could have gone back and found some existing code for creating a csv and then sanitize it to remove any client specific information, or ChatGPT can spit some code generic out in a few seconds.

 

[DaveWagoner]

EXACTLY. Those of us who made a trade out of being decent at getting to what we need via effective googling can pivot to GPT-- it's superGoogle.