How to deal with Header truncation

[domlow]

Hi there, I'm importing an xls file into designer and noticed that the first cell (A1) is being truncated after it's import as a header.

The issue is resolved when the same file is saved as an xlsx.

Was wondering if there's a way to prevent the truncation when using the file in xls format?

I'm trying to use the test tool to test if the first cell contains the word "Final" but due to the truncation its not working. 

[mceleavey]

Hi @domlow ,

can you post your data so we can look at this for you?

M.

[domlow]

Adding the recreated data sets saved in xls and xlsx respectively.

[domlow]

@mceleavey attached

[mceleavey · Accepted solution]

Hi @domlow ,

Unfortunately this is a limitation of the field names in the old .xls version of Excel (uncompressed).

The only way around this is to load this in without headers, treating the first row as data as this will not be truncated, then dynamically renaming from the first row:

Hope this helps.

M.

[domlow]

Hey M

Thanks for this, was wondering any way to select "first row contains data" while using a dynamic input?

I'm actually using a formula tool before the dynamic input to dynamically change the YYYYMM of the file path to only extract the file a month prior to today()

Otherwise a great workaround!

[domlow]

Found this

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/First-Row-Contains-Data/td-p/596447 

Solved!

[mceleavey]

Hi @domlow ,

Use the directory tool to read the files in from that location, then use regex to pull the date from the filename. Then create a formula as follows:

datetimediff([<today>,<date from filename>,"months") which will give you the number of months before today. Filter to the ones you need.

In the Dynamic input tool, select the template you wish to use and edit the template input to have the same selection in the first row contains data box.

That should work.

M.

[danilang]

Hi @domlow 

Even when using @mceleavey's solution, you might still run across an undocumented(as far as I can find) 255 char field name limitation within Alteryx itself.  The attached workflow has a field with 567 chars in it. After the Dynamic Rename, only the 1st 255 chars are used

Dan

[domlow]

Thanks @danilang 

Noted on this header limitation, currently, the headers are well under the character count (it is approx 60 characters). 

Will keep this in mind 🙂