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

Auto Write Concatenated Column to Notepad (txt File)

jmmart08
8 - Asteroid

Hi All

 

I have a question about taking a concatenated column in an excel sheet and writing that data to the Notepad application to create a text file.

 

On the sheet attached, my group pastes in data to the Holdings File tab and ultimately ends up having to copy and paste the concatenation in Column A into a notepad file to upload to an accounting system.  I am being tasked with coming up with a way to have this automatically done, and say creating a process that writes only the first 100 rows of data, spits out a txt file, then continues on the next 100 rows and follows this process until all rows have been written to a txt file.  This is currently being processed manually where multiple individuals split the work and copy several concatenated rows and manually paste into Notepad.  I honestly have no idea how to try to automate this or if that is possible through Alteryx.  I'm nowhere near an expert in vba code so that route is difficult for me.

 

Anyone have any suggestions on how I can even try to attempt doing something like this?  Thank you all very much.

18 REPLIES 18
jmmart08
8 - Asteroid

Hi @BrandonB 

 

This is PHENOMENAL.  I very much appreciate your help with this.  Thank you so much!!  Is there a way on the last select tool where it is grabbing the concatenated column to not have that header show up on the txt file? 

jmmart08
8 - Asteroid

Please disregard this.  Apologies.  I know I can uncheck the box for first row contains field names.  Thanks again, this is very  helpful.  I may have more questions.  I hope I don't because you have answered so many already.

BrandonB
Alteryx
Alteryx

Of course! And you got it, you just uncheck the box for first row contains field names. Glad this is helpful for your team. Alteryx can handle large volumes of data so hopefully this alleviates your need to split up the work to make this manageable and can handle it all in one process. 

BrandonB
Alteryx
Alteryx

Also @jmmart08 if you wouldn't mind marking this as a solution it helps us to manage the threads that have been solved. You are welcome to reply to this again if you have a followup. Glad I could help!

jmmart08
8 - Asteroid

Hi @BrandonB 

 

Again, thank you for all of your help.  Would you mind taking a look at the below?  I have attached a sheet again.  The ask of me is to get as close to zero human intervention as possible and I'm nowhere near that level at this point with Alteryx.  Let me know if the below makes sense, in combination with the attachment and what I have provided to you previously.

 

 

I was curious if we could build an Alteryx workflow that can create several output files based on a set of distinct criteria.  Here are some of the details.   I have one Excel sheet.  That excel sheet has several thousand rows of data.  The values in column A are distinct.  While another column has an account number, and a second column has a security ID.  There exist a few hundred rows which share the same account number/security id combination.  I would like to export all of the values from column A, where the account number and security id are the same.  For example, account number XYZ, and security ID 123ABC are on the file 183 times.  The export document should be all 183 values from column A where the account number is XYZ and security id is 123ABC.  That file should be exported as a .txt file with the name XYZ_123ABC_183 records.txt (filename is a combination of accountnumber_securityid_#ofRecords.txt).    I would want the process to repeat until all of the distinct combinations of account number/security id have created an export file.    As an example, if there are 30,000 rows of data and 300 distinct combinations of account number/security id.  There would be 300 text files created and the sum of the records included in each file would be 30,000.   Is this a possible use of Alteryx?

BrandonB
Alteryx
Alteryx

Yep, absolutely possible. You would use a formula tool at the end of your workflow to create a new field that combines these fields to create the file name. Then the Output Data tool can dynamically create the files based on these values:

 

BrandonB_0-1597790715049.png

 

 

BrandonB_1-1597790772426.png

 

 

This will dynamically create files for each set of combinations. All rows with a given account number/security combination would have their own file output. 

jmmart08
8 - Asteroid

WOW.  Thank you so much for your unbelievably quick reply!!  I will take a look at this tomorrow morning and see where I can get.  I will absolutely accept as a solution once I take a look and get a better understanding.  This is so great.  Thanks again.

BrandonB
Alteryx
Alteryx

Example of this process with your latest file provided is attached. Notice below that a new field called Create File Name is calculated and then the output data tool uses it to create separate files for each combination. Workflow is attached!

 

dynamic output.png

BrandonB
Alteryx
Alteryx

Screenshot of the files generated on my desktop haha. You can of course point this to another folder location. 

 

files generated.png

Labels