Parse date when months are not in english

Sergio_Gonzalez · September 2020

Hi, I have a file where the dates are in the below format:

01-ago.-20

08-nov.-20

As you can see the month is in Spanish abbreviation, the code that I trying to make work separates the field in dd and MMM, then I used nested if in order to change the MMM to English, at the end I just concatenate dd + MMM + '2020', this is the code I'm using:

a = LEFT(csv['Periodo inicial campaña'],2);

b= IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='ene','Jan',
IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='feb','Feb',
   IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='mar','Mar',
IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='abr','Apr',
   IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='may','May',
IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='jun','Jun',
   IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='jul','Jul',
IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='ago','Aug',
   IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='sep','Sep',
IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='oct','Oct',
   IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='nov','Nov',
IF(LEFT(EXTRACT(EXTRACT(csv['Periodo inicial campaña'],'-',1),'.-',0),3)=='dic','Dec',
   null
  ))))))))))));

c = '2020';

d=a+b+c;

PARSEDATE(d,'ddMMMyyyy')

csv['Periodo inicial campaña'] is the field containing the date; while in the mapping I test it all is working good, but when I try to save the Data Stream I get this error message:

Error while evaluating formula {My code} for field 'date_id': CompilationException - PARSEDATE(d,'ddMMMyyyy'): Unparseable date: "Monull2020" (at line 1, column 1). The input values to the formula were: {Periodo inicial campaña=Mon Aug 17 00:00:00 UTC 2020}

Any help will be appreciated,
Thanks in advance.

tula · September 2020

Hi Sergio,

In the message error that you are getting you can see that Datorama is not reading the date in the format 17-ago.-20 but in the following format: Mon Aug 17 00:00:00 UTC 2020

The input values to the formula were: {Periodo inicial campaña=Mon Aug 17 00:00:00 UTC 2020}

That's why the output looks like this: Monull2020

So you probably don't need any formula and just map directly the field csv['Periodo inicial campaña']

Hope you find this helpful!

Parse date when months are not in english

Answers