Input Data Tool not picking up one of my columns

[Greg2186]

So I'm dealing with a pretty ugly Excel file with a lot of NULL rows and columns. There's a job that is dropping this file weekly and I'm trying to automate the process of scrubbing the file into a .tde.

My issue is that for one of my fields (the most important one of course) the input tool returns a NULL for all values including the header. Has anyone run into this?

The Excel file and my Alteryx Workflow has been attached (dummy data), the field that I'm having issues picking up is "Completion Status". Believe it or not the file comes in as expected via Tableau, but I want the capability of bringing in the new files once they're dropped into my file location.

[patrick_digan · Accepted solution]

@Greg2186 Does the size/dimensions of your file change? If the data always starts at line 12, you could use the attached. Instead of grabbing the whole sheet, I've found that limiting the data to certain ranges can be a workaround.  From the input tool, I changed it to pick up just row 12 on:

SELECT * FROM `Sheet1$A12:Q65536`

That seems to resolve the issue. This is definitely more of a workaround than a fix.

[Greg2186]

Thanks @patrick_digan.

That worked. The size/dimenstions of the file do not change from week to week. They are very consistent in format. I'm thinking that the workaround should work.

I didn't realize you could write Excel SQL :)

[JordanB]

Hi @Greg2186

I believe the reason is because the Completion Status field is being read in as a date field within Alteryx because of the first value in the competition status is a Date format.

Although the data type changes in excel when you get to completion status, Alteryx will take the first data type it finds in this field. I tested this theory by saving the .xls as a .csv and Alteryx read that is correctly (All fields will be V_String). 

Potential Options:

- Convert xls files to a csv

- Read file in with a specified cell range (Skip the date field and Alteryx will read the data in correctly). You can do this in the SQL editor tab.

Unfortunately this is an .xls file otherwise we now how in-built function to read from a certain row.

Best,

Jordan Barker

Solutions Consultant