In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

CSV File Date Format Issue

SowmyaSrinivasan
5 - Atom

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.

11 REPLIES 11
Luke_C
17 - Castor
17 - Castor

@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
17 - Castor
17 - Castor

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:

IraWatt_0-1660672551105.png

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
5 - Atom

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
17 - Castor
17 - Castor

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

IraWatt_0-1660673618623.png

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
17 - Castor
17 - Castor

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
5 - Atom

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

IraWatt
17 - Castor
17 - Castor

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

IraWatt_0-1660674466343.png

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
17 - Castor
17 - Castor

@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
17 - Castor
17 - Castor

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

IraWatt_0-1660680161767.png

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?

 

Labels
Top Solution Authors