Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

hydrogurl01
8 - Asteroid

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.

14 REPLIES 14
Claje
14 - Magnetar

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
7 - Meteor

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
8 - Asteroid

Hi @Claje,

 

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

 

Capture.PNG

 

 

hydrogurl01
8 - Asteroid

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?

danilang
19 - Altair
19 - Altair

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
8 - Asteroid

Hi @danilang,

 

Thanks so much! In the file there are 17 columns of data:

Customer

Main C##

Region

Market

Business Unit

Reporting Unit

CollectOP Code

Deposit Balance

Loan Balance

Outstanding Amount

Current

1-30 Days Past Due

31-60 Days Past Due

61-90 Days Past Due

91-180 Days Past Due

181-360 Days Past Due

361+ Days Past Due

 

I don't need the rows where it says Customer Unapplied Funding Receipts, which is an easy filtering process. When I am able to bring in all of the columns using mtakka1's approach above, all of the columns are squeezed together without any whitespaces making it impossible to separate into appropriate columns. However, if I try keeping the white spaces, I am unable to read in all of the columns of my file. 

 

I've attached the .txt file I am using along with images to this message.

 

hydrogurl01
8 - Asteroid

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 

mtakka1
7 - Meteor

Hi @hydrogurl01

You can just select the "Tabs, Line Breaks, and Duplicate Whitespace" in the Data cleansing tool. That way you delete only duplicate spaces while retaining the format. If the format is consistent, you can use the Text to Columns tool to split then into rows and format from that point

 

Thanks

hydrogurl01
8 - Asteroid

@mtakka1,

That would work with the original data I provided. I just edited the sample data I'm using to reflect more with the actual data I'm using. I have some customers with extremely long customer names and others were extremely short names. This makes it difficult to separate out with the parse tool or flat file since I data that should be in 1 column spread across 4 or 5 columns and mixed with other column data within the same column if that makes sense? I've attached another workflow and the edited dataset I'm using to demonstrate.

Labels