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

Output to CSV remove null value commas

VidyaS
7 - Meteor

Hello,

 

I'm still new to Alteryx and trying to learn. I've a question regard output to CSV format and I need a header to be added along with details in the CSV. So I tried using the Union tool to add these two data sets and output them to CSV, now Alteryx is adding "," to null values in the first header row as there are more columns in the detail data and header has only two columns. Following is the output of the CSV when opened in notepad.

 

03162018,08,,,,
ABC,GFK,PDQ,XYZ,123,345

 

Is is possible to output to CSV without the extra comma's in the first row? For example in the following format.

 

03162018,08
ABC,GFK,PDQ,XYZ,123,345

 

Attached the sample workflow. Let me know if you need more information or have any questions. 

 

Thanks in advance!

 

6 REPLIES 6
CharlieS
17 - Castor
17 - Castor

I don't think your workflow attached appropriately (I was unable to open it), but there's still a few things we can discuss.

 

- In general, if you want to remove a series of characters from the right side of a string, use the TrimRight( function in a Formula tool. This function will remove every sequential occurrence of the character you specify from the right side of the field or value. In this case, TrimRight("03162018,08,,,,",",") will result "03162018,08".

 

- In the Input/Output tools, you can specify the Delimiter used for csv files. You could input your csv file using a different delimiter like "|", edit the commas, then output using comma as the Delimiter again.

 

- You mentioned you're Union-ing these two csv files together, but only need the first 2 columns. When the Union tool is configured to "Auto Config by Name", there is a property below that can be set to "Output Common Subset of Fields". If these options are selected, only the first two columns of these csv inputs that are common will be output form the tool. 

VidyaS
7 - Meteor

Thanks for the response Charlie, I've attached the workflow again. 

 

The delimiter the for the service that needs these CSV export is "," so I am unable to use "|" delimiter.

VidyaS
7 - Meteor

I think for now the solution that works for me is through command line event handler. I can use the MS DOS command to merge the header and details CSV files using the command below.

 

/c copy /b "header.csv" + "detail.csv" "combined.csv"

 

Once the header and detail file ouptut operations are completed in my workflow, I can run this event to merge the two csv files together, which is different from working of Alteryx Union Tool.

 

Thanks for the help.

Namrathabarker
5 - Atom

Hi @vidyasadhanala      ,

 

I have same requirement where I need to remove null value commas in few rows.

Kindly explain in steps how you achieved this.

 

Thanks in advance.

 

 
VidyaS
7 - Meteor

Hello,

 

The scenario I was running into for the workflow was, I had a header row csv called "header.csv" and a detail rows csv called "details.csv". Since the number of columns are not the same in both csv's; Alteryx UNION tool appends "," to the missing columns. So I've just used Run Command tool to execute a DOS command see the post above to merge the two files together. Is this what you are trying to achieve as well? 

 

Hope this helps, else posted an example workflow

Jovial
5 - Atom

Hi guys, I am writing data from a sql query to a txt file with a comma seperated delimiter. Unfortunately the last row has only 3 values namely dateofRun,Sum(price) and count of records and all the other columns are empty. How would i remove the trailing comma from the output file.

Labels