Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to force a format from an odd Excel input?

pierrelouisbescond
8 - Asteroid

Dear Alteryx fellows,

 

I am currently helping a colleague to consolidate a large number of Excel files coming from different world regions into a single one (it was taking hours manually… and now only a few seconds with Alteryx with the proper workflow :-) ).

 

The single issue I have is that one of these files is generated with the wrong Excel format for some columns. This means, for ex. that, instead of displaying “2018” it shows “10-Jul-05” in Excel ("dd-mmm-yy" used instead of the "standard" format).

 

Hereunder, columns #1 and #2 are in the wrong format; column #3 is fine:

 

screenshot.2019-01-22 (6).png

 

I have no possibility of changing the Excel format so I was wondering if there was a way to force the format in Alteryx?

 

For some columns (#2 and #3), Alteryx does interpret properly the numbers but column #1 is still wrong as it should display “2018”:

 

screenshot.2019-01-22 (4).png

 

 

 

 

 

 

 

 

 

 

 

And, quite strangely, after more than 300 lines, the wrong format is displayed everywhere:

 

screenshot.2019-01-22 (7).png

 

To go back to normal for columns #2 and #3 around lines:

 

screenshot.2019-01-22 (8).png

 

To back to normal for columns 2 and 3 around line 483:

 

screenshot.2019-01-22 (8).png

 

I am attaching the Excel file for those who will be kind enough to have a look at it and the interpretation from Alteryx.

 

Thanks!
PLB

6 REPLIES 6
mmenth
11 - Bolide

Hi @pierrelouisbescond,

 

Are the dates wrong in the original files you are feeding into Alteryx or are they being converted to the incorrect dates somewhere in the Alteryx workflow?

 

Best,

mmenth

estherb47
15 - Aurora
15 - Aurora

Hi @pierrelouisbescond,

 

Would love to take a stab at this, but the Excel file is linking to data that can't be retrieved outside of your computer. Would you please break all links and just post with the data itself?

 

That said, I wonder if it's the linkeage that is causing the problems with format.

 

Best,

Esther

pierrelouisbescond
8 - Asteroid

Hi EstherB47!

 

I have converted all links into values... and tested it again in Alteryx and the results are the same.

 

Hoping you can tell me what would be the proper way to fix this :-)

 

Pierre-Louis

pierrelouisbescond
8 - Asteroid

Hi mmenth,

The values contained in the Excel cells are right but the format is wrong.

There is no specific worklflow in Alteryx, just the data entry.

Thanks,

PL

estherb47
15 - Aurora
15 - Aurora

Ah, the joys of Excel and serial dates.

 

@pierrelouisbescond, the first column needs to be turned back into the number it represents, which can be done with a DateDiff function (little bits of conversion in the formula tool to turn the text into a date, and then figure out the number of days between that date and 12/30/1899)

 

DateTimeDiff(DateTimeParse([Annee_Validation],"%Y-%m-%d"),"1899-12-30","days")

For the month column, created an IIF statement to see if the field was a date or just the number. If a date, a touch of manipulation to make it a real date, and then pull the month out.

 

ToNumber(IIF(REGEX_Match([Mois_Validation], "\d{4}-\d{2}-\d{2}"),  DatetimeFormat(DateTimeParse([Mois_Validation],"%Y-%m-%d"),"%m"), [Mois_Validation]))

 

Please let me know if anything needs further explanation.

Cheers!

Esther

 

 

pierrelouisbescond
8 - Asteroid

@EstherB47

 

This works perfectly!

Thanks a lot for guiding me through the dark side of the Excel formats... and reg expressions ^^

Labels