Hello,
We need to export all table and views from Database to CSV file through Appian process. The environment is setup in Appian cloud. The column data should be enclosed in double quotes and comma separated - like in example below:
"column header1","column header2"
"col1-value1","col2-sample-value2"
"col1-value2","col2-sample-value2"
We are using Export SQL to Flat File smart service to this. The headers come out with double quotes, however, the values are not enclosed in double quotes. The whole process is automated, as we do not have SQL script built do do this. We use information_schema table of MYSQL to find the tables and then use "Select *" to build the query. Can we get help on how to enclose double quotes on the column values?
Thank you,Harish
Discussion posts and replies are publicly visible
Hello Harish,
I have one question, who is reading the target file? excel? in taht case this implementation will be enough, but if something else is reading it please keep reading.
I was reviewing the code and I found that they are returning the value if the column is string/varchar "return StringEscapeUtils.escapeCsv(string)". based on the documentation you will find.
Returns a String value for a CSV column enclosed in double quotes, if required. If the value contains a comma, newline or double quote, then the String value is returned enclosed in double quotes. Any double quote characters in the value are escaped with another double quote. If the value does not contain a comma, newline or double quote, then the String value is returned unchanged.
Reference: https://commons.apache.org/proper/commons-lang/javadocs/api-3.1/org/apache/commons/lang3/StringEscapeUtils.html#escapeCsv(java.lang.String)
For example, if you do something like this in the query
="select concat( '""',concat(firstname,'""')) as firstname ,lastname from test.people;"
You will get in your file
"""Harish""", """LastName"""
If you really need the values to be quoted you will need your target system (the one which reads the csv to do something special with this).
Hope this helps
Jose Perez