importing text file to cleanse issues

Hi, I'm trying to import a text file (please see below details and the attached file) to perform some cleansing, specifically deleting the lines beginning with EXTRACT and splitting the DETAIL lines by |. I thought this would be a breeze but it's harder than i expected. can someone advise on what i'm doing wrong? The workflow is also attached. thanks!

EXTRACT|2019-07-02|540|261902.66
DETAIL|2764|2019-07-02|1|00001083|Joe|Ty||UNITED STATES|USA|857.52
DETAIL|2764|2019-07-02|2|00001083|Joe|Ty||UNITED STATES|USA|745.01
DETAIL|2764|2019-07-02|3|00001083|Adam|Smith||UNITED STATES|USA|52.21

testdetails workflow.yxmd

testdetails.txt

Workflow

Input

Common Use Cases

Accepted answers

MichaelLaRose

Hi @tyreenugen,

The problem with reading the data is that Alteryx is seeing that different rows have a different number of fields and that is causing issues.

You can use the attached workflow to read in the document and create the dateset you are looking for.

Note I used a relative path so the data file just needs to be in the same folder as the workflow.

Best,

Michael

testdetails workflow.yxmd

All comments

MichaelLaRose

Hi @tyreenugen,

The problem with reading the data is that Alteryx is seeing that different rows have a different number of fields and that is causing issues.

You can use the attached workflow to read in the document and create the dateset you are looking for.

Note I used a relative path so the data file just needs to be in the same folder as the workflow.

Best,

Michael

testdetails workflow.yxmd

jeff_reynolds

As with anything Alteryx related, there's more than 1 way to solve the issue. I imported your data by setting up the file import config the following way.

If you have other rows downstream that contain 'EXTRACT', you should be able to filter those right out.

Good luck.

smoskowitz

Hi @tyreenugen

See if this helps,

Seth

testdetails workflow.yxmd

tyreenugen

thanks all for the help! i appreciate it.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703