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.
Solved! Go to Solution.
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?
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
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?
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
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.
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
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
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.