Can't export to CSV files

[AkisM]

Whenever I try a CSV output, the file comes out looking like this, all mushed up in the same column:

Place,Age,Address,Description,ZIP

My understanding is that if you set comma as the delimiter (which I have), there will be separated columns. Anyone knows what the issue is? I tried using dot as a delimiter too just in case, and it's the same issue (mushed up in 1 column), but instead it looks like this

Place.Age.Address.Description.ZIP

[AkisM]

Hi @danilang . This is the process:

My workflow>Output tool, specifying location and CSV file>I run workflow> I double click the newly created csv file>It opens in excel automatically, but columns are mashed up together in 1 column.

No in between opening with text editor or GetData or anything fancy.

Here's the file that is directly created by Alteryx, which to me at least, opens as a single column file:

I will also attach a screenshot of what I actually see when I open the file in excel by simply double clicking it.

[AkisM]

Pasting what I see in a separate reply since whenever I tried to upload a 2nd file the 1st one was replaced

[Jonathan-Sherman]

That is a weird one, could you attach an image of your output configuration window? Also could you try saving as an .xlsx to see if you get the same result?

[danilang]

Hi @AkisM 

Are you in a location that uses "," (comma) as a decimal separator?  "999 999,99"   It looks like your issue may be related to this.  My regional settings (Canada) are this "999,999.00" 

when I double click on your csv file, it opens in multiple columns.  

When I change my settings to this "999 999,00"

double-clicking on the file opens it with the data all in one column.

This seems to be source of the problem, but I haven't been able to find a way to configure Excel to get around this.

If I use Data-> From Text/CSV

It opens the import wizard that properly parses the file.

Maybe your IT department has a workaround for this Excel issue.

Dan

[AkisM]

Hi @Jonathan-Sherman , attaching below screenshot of my export tool settings.

Also, I'm not sure I understand exactly what you mean by saving it as xlsx. Do you mean to open the jumbled up csv and save it again as xlsx or do you mean to use a different output tool in alteryx and output directly to excel? If the former, yes, even after saving again as XLSX, it's still all stuck in 1 column. If you mean the latter, exporting to XLSX directly from alteryx works as intended. I don't have any issues with that.

[AkisM]

@danilang Yes indeed I am from a region that uses comma as decimal separator. Unfortunately I can't change this because I specifically need amounts to be separated by comma at their decimal point, not a dot.

I was wondering if there was a way to fix the csv file directly from inside Alteryx (to minimize human interaction) without having to change regional settings that affect my amounts format.

[Jonathan-Sherman]

I meant the latter, using a different output tool. I would have to agree with @danilang with the problem being using a coma as a decimal separator as there is no issues when outputting as an xlsx. Is there a reason why you specifically need to use a csv?

[danilang · Accepted solution]

Hi @AkisM 

It looks like there are 2 ways to modify the CSV, both with drawbacks

The 1st is to use semi-colon ";" as the delimiter.  This is the default that excel looks for when a comma is used as a decimal separator.  This opens properly with "999 999,99" but opens in one column if your settings are "999,999.99" 

The 2nd is to add a an extra first row with the text "sep=," as in   

"sep=,"
Bank Account,B0008,,2018-01-14,,302,random.descrp@hotmail.com,,,EUR,33.99,Customer,C0004

adding the extra line, automatically makes the file Excel specific.  The csv format is now broken as far other programs, including Alteryx, are concerned causing errors on input 

The choice is up to you.

Of course a 3rd option is to follow @Jonathan-Sherman's sage advice and save to .xlsx if you can. 

Dan

[AkisM]

Thanks to all for your contribution and thanks @danilang , simply changing delimiter to semicolon fixed the issue.