This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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 ?
As I am new in alteryx can you please explain me that work flow which you have shared with me ?
second thing is I have 100 of excel files and all the files has same structure, every file has multiple sheet and in every file sheet names are same, Now i need to connect all the files using single workflow which you have already shared with me as a solution and wanted to collect all the data from all the files for one particular sheet into one single .csv file.
takes the Value of F2 (Column B) 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.
Basically, this just adds one to the row above until it finds Speciality in F2 (Column B). 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.
I forgot to ask one more question which is how to create new column from particular row data.
for example in the attached sheet I have row number 5 "Data Collection 2017-18" from which i wanted to create date column and the value will be 20180331 means last day of macrh for year 2018 ,if the year is 2016-17 then 20170331 and so on for all the row of that file.
Second In row number 6 which is "My Trust Name" wanted to create new column trust name and the value for that column will be the value of that cell for all the rows of that file ,currently it's My Trust Name.
How we can achieve this in previous workflow(Excel Reformat.yxmd).