Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
RÉSOLU

Can't export to CSV files

AkisM
Boule de feu

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

18 RÉPONSES 18
AkisM
Boule de feu

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
Boule de feu

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

Jonathan-Sherman
15 - Aurora
15 - Aurora

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
19 - Altair
19 - Altair

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" 

Regional(CDN).png

 

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

 

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

 

Regional(EUR).png

 

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

 

fromCSV.png

 

It opens the import wizard that properly parses the file.

 

Import wizard.png

 

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

 

Dan

AkisM
Boule de feu

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
Boule de feu

@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
15 - Aurora
15 - Aurora

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
19 - Altair
19 - Altair

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 

 

Input error.png

 

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
Boule de feu

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

Étiquettes
Auteurs des meilleures solutions