Hello,
Is it possible to trim only the first and the last record of a CSV output and delete all the other commas and keep delimiters?
Please find the screenshots for reference.
@shilpamehta1991 wrote: Thanks @Pratham @DavidP for your solutions.So I have got rid of the trailing commas in the first and the last records however while outputting the records to csv the entire record is now coming under a quote. See below for reference.I suspect when I will upload this file to any other system the entire record will only be considered as 1 field, which is not what I expect. Is there any way these quotes can be removed, so my fields aren't treated as single field?
Thanks @Pratham @DavidP for your solutions.
So I have got rid of the trailing commas in the first and the last records however while outputting the records to csv the entire record is now coming under a quote. See below for reference.
I suspect when I will upload this file to any other system the entire record will only be considered as 1 field, which is not what I expect. Is there any way these quotes can be removed, so my fields aren't treated as single field?
Well in the Output tool configuration of the CSV file, Alteryx does allow you to unquote your output fields, provided the delimiter you choose for your output file is not present in the records. This clearly not possible in your case, since you have the delimiter in the records.
So a simple workaround for this is to change the delimiter of your output file, let's say to a pipe character "|", just for the Output tool's sake and have the tool output your file with the "Quote output fields" option set to "Never".
Your output file still gets to be a comma-separated CSV file.
Have a look at the configuration and output below:
Hi @shilpamehta1991 , @Pratham
As opposed to specifying an artificial pipe "|" character as the field delimiter, Alteryx allows you to specify no delimiter "\0" in the output configuration.
Dan
I'd suggest configuring your input tool to load the data with no delimiter \0 option, uncheck First row contains Field Names and set Field length to a large enough value.
The use a filter tool to remove the 1st and last line with a formula like this:
!Contains([Field1],'HDR,E,1234567') AND !Contains([Field1],'TRL,E,1234567')
Please clarify the last part of your question.
I mean I want to output a csv in which the first and the last record should not have additional , at the end. I want to trim those commas only for the first and last record.
Please see the 2 screenshots. one is my current output with the , in the end and the other is my expectation without comma at the end.
Ah ok, now I get you.
Ok, so load the data as described into a single field with no parsing (\0). The use a formula tool with the following formula.
Let me state my requirement again.
I am outputting a csv which currently looks like below-
However I need the csv to not have additional comma in the first and the last record. Please refer below-
Is there some way this can be done?
Ok, perhaps I was over complicating it.
The key thing is that your data has to be in a single column as shown in the attached example. You can then get rid of the trailing commas with a trimright formula. This will remove trailing commas from ALL rows, unless you have another way to identify top and bottom records.
You can then use an Output Data tool, CSV format with no delimiter (\0) to write your output file.
If, however, your dataset is in multiple columns, you have to get it into a single column first for this to work.
No but I need to keep all the trailing commas in all my other records except the first and the last.
Is that possible?
