CSV File Date Format Issue

[SowmyaSrinivasan]

I am trying to read a CSV file that has date in custom format "DD-MMM". How to bring proper date format in alteryx? I have more files so it is impossible for me to change in excel. Please help. Attaching an image of what is the issue.

[Luke_C]

@SowmyaSrinivasan 

Can you share a sample csv file? If you open the data in notepad does it appear the same way? You can use the Date Time tool or DateTimeParse function in a formula tool to parse the data. It would look something like this for 'DD-MMM'. The issue will be the year. Is the data all the same year?

DateTimeParse([Field1],'%d-%B')

[IraWatt]

Hey @SowmyaSrinivasan,

CSV's will always be read in as Text data. That means you will have to use the datetime tool to parse your dates into a date data type. This set up should work for you:

The formula tool just adds on the current date year.

The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20...

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[SowmyaSrinivasan]

The data is not for same year. We have different years. I need to check the year based on the alteryx output only. Attaching a sample excel file. I have 10000 files that needs to be processed.

[IraWatt]

@SowmyaSrinivasan the only way I can see to get round this is writing some code in Alteryx to loop through each CSV and covert them to an Excel file and then importing the data.

This is because the Excel formatting overwrites the CSV making it impossible as far as I know to get the full dates back without converting the files back to Excel.

[Luke_C]

Hi @SowmyaSrinivasan 

I believe Excel just assigns the dates to current year, so I'm not sure how you would derive the year unless there's a field in the data to leverage. I didn't see one though. You could check by opening some other csvs and seeing if they're all 2022. 

[SowmyaSrinivasan]

Hello, Do I need to do something like the attached workflow? Please help.

[IraWatt]

@SowmyaSrinivasan Alteryx will open the CSV as text so that wont work. 

I think a python tool opening the CSV in Excel then saving it as an Excel file could maybe work but I would not know where to start with that. 

[Luke_C]

@SowmyaSrinivasan @IraWatt 

Unless I'm missing something even opening them all in excel would just populate the current year on each date. I don't think there's any way to know what year is associated with it based on the data provided.

[IraWatt]

@Luke_C If you open the CSV file in Excel you will see that formatting is being applied which removes the date vales in the CSV:

Only way I could think of correcting this for Alteryx is to save the CSV as an .xlsx file then alteryx could process the formatting?