Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to deal with Header truncation

domlow
7 - Meteor

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. 

9 REPLIES 9
mceleavey
17 - Castor
17 - Castor

Hi @domlow ,

 

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

 

M.



Bulien

domlow
7 - Meteor

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

domlow
7 - Meteor

@mceleavey attached

mceleavey
17 - Castor
17 - Castor

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:

 

mceleavey_1-1621336635887.png

 

mceleavey_2-1621336665492.png

 

mceleavey_3-1621336680247.png

 

mceleavey_4-1621336698283.png

 

Hope this helps.

 

M.

 

 



Bulien

domlow
7 - Meteor

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
7 - Meteor
mceleavey
17 - Castor
17 - Castor

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.

 



Bulien

danilang
19 - Altair
19 - Altair

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

danilang_0-1621339440938.png

 

Dan

 

 

domlow
7 - Meteor

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 🙂

Labels
Top Solution Authors