Input interpretation error with .xlsx file

[Joma]

Hi all,

i am having trouble with my input file. I have a .xlsx file where i import a bunch of data. Some columns are completely misinterpreted by the tool. For example a column with text is directly interpreted as time column (00:00:00) or a column with date all values are interpreted as 1900-01-10. The input date has following type MM/dd/YYYY.

I know that Alteryx only use one date format and i need to change it with datetimepase but as already all values were changed i need to something before as it already shows in the preview of the input tool the wrong data.

I would be happy if somebody can help me.

[grossal]

Hi @Joma,

can you provide a sample of the file? 

On a note, you are right now in the German Forum, so feel free to speak German 😉

Best

Alex

[Joma]

Hi @grossal,

thanks for the hint. I didn´t realize it, but i will stay in english for now as i already started in english 🙂

I just tried to extract a sample of it and i realized the problem is not occuring if i copy and paste the values of the data in a new file. Nevertheless it is not an option as i need to stick to the original file. 

The structure of my file is as followed: In 1 tab all relevant information of the other tabs are collected via if- formula.

Thats how my input tool shows my input

 

How it looks in Excel

Could it be possible that the reason is because of the formating of the xlsx. file?

Best,

Joma

[grossal · Accepted solution]

Hi @Joma,

sorry for the last response. My biggest issue right now is that I can't replicate it.

The formatting is might responsible for the issue. It's possible to format a number like 10 as a date and people wouldn't be able to tell the difference between a real date and an integer.

I'd maybe try these things:

1) Import with Headers as rows:

Every columns now starts with a text and Alteryx will try to interpret it as Strings. If the strings are right, you can use the Dynamic Rename and Auto-Field / Select tool to format the rest.

2) Export Excel to CSV and Import CSV

It's probably a nasty solution, but I'd give it a try. 

3) Try to trick Alteryx

We can also try to use the Python tool and just read in the Excel via Pandas and than pass it through to Alteryx, but I am not quite sure if the result is different, but the code is quite simple:

Here is the Code for copy-paste:

from ayx import Alteryx
import pandas as pd

file_path = "C:\_Data\Alteryx\Community\data.xlsx"
data = pd.read_excel(file_path)

Alteryx.write(data,1)

I hope something works. Let me know!

Best

Alex

[StephV]

Hallo @Joma, 

war diese Antwort von @grossal für Sie hilfreich?

Wenn Ihnen die Antwort geholfen hat, können Sie diese als „Lösung akzeptieren“. Sie wird sicherlich auch anderen Benutzern helfen! So können auch andere in der Community nützliche Antworten finden.

Danke!​​​​​​​

[Joma]

Hi Alex,

sorry for my late response. 

I figured out that one main problem is because of a simple reason. The respective columns in the excel sheet are formated as date column. If i change it to a standard column in excel it can be recognized as string and afterwards it is possible to transform to alteryx date. But i would love to avoid that.

I tried your ideas:

The first solution is a good idea, but didn´t work as expected, because the string changes the format to 00:00:00, so all values are "gone" again.

The second idea is not really an option as some users work with the xlsx.files already and they will upload a new version everyday.

The python idea looks promising so far and seems to work 🙂

Thanks for your help Alex. 

Best

[StephV]

Vielen Dank für Ihre Frage @Joma und an @grossal für Deine Hilfe und guten Ratschläge!