Auto Write Concatenated Column to Notepad (txt File)

[jmmart08]

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.

[BrandonB]

@jmmart08 that does not sound like fun at all! This is definitely something that can be accomplished in a workflow. I will see if I can mock up something that tackles most of this for you. 

[BrandonB]

There are still additional columns that need to be built out, but this is the general framework of the process. Alteryx will be able to tackle this entire job so you won't need to split this up among analysts. I have attached the workflow that I started on below for your reference. 

 

[BrandonB]

The end result can be written directly to a text file as shown below that would be ready for upload into the system you are describing

 

[BrandonB]

Here are the settings you will want to use to configure the Output Data tool. You originally set it up as a CSV, but then change the delimiter to \0 which means no delimiter. You can also change the CSV to txt in the file path, and uncheck the box for "First Row Contains Field Names". That should do the trick!

 

[jmmart08]

Hi @BrandonB 

Thank you VERY much for your assistance on this.  I really appreciate it.  Can you please take a look at my attached .txt file?  I am trying to tie this exact format to the output that is in your workflow you provided.  I need my format to match exactly to what I have attached.  This is a specific message type that must be uploaded to a system.  Commas, quotations, spaces, etc must match.  I'm not sure how to account for that in your workflow.  There are several variances so I'm trying to see what I can do to match what I have attached when everything has been concatenated.  Any suggestions would be greatly appreciated.  Thanks again!

[jmmart08]

Hi @BrandonB 

I also had a question regarding the trade date, settle date, cusip column, etc.  These are dynamic.  The data will change depending on a different set of data that will be pasted in to that holdings tab on my file I provided.  Rather than adding a specific date or specific cusip, I was wondering how to just have those fields equal what the value will be once data is pasted into that tab as opposed to static values.

Thank you!!

[BrandonB]

Yep the previous formula wasn't 100% matching because I hadn't made it through every column in your spreadsheet. I had some time this morning to make my way through the rest of the spreadsheet and the updated workflow attached I believe should be what you are looking for. One thing to note is that you did have a bunch of hard coded values. I'm not sure if you normally leverage vlookups for these (green columns), or if you hard code data. In any case, you can find the formulas where I hard coded the values like you did in the formula tool labeled "hardcoded formulas". 

Let me know if you have any questions!

-Brandon

 

[BrandonB]

Alteryx has the DateTimeNow() function which can pull the current date time of when the workflow is run, but I'm not sure how you wanted to address that based on your last comment. In any case, I think your next steps would be validating that the workflow I provided above is in the right format and then the formulas can be tweaked to get your desired result. 

[BrandonB]

If you want the trade and settle date from the original data rather than hardcoded, you just bring it in with the select tool in the beginning of the workflow rather than using a formula to hard code it. I have attached the updated workflow below that leverages the trade and settle data from the file.