How to remove unnecessary row and data from excel to create single csv data file.

shukla_sumit

@ChristineB @jdunkerley79

In my case in a single excel sheet I have data into 3 groups and for each  group structure is same means column header are same in all three groups but I need it only once as a column name or header and need to merge all 3 groups data into single csv file.

How can i handle such situation into alteryx, Can you please help me out with this issue ?

attaching source file and expected result both.

SampleData.xlsx

jdunkerley79

So the workflow I posted does the following

First, use a Select tool to pick the columns I actually want. In this case ignoring F1, F18, F19 and F20 (these are what Alteryx calls columns A, R, S & T).

Next, use a Multi-Row formula tool to create a new column containing the Group. The expression:

IIF(Regex_match([F2],"Group \d+"),[F2],[Row-1:Group])

takes the Value of F2 (Column  if matches the regular expression Group \d+ - i.e. starts with Group followed by a number. If not it copies the value from the row above ([Row-1:Group])

Again, I use another Multi-Row formula tool, this time to create a new column, called row, holding the row number within each group.

IIF([F2]="Specialty",0,[Row-1:Row]+1)

Basically, this just adds one to the row above until it finds Speciality in F2 (Column . In order to not count rows before the first Speciality entry, the Multi-Row formula is told to use NULL as the default value rather than the usual 0 or empty.

At this point, I use a Filter tool to remove dead rows (where F2 is empty) or rows with no Row number. This produces the dataset and header needed.

A second filter splits the header rows (when row = 0) from the data rows. For the data rows, we are done. 

The header rows the first is chosen and used to rename the columns from F2-F19 to nice names. This is done by transposing this row to columns and then using a dynamic rename tool.

I hope that helps explain the workflow.

In terms of doing multiple sheets. 

If all the sheets are identical then the easiest way to do it is to use a wildcard on the input tool to read all the files in. You can change the input tool to read a list of worksheet names and then feed this into a dynamic input tool to read all the sheets. This will only work if all columns are consistent across all worksheets in all workbooks. 

Could you post an example source workbook with multiple sheets? I can then stick a simple sample together for you to look through.