Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Trim CSV output

shilpamehta1991
6 - Meteoroid

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.

 

Expected_Output.PNGCurrent_Output.PNG

14 REPLIES 14
DavidP
17 - Castor
17 - Castor

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.

 

load csv.png

 

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.

shilpamehta1991
6 - Meteoroid

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.

 

Current_Output.PNGExpected_Output.PNG 

DavidP
17 - Castor
17 - Castor

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.

 

regex commas.png

shilpamehta1991
6 - Meteoroid

Let me state my requirement again.

 

I am outputting a csv which currently looks like below-

Current_Output.PNG

 

However I need the csv to not have additional comma in the first and the last record.  Please refer below-

Expected_Output.PNG

 

Is there some way this can be done?

Pratham
6 - Meteoroid

Okay, maybe not the most optimal way to do it, but does the attached WF solve this?

DavidP
17 - Castor
17 - Castor

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.

 

trailing commas.png

 

TRIMRIGHT commas.png

 

If, however, your dataset is in multiple columns, you have to get it into a single column first for this to work.

shilpamehta1991
6 - Meteoroid

No but I need to keep all the trailing commas in all my other records except the first and the last.

Is that possible?

DavidP
17 - Castor
17 - Castor

As I said, you then have to find another way to identify the 1st and last row and put it in an if statement, for instance, something like:

 

if StartsWith([Field1], 'HDR') or StartsWith([Field1], 'TRL') then TrimRight([Field1],',') else [Field1] endif

Pratham
6 - Meteoroid

@shilpamehta1991 wrote:

No but I need to keep all the trailing commas in all my other records except the first and the last.

Is that possible?



Have a look at my earlier post that retains the trailing commas except in the first and last record.

Labels