Alteryx Designer Desktop Discussions

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

Input column issue

Pascal_R
8 - Asteroid

Hi Team,

 

If I try to input attached txt file I dont get the correct columns and data. 

Can you help me where the issues is?

 

Best regards,

Pascal

10 REPLIES 10
jarrod
ACE Emeritus
ACE Emeritus

looks a bit dirty to me, so i would go a cleansing route outside of the input tool.

Set file format to .csv

set Delimiters to \0 (no delimiter)

deselect "first row contains field names"

increase field length to 5000 so it isn't truncating the data.

 

then add a text to columns tool and parse on \t (tab-delimited)

set number of columns to 50 and you'll see extra "fields" if you scroll over to the right after running the workflow.

Pascal_R
8 - Asteroid

@jarrod: Thank you for you help: So the Input file is now csv. Can you please check the settings for me?

Somehow it still is not correct.

 

Pascal_R_0-1615903532930.png

 

Pascal_R_1-1615903637157.png

 

 

 

The point with the extra "field" is also not clear to me.

 

Thank you for your support.

 

Regards,

Pascal

jarrod
ACE Emeritus
ACE Emeritus

If you deselect the "First Row Contains Field Names" in the input tool, you'll see the field names in first row (and the field names will be numbers). With that setup, you'll see row 2 has a few more "entries" than the row 1 (field names). that's due to extra Tab's in the data.

 

Edit: Also, you just need "\t" in the text to columns field. 

Pascal_R
8 - Asteroid

@jarrod 

 

I have deselected the "first row contains field names now". As you can see in my screenshots I have now different field headers.

Is there no way to keep the initial headers? Strangely I have the issue only with this report with others Alteryx is able to read the txt files.

 

 

Regards,
Pascal

jarrod
ACE Emeritus
ACE Emeritus

right, so in the Text to columns tool, delete "(tab-delimited)" from the field so that the only text in that field is \t

 

EDIT: to shed more light on this - every character (except for special characters \t, \s, \0) is used as a delimiter in this field. so if you put abcd, then the text to columns tool sees that as 4 delimiter values, not a single pattern "abcd". So in this case, it is looking for any t,a,b,d,e,l,i,m,(,) characters to split for a new field. 

jarrod_0-1615904432645.png

 

Then to pull the field names up from the first row, use the dynamic rename tool and choose:

Take Field Names from First Row of Data

jarrod_1-1615904664554.png

see my attached workflow for how i solved.

 

at the end of the data are 4 fields after "memo" that do not have headers - i don't know why that is happening, but it looks like somewhere along the way the data has a few extra tab's that are shifting the data over by 4 fields. 

afv2688
16 - Nebula
16 - Nebula

Hello @jarrod ,

 

I've built a little solution which can be used regardless the number of input columns.

 

Regards

jarrod
ACE Emeritus
ACE Emeritus

I do love this method @afv2688 . thanks for posting it. One tweak i would make is change "concatenate" to "First" in the Cross Tab tool - it's more performant and shouldn't matter for this use case since each value should be unique (1:1 field to row relationship). 

Pascal_R
8 - Asteroid

@jarrod  I'm unable to open your attachment due to using an older version. Any idea I can open it without changing the version?

 

 

Pascal_R_0-1615905416804.png

 

The 4 fields after "memo" I can exclude because SAP is bringing them up without headers.

 

jarrod
ACE Emeritus
ACE Emeritus

yeah, sorry about the version issue. it happens. The quick fix is to right-click on the workflow file (.yxmd) and edit in notepad. the second line controls the "version" of the file.

jarrod_0-1615905798714.png

 

i'll change it to 2019.1 and save, assuming you are on a version after that one and it should open just fine.

jarrod_1-1615905853206.png

Here's the updated Workflow

 

Labels