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
Solved! Go to Solution.
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.
@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.
The point with the extra "field" is also not clear to me.
Thank you for your support.
Regards,
Pascal
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.
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
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.
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
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.
Hello @jarrod ,
I've built a little solution which can be used regardless the number of input columns.
Regards
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).
@jarrod I'm unable to open your attachment due to using an older version. Any idea I can open it without changing the version?
The 4 fields after "memo" I can exclude because SAP is bringing them up without headers.
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.
i'll change it to 2019.1 and save, assuming you are on a version after that one and it should open just fine.
Here's the updated Workflow