Outputting to CSV File - need to eliminate the last comma

[to8nbeyond]

I'm working on an creating a template for a software product we use. The first line of data needs to be 6 columns, the data needs to be 7 columns. When I output the finished file to CSV, the header has an extra comma and the file will not import. I'm trying to find a way where that null value in the header line will be excluded when writing to a CSV file. At the moment, I have been editing each file to remove the comma manually. There has to be an easier way.

This is what I get on output.

This is the output right before writing the .CSV file

Here are the settings for the output tool.

Any help to eliminate the very last comma in the first line would be greatly appreciated. 

[grossal]

Hello @to8nbeyond,

first off, I am kinda curious what product you are using. Alteryx is outputting the normal way a CSV is done and I'm surprised that some product is doing its own thing.

Anyhow. How can we work around it?

My mind switched between the following three ways:

Macro

We could build a macro that does: 

1) Export

2) Reimport with \0 delimiter (every row will be put into one cell)

3) Remove comma from first line

4) Export again with \0 delimiter

It sort of stupid, but it would work. To ensure that the export is finished before the loading starts, I'd pack this in multiple macros.

Run Command / Python

Alternatively I think we could also use something like a Run Command tool or Python-Tool to manipulate the data after it is written. If you are familiar with one of them, then I'd probably recommend to go that route instead of building the macro way.

Fake CSV build

In the macro solution, we already used the possibility to export with a \0 delimiter. We could use this mechanic in another scenario. If we built ourselves the same, than we could also use this option. It could look something like this:

The top part builds our data rows. Our goal is it to achieve exactly the format you have in your 2nd to n line of the CSV. Texts shall be surrounded with "" and numbers are shown normally (in your case, numbers seem to be texts). Afterwards we use a RecordID tool to know which data belongs to each other, transpose it and than concat it.

The process for the column names is similar, we grab the column names using the Field Info tool, remove the empty one and than concat it. 

The Union tool is set to a fixed order to ensure we have our column names beforehand. Afterwards it looks like this:

In the output tool we need to make sure, that not only the Delimiter is set to \0, but also the column name ('concat') is not written to the output (see highlighted options).

The final CSV looks like this:

I've attached the little sample workflow that I used for the last case. You'll probably need to adapt it slightly to fit your case, but it should be a good reference. Let me know what you think.

Best

Alex

[AkimasaKajitani]

Hi @to8nbeyond 

it needs some tricks because In this case the csv that you need is special csv file that it means not normal csv.

In this case, we needs add the delimiter and quote to the data in advance.

And we should set the output data tool below.

The most important point is "Delimiters" option. It should be set "\0" if we set the "Never" to the "Quote output Fields".

Because if we set the "Never" to the "Quote output Fields" and set space or any other character to "Delimiters", Designer shows error. 

Please try it and if you have any questions, let me know.

[to8nbeyond]

Thank you for your suggestions. Using the info for outputting to csv files with the \0 & never option, I was able to string together the required fields using an if statement within a formula tool and then output the new column. This seems to work. 

Thanks for your help.

[to8nbeyond]

I'm using Thompson Reuters OneSource Income Tax. They are very specific about their import templates.