Prevent Alteryx from changing data when loading excel sheet

I am trying to input this excel sheet attached below into Alteryx. Some values in the column "Last Week / Weeks on List" are getting converted into a date format when loaded in Alteryx. Please see attachments below. I need it to remain in the same format so I can split per delimiter before analysis. Thanks

Help

Accepted answers

All comments

Qiu

@Uchiha_Itachi
Can you force column "C" to be Text type data in Excel?

apathetichell

This is weird. Can you click on the cell which says 1/8 in Excel? Is the underlying data in excel a formatted date?

Uchiha_Itachi

It didn't work. I had resave the whole excel sheet as a csv and then uploaded the sheet into Alteryx as a csv file instead of an excel file. For it to work. There must be simpler process. But that is too long a process. Thank you by the way. I tinkered with it for a while.

Uchiha_Itachi

Very weird. Yes the "1/8" format in excel is under custom as "m/d" which I'm guessing is month/day. When I try to change it, it auto populates as a whole weird number ("43108"). Saving the whole sheet as a csv solves this problem but having to save an excel as a csv before inputing into Alteryx is an annoying process. Let me see if I can upload the excel sheet as a csv directly into Alteryx

apathetichell

I understand your frustation - but Alteryx is correctly reading date values from excel and translating those to dates .You have 1/8 set up as a date in excel. you have an underlying number of 43108 set up there. That is days in 12-30-1899 (really 12-31-1899 including the day) - which when calculated becomes... 2018-01-08. Your issue is in the field types of your excel data - not in how Alteryx is reading it. I have no idea why your excel data is so weird.

jdminton

I agree with @apathetichell . You can use =IF(ISNUMBER(A2),TEXT(A2,"m/d"),A2) in another column to convert the formatting to text for all records or find a way to import the data as text to begin with.

Uchiha_Itachi

I absolutely understand you. Thank you for the feedback. The problem with preprocessing data /ETL is the fact that we most of the time cannot or do not control how the data is collected.

I would have preferred to able to fix this issue in Alteryx without going back to excel. Regardless of the field type in excel the data still remains 1/8 in the cell. But In Alteryx the data type isn't coerced to datetime but rather a V_String so it shouldn't convert to 2018-01-03 if the point is how Alteryx read in the data.

I was primarily interested in determining whether Alteryx offered a solution to this seemingly uncommon issue.

Uchiha_Itachi

This worked thanks. Its another option to have without having to resave the whole excel document as a csv, which was the option that worked for me initially.

jdminton

I'm just curious, but is the data coming to you as an Excel workbook? Since Excel is the program formatting the field as a date, I'm wondering if someone is opening the file first. I've had issues with files once they're opened in Excel where there isn't an issue if I open them in Alteryx first.

Uchiha_Itachi

That's a very good point. I does happen something. I. might have to go back to the original source and redownload just to be sure. Thanks.

jdminton

@Uchiha_Itachi Alteryx can make it work, but you would need to identify the values that have been set as date format and use a formula tool to essentially undo. I.e. you can Regex match to identify \d{4}-\d\d-\d\d in the field and convert that. See attached.

Dates Issue.yxzp

Uchiha_Itachi

@jdminton I haven't tried that yet. Will do and revert. Thanks

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703