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

[hydrogurl01]

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.

[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 · Accepted solution]

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

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.

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

Dan 

[hydrogurl01]

@danilang,

This is absolutely phenomenal!! Never in a million years would I have known to try something like this. Thank you so much!! Going to take me a bit of time to go through and understand all the steps you took. It seems like the base of being able to do this was using the RegEx formula you had in there to add a pipe delimiter, correct?

Again, thanks so much! This does exactly what I was hoping for!

[danilang]

Hello @hydrogurl01

i'm glad I could help.  Yes, the starting point was the Regex that replaced all occurrences of 2 spaces or more with a pipe.  specifying at least 2 spaces made sure that the workflow left the single spaces that were within the customer name.

Judging from your screen name, do you work in the Energy sector?  I happen to work for a renewable energy provider and most of our power comes from hydro.

Dan  

[hydrogurl01]

I don't specifically work in the Energy sector, however I have had multiple client projects in that realm. Hydro power has certainly been a fast growing energy power within the last few years from what I've seen at clients!