How to create new column from one of the cell of excel sheet ?

[shukla_sumit]

@yalmar_m @Kenda

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 march 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.

I wanted to add this column into my exisitng workflow result.

[Kenda]

Hi @shukla_sumit

We can't run your workflow successfully unless you package it before uploading or upload the Excel input separately.

[shukla_sumit]

Please use SourceFile tab of this Excel.

Thanks.

[jdunkerley79]

I suggest:

The select records tool makes it easy to pick a specific row from the input data set.

You can then use a Formula tool to construct a date from the date range:

DateTimeAdd(Left(Right([F2], 7),4)+"-03-31",12,"months")

Finally an Append Fields tool puts it into one row.

This can then be appended to any other data set

[danilang · Accepted solution]

hi @shukla_sumit

Here's a modification of your original workflow.  You were very close.  All I added was the YE date and the split to find the Trust name.  this was a little tricky since there's nothing to indicate that it is the trust name row.  In the end, I went with the row that has "Data Collection" in the row before and Specialty in the row after(after removing null rows)

I would have thought that @jdunkerley79 might be taking some time off to let the rest of us catch up!  I guess the 750 prize is too tempting!

Dan

[shukla_sumit]

@jdunkerley79 @danilang Thank you so much for your help.

Both of you your solution is awesome but my scenario is little bid different.

Solution is working if we have only one excel file but my problem is i have 100 of trust file.

I need to create only one .csv for all the trust and each row should contains their corresponding trust name.

 As i am using wildcard * to connect all the file i am getting only one trust name for whole the data not their corresponding trust name.

suppose i have 2 trust file and i am creating 1 csv from both the file data so the rows from trust 1 file should have trust 1 name and trust 2 file should have trust 2 name in csv file.

Pattern which i observed is the trust name present above the Specialty.

I have already shared the source file so you can duplicate the file and change the My trust name inside the file to try this.

Thanks in advance. :)

[jdunkerley79 · Accepted solution]

The easiest way to do this is to bring the file name with the input tool and pass it all the way along.

The groupings must be set to group by file name as well as group and then should basically work.

You can then use a Join Multiple to bring the extra columns in rather than the Append Field tool.

As the row number needs to reset by file, the select records tool I used doesnt work in this case. So instead I use a multi row formula to create a row number within each file and then filter on this to get the date cell and trust name.

Update sample attached.

[shukla_sumit]

Hi @jdunkerley79 I have tried this workflow but i am getting null in Trust Name and date.

[shukla_sumit]

I have created replica of excel and changed the trust name so now i have 2 excel and i am using wildcard to connect both in a single run each excel has 44 rows so in the result 44 rows should have My trust name as a Trust Name and other 44 should have My trust name1 as a Trust Name. total rows should be 88 but i am getting null on both the column.

[jdunkerley79 · Accepted solution]

Sorry was a mistake in my sample.

The Multi Row formula to make the Row number needs to default to 0 not NULL

Fixed example attached.