Datetime format changes when connecting to alteryx

[OrlanViloria]

Hi, I am struggling with the time format of a field. I have a .csv file with a field with datetime information 

For example, 

However, when I connect it to alteryx it looks like this 

Do you know why is this happening? 

Thank you!

[OrlanViloria]

I am referring to :00 PM that is missing

[estherb47]

Hi @OrlanViloria 

On initial glance, it could be a field length issue. Not sure if this will work, but drop a Select tool after the input, and change the length of that field to longer.

Let me know if that works.

Cheers!

Esther

[OrlanViloria]

Hi! I am trying with this configuration 

However, it did not fixed the problem

[grazitti_sapna]

Hi @OrlanViloria , could you possibly share the sample workflow to figure it out? I tried creating one but gives correct output. Please refer to the screenshot. 

Note: While creating the csv file I converted the column to text.

[OrlanViloria]

Hi! The thing with my dataset is that I am extracting it from salesforce as .csv and thus using the default format to connect it in alteryx. I am not using excel to prepare data! Is there a way to solve it from alteryx?

Thank you

[jarrod]

@OrlanViloria what do you see for that field if you open the csv in a text editor? i wonder if excel is actually inferring the :00 PM when you open it. 

[OrlanViloria]

Hi, 

It has this format

The reason I want to be able to add the :00 is because without those zeros alteryx is not being able to convert it to datetime format with the datetime tool 

There you can see I am getting a null output.

Thank you! I would appreciate any advise with that.

[jarrod · Accepted solution]

Simplest way I can think of is regex parse:

(\d{1,2}/\d{1,2}/\d{4})\s(\d{2}:\d{2})(:\d{2}:)*\s([a-z]{2})

That should split it up into the major pieces and you can add in the :00 to the second group.