.TXT strings are being truncated. How can I make sure all my data is read into Alteryx?

Hi All,

I have a .txt file I am attempting to input into Alteryx. For some reason, all of my fields are not showing up. My end goal is to separate this .txt into columns, but I can't do that if Alteryx isn't reading all of my columns from the get-go. Any advice on how to resolve this? I have attached my data along with what I've attempted to do to resolve this issue. I've tried reading in as a flat file, selecting allow long lines, and putting the field length to the max with no luck. I've also changed the character length around to a higher number when reading in as a normal csv file but no luck there either unfortunately.

Alteryx Community Example Workflow.yxzp

Data Example for Alteryx Community.txt

Workflow

Parse

Best Practices

Transformation

Help

Dynamic Processing

Preparation

Download

Data Investigation

Accepted answers

danilang

Hi @hydrogurl01

Here's my 1st cut of the workflow. Like I mentioned in my first post. Flat files are tricky. You have to break them down into groups depending on the type of data in the row. I parse the header rows, data value rows, percent rows and "All companies" rows separately and recombine them all and sort by a RecordID field that I added early in the work flow

solution v2.png

Using your second data file, stored in the same directory as the workflow, the results are as follows. This is assuming that you alternate data and percentage rows and that you remove the extra space after the initial customer number.

Results v2.png

I'll leave it to you to output in the format of your choosing

Dan

Alteryx Community Example Workflow v2.yxmd

All comments

Claje

Hi,

I took a look at your workflow, and it looks to me like the Flat file input is working and pulling in all data. What data seems to be missing? Can you provide an example of where you are expecting a value that does not appear?

mtakka1

Hi @hydrogurl01

You are not able to view the complete data in the results pane because of the white spaces, add data cleansing tool and remove duplicate white spaces and you should be able to view everything or expand the size of the results pane

hydrogurl01

Hi @Claje,

I am not seeing these columns in any of my workflows unfortunately:

hydrogurl01

Hi @mtakka1,

That worked in bringing all my data in! However, the way it was output was extremely scrunched up in doing so, and now I'm seeing that separating my data out into proper columns is proving much more difficult because of that. In my attached workflow I've tried separating columns using both a flat file manual method and using the Parse tool, but I have a lot of overlapping columns that shouldn't be in the same column. Any ideas on how to resolve this?

Alteryx Community Example Workflow.yxzp

danilang

hi @hydrogurl01

I'm in the process of building a workflow to map your file. it's not simple, since flat format has different columns depending on which row is read. Right now I'm working on the customer amount rows that look like this

                                                                                                                           Business         Reporting               CollectOP
Customer                                                                Main C##      Region        Market                 Unit             Unit                    Code     
----------------------------------------------------------------        --------      --------      --------               --------         ----------------------- ---------
76165828       Customer 12340848304                              25150001      127            120                     0127          MAJOR-UP                  CODEW

There appear to be 7 columns of data as follows

Customer

Main C##

Region

Market

Business Unit

Reporting Unit

CollectOp Code

Can you please tell me how the values in the data line, the one that starts with 76165828, map to these 7 columns?

Thanks

Dan

hydrogurl01

Specific to the columns you saw:

Customer is:

76165828       Customer 12340848304

Main C## is:

 Main C## 

25150001

Region is:

Region       
 --------    
127

Market is:

Market               
  ---------
120

Business Unit is: 0127

Reporting Unit: Major-UP

CollectOP Code: CODEW

danilang

How do you want the percentages in your final CSV? Do you want one row values, next row percentages like your input data? Or do you want alternating value/percentage on one row like this

Current Balance   Current %   1-30 Days   1-30 Days %
         202.65     100.00%         0.0          0.0%

Also

For your Customer column, does the result have to include all the spaces between the fist number and the word "customer"

76165828       Customer 12340848304

Or can that be shortened to "76165828 Customer 12340848304" with only one space

Dan

danilang

Hi @hydrogurl01

solution v2.png

Results v2.png

I'll leave it to you to output in the format of your choosing

Dan

Alteryx Community Example Workflow v2.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703