Extract certain from one .xlsx file to another template .xlsx file

Question

Hi All,

This is my initial experience with the Alteryx Designer tool, and I would appreciate your guidance in finding solutions to this matter..

My Query

I possess an .xlsx file that encompasses data ranging from columns A to AZ. My objective is to extract specific rows from this Excel file and present the output in a separate Excel document formatted according to specific requirements. The output file will already include a header, and the records should be appended beneath this header.

Furthermore, I require that additional validation be incorporated prior to the generation of the output data file as outlined below.

1) The input file contains names organized into three columns: First Name, Last Name, and Middle Name. These names should be concatenated and included in a single column in the output file.

2) An additional validation must be incorporated into this output file to ensure that if three rows of data are duplicates, only a single row is generated in the output instead of multiple rows.

shancmiralles · Accepted Answer

hi! first clean your data - "data cleansing" tool first! 
      clean your data for any "null rows", "null columns", "Leading and Trailing Whitespace", and "Tabs, Line Breaks, and Duplicate Whitespace".

Next remove dup row  -  " Unique" tool :

connect a  "unique tool" after your "data cleansing" tool and choose "First Name, Last Name, and Middle Name" under column names from the configuration window to ensure that duplicate records are remove (if you are basing duplicates according to the "First Name, Last Name, and Middle Name" data columns).

First Name, Last Name, and Middle Name = FULL NAME : formula tool

connect a "formula"  to the "U" output anchor of your "Unique" tool.  from the "formula" tool configuration window, choose the "add column" from the Select column drop down option (below the words Output Column) give it a name " FULL NAME"  and enter this as a formula:
                     [First Name]+","+" "+[Last Name] +","+" " +[Middle Name]

removing "First Name, Last Name, and Middle Name"

Now, get a "Select" tool and  connect it to your formula tool.  uncheck the columns for the "First Name, Last Name, and Middle Name".

try running the WF and this should give you a clean data now.

for your output you have a couple of options..

use the reporting tools

use blob tool(s) to direct your output on a pre-made template.

hope this somehow help you start with your project! - goodluck!