Date field type of CSV exported by Alteryx not correctly recognized by 3rd party programs

[AkisM]

I have a workflow with a number of fields which include, strings, integers, and dates. I make sure all fields are set to the correct type before the export (I'm not sure whether this actually affects anything, as I know csv files only show string formats in the end).

The 3rd party app has forced field types that the input file must adhere to. When I import that file, the 3rd app throws out an error that the field containing my dates cannot be evaluated into "date" type. This is because it takes the month as a day and the day as a month. 

Now, the WEIRD thing, which might give a clue as to what's happening (but I don't get it), is if you open that csv, save it again without making any changes to it, and try importing it again, it works. So something happens when you re-save it with excel, even though 0 changes were made, that makes it work. Any idea what this could be? Something I could try changing in alteryx to make it work without having to re-save manually with excel?

Though typing this out I think that I may have realized the issue myself. CSVs by nature are always string fields even if they had field types in alteryx. And since the 3rd party program expects dates, it tries to evaluate it into date but due to its regional setting picks the wrong digit as date/month. The reason why this doesn't happen when re-saved by excel is that excel's auto-field recognition correctly applies to date, and re-saving the CSV adds field types to the file. Correct me if I'm wrong. If that's what's really happening then is there a way to instruct alteryx to keep field types when exporting to csv?

[RolandSchubert · Accepted solution]

Hi @AkisM ,

I think, it's not a problem of field types, as all fields are basically strings within csv files. If you export a date field using Alteryx, a string "YYYY-MM-DD" is created. The 3rd party app seems to expect a different date format (e.g. DD.MM.YYYY), so you should convert the date field to that format before export insteadt of setting it to date field type. You can use a formula to do that:
DateTimeFormat([YourDateField], '%d.%m.%Y')

Result will be a string field (e.g. date represented as 17.12.2019) you can use for export. What do you think?

Best,

Roland

[AkisM]

Hi @RolandSchubert , you're right, tried a few different date formats (as string field type) with regex replacement and it worked like a charm. I guess what confused me is that when i was resaving the file in excel to make it useable, and I opened it again to see what's different, everything seemed the same. But I should've considered at least changing the order of the digits since csv's are always strings anyway. Thanks for the help.