Skip to main content
sandhyam6767
sandhyam6767
October 17, 2022
Question

Export data store entity to CSV, quotation marks (") automatically added in the generated csv file

  • October 17, 2022
  • 8 replies
  • 0 views

Hi ,

We have a requirement with the third party(old system) to have csv with comma delimiter delivered with data enclosed in double quotes.

Below is sample data:

Data in Database table:

column1 column2 column3
testdata11 testdata12 testdata13
testdata21 testdata22 testdata23

Expected export of csv by third-party should look like below

"column1","column2","column3"

"testdata11" ,"testdata12" ,"testdata13"

"testdata21" ,"testdata22" ,"testdata23"

This was not possible 2 years back with smart service "Export Data Store Entity to csv" so we ended up

1. creating an expression rule that queries data from database and concatenates data of each column and each row with double quotes.

2. generate a text string

3. pass the text string to "Text doc from Template" smart service that creates a csv as expected by third party

But the problem is as the data keeps growing , there is an overkill on the performance to generate csv having the the expression rule that loops through data and does the transformation of data to have double quotes instead of using the smart service that does export of data efficiently and quickly.

So we tried below approach to use the smart service by transforming data in view itself rather than transforming data in Appian but of no luck.

Below is sample data: Data in Database view with transformation of data to have double quotes:

"column1" "column2" "column3"
"testdata11" "testdata12" "testdata13"
"testdata21" "testdata22" "testdata23"

Now export of csv looks like below which doesn't serve our requirement.

"""column1""","""column2""","""column3"""

"""testdata11""" ,"""testdata12""" ,"""testdata13"""

"""testdata21""" ,"""testdata22""" ,"""testdata23"""

Now the Appian "Export data store entity to csv" smart service adds extra double quotes if it finds any double quotes or single quotes in data.

is this expected behaviour in latest version of Appian as well for export smart services to manipulate data with extra quotes rather than just exporting data with specified delimiters?

Would like to know inputs from you if you had the similar situation in your projects.

Many Thanks,

Sandhya

8 replies

stefanhelzle0001
stefanhelzle0001
Brainy
October 17, 2022

This pretty much seems to be expected behaviour. Double quotes are added as necessary to allow a commas or other problematic characters inside a field value.

You said, that you have run into performance problems. Is this because of the data volume? Do you export all data in one go? Did you think about batching?

sandhyam6767
sandhyam6767Author
October 17, 2022

Hi Stefan,


Thank you for the response. Yes, now as the data is growing generating csv is taking longer than it used to be when we have less data... Yes we do in batches though but still trying to explore the options to get rid of this transformation in Appian just to wrap data in double quotes.
Would be nice if OOB "export dse to csv" smart service is leveraged with option of having an input where we specify whether to enclose data in double quotes than using loop functions in batches to do data transformation.

csteward
csteward
October 17, 2022

You could add the quotes to the data store entity itself essentially, by creating a view over your data that performs adds quotes, then point a data store to the new view and utilize this in the Export DSE to CSV service.

SELECT 
'"' + Column1 + '"',
'"' + Column2 + '"'
FROM YOUR_TABLE

Terms & ConditionsAccessibility statement