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:
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:
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
Discussion posts and replies are publicly visible
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?
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.
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
Hi Chris,Yes we did that but as mentioned already in the post , the problem with that is
Now export of csv looks like below which doesn't serve our requirement. smart service adds extra double quotes as soon as it sees quotes in data.
Apologies, I missed that piece. To confirm, the smart service is adding quotes only when it sees quotes in the data, and it adds them to the entire data set (vs only that one data point)?
In which case sometimes it meets your requirement, and sometimes it does not?
Hi Chris,Our requirement is to enclose data in double quotes. Our test scenarios with existing smart service are like below
Test case1: In this case, smart service exports data without any double quotes
Output of smart service csv file with comma as delimiter:
column1,column2,column3
testdata11,testdata12,testdata13
testdata21,testdata22,testdata23
Test case2: In this case, smart service exports data adding double quotes to the data that has any quotes example row 2 here has one quote like test"data22
testdata21,"test"""data22",testdata23
Test case3: As exporting of data using smart service to csv doesnt by defualt enclose it with double quotes. we thought of creating view that transforms data to add double quotes in the view itself like below
Now export of csv looks like below which doesn't serve our requirement. A csv file with comma as delimiter but with extra double quotes:
instead of
Hope this is clear.
Apologies for being dense this morning - I've finished my coffee and now understand
Any location the smart service sees a quote it will wrap with additional quotes as you have experienced. It is unlikely you will see changes to the smart service functionality (at least within a short time period).
As you have migrated to a manual CSV creation instead, this is what I typically do anyway vs relying on the CSV services. For your scalability issues, how many rows are you sending / how long is the file taking to generate, etc?
Is it safe to assume the system ingesting this CSV cannot be adjusted? For example where you could send changed/new data only, and not any data that has been sent previously?
One other thought is that the Excel Tools plugin also contains a Convert Excel to CSV service - I don't have experience with this, but it might be a good test to see if you can export from your view with quotes via Export DSE to Excel, then convert that Excel file to CSV with this service to see if that works differently.
Hi Chris,Thank you for the response. Yes tried the option with convert excel to csv as well but i get the error like this though ???Index: 1, Size: 1???. But felt having control over transformation of data through expression is better at this moment as csv smart service doesn't meet our requirement.Just wanted to check if there are any other better alternatives in latest version than using "Text doc from template"Now from you and stefan i understand and its clear that there is no better alternative yet even with recent versions for this requirement than using the text doc from template.
Thanks both of you!!