Hello Everyone,
I am converting HTML to CSV using this process nodes and it's working fine. Although I can export data store entity to CSV- but there is different requirement, so I am not using this approach. Please see below screenshot.
Requirement: When I open that CSV file in Notepad++ I see this:
I need to remove these quotes in a file. How can I achieve this?Thanks
Discussion posts and replies are publicly visible
NOTE, if you are only removing quotes from the CSV file, you may be left with values within the quotes that contain a comma that is not intended to be used as a delimiter, this will break your entire file. This is the purpose of quoting all values within a CSV, which is essentially a standard that all systems designed to accept CSV should honor, in my opinion.
And what is the data like that is coming into the initial HTML node, from a DB query etc? Can you utilize an expression rule to build the CSV file?
In all the CSV files I create, I generate the contents via an expression rule, which gives you full control over the output, then run that into a Text Doc from Template node which only has one placeholder (for the entire text). As I type this however I realize that all of my CSV files are SFTP'd out, which is where I convert them from .txt to .csv, I'm not sure if we have an other way to change file types in Appian..
I am taking data from process variables. because at this time data is not saved into database which is the reason I am not using Export data store entity to CSV. What I have done. I have used node HTML Doc from Template. In this template I have designed a table form data as mentioned below..
<HTML> <head></head> <BODY> <form> <table name="Add" style="border:2px solid black" width="100%" border="1" cellpadding="5" cellspacing="0"> <tr> <th><label> <b> Name </b> </label> </th> <th><label> <b> Date </b> </label> </th> <th><label> <b> Address </b> </label></th> <th><label> <b> Department </b> </label></th> <th><label> <b> Country </b> </label> </th> </tr> ###details### </table> </form> </BODY> </HTML>
We can achieve this ting by using CSV file template in "Text doc from template smart service". It worked.
Sorry, yes now that you mention it, I did know that would be a workable alternative: namely, to create a (text) template that's actually a filename with ".csv" extension. Appian doesn't enforce the extension (and keeps the original) despite it being "text from template".
I reiterate my original warning that the file you're generating here is no longer an actual CSV file (despite the extension you're keeping), since the quoted fields are a part of the CSV file format for parsing sake. If you're lucky the resulting file might still parse correctly by a CSV reader.
Yes. Actually it was the same result of CSV file that I got before. Therefore, CSV file seems to be perfect and parsed properly, also in editor I can this file without quotes.
Can anyone guide how to remove these commas from this file? This file is basically a CSV file that I open in editor.
AFAIK those commas indicate the presence of columns (exported to the CSV) that didn't have any data in them. You could always just pass the text through some function that removes any comma(s) at the end of a line of text... but it might be more worth your time investingating why they're appearing in the first place.
= a!localVariables( local!data: pv!empDetails, local!uniqueName: union( local!data.name, local!data.name ), joinarray( a!forEach( items: local!uniqueName, expression: a!localVariables( local!currentUserIndices: wherecontains( fv!item, touniformstring(local!data.name) ), concat( union( local!data.name_txt, local!data.name_txt ) , ",", union( local!data.address_txt, local!data.address_txt ) , ",", local!data.dept, ",", local!data.side, ",", local!data.city, ) ) ), char(13) & char(10) ) )
Actually, it suddenly appears, Earlier it was working fine- although, I have checked the previous versions as well. But no difference in code. Can you(Mike Schmitt ) please go through the code?
it seems like it's adding a whole extra set of blanks at the end (along with the hardcoded commas you have separating them), though the exact cause is a little hard to tell without seeing an example value for "pv!empDetails".
I would definitely recommend creating an expression rule for this, vs adding the code directly within the process - this way we can utilize different test cases, etc, much more easily.
Good news on the Text Doc from Template working to retain a .csv extension, I could have swore I tried that unsuccessfully in the past - but guess not! Or it was ages ago
You can refer this one:
Yes, I have created a rule for the same. But for now i am just testing it directly. Anyways, I put a rule before proceeding this.
Agree with Mike we need more information on pv!empDetails. Is this single or multi-valued? If it is single, the unions in and a!forEach() are not necessary. If it is multiple, the unions are still confusing to me, and will likely confuse the output as well (and you should be using fv!item.dataPoint. Are you trying to reduce multiple exact rows into 1, etc?
It's multivalued. Actually data is coming right in multiple rows in CSV. The only thing is when I open this file in editor it shows commas at the end of array.
= a!localVariables( fv!item: pv!empDetails, joinarray( a!forEach( items: fv!item, expression: a!localVariables( local!currentUserIndices: wherecontains( fv!item, touniformstring(fv!item.name) ), concat( fv!item.name_txt, , ",", fv!item.address_txt, , ",", fv!item.dept, ",", fv!item.side, ",", fv!item.city, ) ) ), char(13) & char(10) ) )
You can also remove the a!localVariables from within the a!forEach since local!currentUserIndices is not used anywhere.
Additionally, note the extra commas within the concat after name, address.
Try this alone in the a!forEach expression - for safety you will want to remove commas from within each of the values as well, while they may not be likely, and single one will break your file (since we are no longer wrapping in quotes):
concat( stripwith(fv!item.name_txt,","), ",", stripwith(fv!item.address_txt,","), ",", stripwith(fv!item.dept,","), ",", stripwith(fv!item.side,","), ",", stripwith(fv!item.city","), )
Okay, thanks. let me try with this.
It's not working. Let's make it simple.I have checked when I delete last blank row in CSV file and save it. After that I open that csv file in editor and there were no commas.
I think we just need to remove blank row that is added as a last record in CSV. That's why it shows commas after last record in editor file.