ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Add Header and trailer to CSV file

KrishS
5 - Atom

Hi Team,

 

I am looking for options to add header and trailer to CSV

My module creates CSV and I need fixed length Header and trailer string.

Trailor to be with the count of CSV records.

 

Saw some options like run command tool,But is it possible with other tools? 

 

Thanks,

Krishna

gnans19
11 - Bolide

I would use UNION tool.

 

Construct your header, data and footer separately and union them in the correct order.

KaneG
Alteryx
Alteryx

Also, if the Output is complaining that the records are of different length, then use 'RecordID > Transpose > Summarize > Select' on the main data before unioning.

betorodriguezv
5 - Atom

Bolide,

I need to add a footer but this has different number of columns. Mine is a CSV file. So, when I add the footer, first, it add double-quotes surroinding part of my footer. Then, it adds as many commas to complete the same number of the file.

 

Is there a way to add a row with arbitrary content and without the double-quotes?

 

Thanks,

Alberto Rodriguez

KaneG
Alteryx
Alteryx

Hi @betorodriguezv

 

Try the method that I've mentioned above whereby you effectively construct the CSV before putting the footer on, so that you are only outputting one column.

 

Record ID >Transpose (all columns except Record ID) > Summarize (Group By on Record ID and Concatenate with comma as the delimiter) > Select (Remove Record ID), then attach your footer and output.

betorodriguezv
5 - Atom

@KaneG  Thanks for your reply. The main stream of data is a CSV file. Then I'm using Text Input, Select,  and Sample tools to Union this with the main stream. I tried your suggestion but it didn't work. Could you please take a look on my existing workflow.

 

Thanks in advance,

@betorodriguezv 

KaneG
Alteryx
Alteryx

I couldn't run your workflow as I didn't have the data. Try the attached to see what I mean.

 

I added this part. I also changed the Union slightly

KaneG_0-1620275135395.png

 

betorodriguezv
5 - Atom

@KaneG 

Thank you so much for your support. Your solution worked as expected.

 

Best Regards,

Alberto Rodriguez

DanielG
8 - Asteroid

I did mine a little differently, as I had a footer with 2 data points: the date and the record count.

 

So basically I created those fields with a formula then unioned them to the bottom of the actual data.  


Then because I was stacking a date into a column that was really an 3 digit ID string field, I was able to create a formula that said if the length of the string is less than 4 characters then combine all the columns with pipe delimiters between them.  There are about 10 columns of data.  If the length of string was greater than or equal to 4 then only concatenate the first 2 columns (which would be the date and record count), which eliminates this issue:

 

2021-01-01|65|||||||

 

Then for the headers I just have a text file with the column headers all laid out with the delimiter in there, so it gets unioned on after that and I have it all in the first column.

 

So then I deselect all the data except the one newly constructed column of concatenated data. Output as CSV and you have a perfectly delimited csv file.  

 

Labels