topic Re: Can't export to CSV files in Alteryx Designer Desktop Discussions

Can't export to CSV files

AkisM — Wed, 02 Oct 2019 07:18:02 GMT

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

Re: Can't export to CSV files

LukeM — Wed, 02 Oct 2019 07:47:48 GMT

Hi @AkisM

Have you checked the other settings in the output tool - this will affect you the CSV file is generated.

They should be like this, for reading in excel:

I assume the issue is when you are opening the csv in excel? This could also be an issue with how excel is configured to read excels.

Let me know if those settings help.

Luke

Re: Can't export to CSV files

RolandSchubert — Wed, 02 Oct 2019 07:50:09 GMT

Depending on your configuration, seperator in Excel maybe ";". Did you try that?

Re: Can't export to CSV files

AkisM — Wed, 02 Oct 2019 08:33:24 GMT

Hi @LukeM , those are indeed the settings I have on my output tool. The issue is indeed when opening the csv in excel. They still appear all squashed in the same column.

Re: Can't export to CSV files

AkisM — Wed, 02 Oct 2019 08:37:05 GMT

Hi @RolandSchubert , in my advanced excel options there is a "Use System Separators" option that is checked. If I uncheck it, all I can edit is decimal separator and thousands separator. I don't see anywhere an option to find delimiter for CSVs in general.

Re: Can't export to CSV files

RolandSchubert — Wed, 02 Oct 2019 08:55:29 GMT

Sepeators in advanced options are the thousands and decimal seperators to be used in numbers within Excel.

To change the seperator for csv files

- call Control Panel

- select "Clock, Language and Region"

- select "Region"

- select "Additional Settings"

- there is an entry "List Seperator"

This is the one used for import of csv files to Excel

Re: Can't export to CSV files

AkisM — Wed, 02 Oct 2019 09:03:21 GMT

Hi @RolandSchubert, tried checking that, however List Separator is already set to comma.

Re: Can't export to CSV files

RolandSchubert — Wed, 02 Oct 2019 10:23:48 GMT

I have no real clue, but one (maybe last) idea: Do you open the file using "Get data" in Excel? The default column seperator for that is a TAB, but can be changed during the import process.

Re: Can't export to CSV files

AkisM — Wed, 02 Oct 2019 10:44:06 GMT

@RolandSchubert I have no idea what opening a file using GetData means. I just double click the file and it opens in excel.

Re: Can't export to CSV files

danilang — Wed, 02 Oct 2019 10:53:10 GMT

Hi @AkisM

When you say "Whenever I try a CSV output, the file comes out looking like this, all mushed up in the same column:" what program are you using to look at the data? It it's a text editor, like notepad, then you're seeing the way that the file is actually stored.i.e.

Place,Age,Address,Description,ZIP
Ottawa,55,123 Any St.,something, 12345

If you're using Excel using Data->From CSV, Excel should be smart enough to figure out the delimiters, like this

If you're double clicking on the file and it opens in excel, or you're using "Right Click-> open with-> Excel" or you drag it on to excel, you should get the file split to columns as well.

The exception to this seems to be if the file doesn't have a CSV extension, i.e. "sample.txt". In this case opening it in Excel gives you all the data in one column

Can you provide us with a sample of one of your files?

Dan

Re: Can't export to CSV files

AkisM — Wed, 02 Oct 2019 11:37:19 GMT

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.

Re: Can't export to CSV files

AkisM — Wed, 02 Oct 2019 11:38:15 GMT

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

Re: Can't export to CSV files

Jonathan-Sherman — Wed, 02 Oct 2019 12:36:10 GMT

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?

Re: Can't export to CSV files

danilang — Wed, 02 Oct 2019 12:36:40 GMT

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

Re: Can't export to CSV files

AkisM — Wed, 02 Oct 2019 12:46:09 GMT

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.

Re: Can't export to CSV files

AkisM — Wed, 02 Oct 2019 12:50:29 GMT

@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.

Re: Can't export to CSV files

Jonathan-Sherman — Wed, 02 Oct 2019 12:57:28 GMT

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?

Re: Can't export to CSV files

danilang — Wed, 02 Oct 2019 13:07:45 GMT

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

Re: Can't export to CSV files

AkisM — Wed, 02 Oct 2019 13:10:17 GMT

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