Changing contents of an individual column in an excel
Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
2 posts
Page 1 of 1
Based on a lookup, I am trying to change the contents of an individual column in an excel.
My approach is based on using a For Each loop and running iterations against the row set.
I want all the contents of this column to get replaced by particular values present in the if loop contained in the for.
The logic I am trying to use is
1. Read the input file and generate rowset
2. forEach row in the selected column,
{
3. compare if
{
the value equals 'X', if yes replace 'X' with 'A'
then write the output to an excel
}
3. compare if
{
the value equals 'Y', if yes replace 'Y' with 'B'
then write the output to an excel, if excel exists append
}
3...
} so on and so forth till the for loop completes all elements in the column
Here's the code :
In the output excel file -- the one that I am writing, I am getting only the values from the last 'if check'. My speculation is since the input file has those values earlier up in the row in that column,
Would there be a better approach so that I am getting all the values written in the excel file from all the 'if checks' if all values are present in the input file ?
My approach is based on using a For Each loop and running iterations against the row set.
I want all the contents of this column to get replaced by particular values present in the if loop contained in the for.
The logic I am trying to use is
1. Read the input file and generate rowset
2. forEach row in the selected column,
{
3. compare if
{
the value equals 'X', if yes replace 'X' with 'A'
then write the output to an excel
}
3. compare if
{
the value equals 'Y', if yes replace 'Y' with 'B'
then write the output to an excel, if excel exists append
}
3...
} so on and so forth till the for loop completes all elements in the column
Here's the code :
Code: Select all
<forEachLoop itemsVariable="${outPut}" currentItemVariable="thisRow" currentIterationVariable="i" logLevel="debug">
<if condition="${thisRow[2] == GB}">
<modifyRowSet inputRowSetVariable="${thisRow}" outputRowSetVariable="gbTrade" excludeRow="${thisRow[2] <> GB}" startAtRow="2" version="1.0" logLevel="debug">
<modifyColumn index="2" value="GBP" />
</modifyRowSet>
<writeExcel inputRowSetVariable="${gbTrade}" outputFile="resource:smb://Copy of Desktop/april_mod3.xls" whenFileExists="append file" excelFormat="excel2007" includeHeadings="true" version="2.0" logLevel="debug" />
</if>
<if condition="${thisRow[2] == MC}">
<modifyRowSet inputRowSetVariable="${thisRow}" outputRowSetVariable="mcTrade" excludeRow="${thisRow[2] <> MC}" startAtRow="2" version="1.0" logLevel="debug">
<modifyColumn index="2" value="MCO" />
</modifyRowSet>
<writeExcel inputRowSetVariable="${mcTrade}" outputFile="resource:smb://Copy of Desktop/april_mod2.xls" whenFileExists="append file" excelFormat="excel2007" includeHeadings="true" version="2.0" logLevel="debug" />
</if>
<if condition="${thisRow[2] == ES}">
<modifyRowSet inputRowSetVariable="${thisRow}" outputRowSetVariable="esTrade" excludeRow="${thisRow[2] <> ES}" startAtRow="2" version="1.0" logLevel="debug">
<modifyColumn index="2" value="EUR" />
</modifyRowSet>
<writeExcel inputRowSetVariable="${esTrade}" outputFile="resource:smb://Copy of Desktop/april_mod1.xls" whenFileExists="append file" excelFormat="excel2007" includeHeadings="true" version="2.0" logLevel="debug" />
</if>
<iterateLoop condition="${ i > 120}" />
</forEachLoop>
<deleteWorkspace version="1.0" />
</module>
<variable name="GB" value="GB" />
<variable name="MC" value="MC" />
<variable name="ES" value="ES" />
<variable name="NL" value="NL" />
</project>
In the output excel file -- the one that I am writing, I am getting only the values from the last 'if check'. My speculation is since the input file has those values earlier up in the row in that column,
Would there be a better approach so that I am getting all the values written in the excel file from all the 'if checks' if all values are present in the input file ?
Chris Spargen from Linoma got in touch with me and he directed me towards the following :
This was possible by using the modify row set only,
No need to use the loop.
made use of a look up by using the following
This was possible by using the modify row set only,
No need to use the loop.
made use of a look up by using the following
Code: Select all
<readExcel inputFile="${ThisFile}" outputRowSetVariable="csvRow" skipEmptyRows="true" dataStartRowNumber="2" headingsRowNumber="1" version="2.0" logLevel="debug" />
<modifyRowSet inputRowSetVariable="${csvRow}" outputRowSetVariable="outPut" existingColumns="false" startAtRow="2" version="1.0">
<newColumn index="1" name="ReqValueDate" value="${CurrentDate("EEE, MMM d, ''yy")}" />
<newColumn index="2" name="BuyCCYCode" value="${if( csvRow[13] == 'GB', 'GBP', if( csvRow[13] == 'US', 'USD', if( csvRow[13] == 'ES', 'EUR', if( csvRow[13] == 'NL', 'EUR', if( csvRow[13] == 'GB', 'GBP', if( csvRow[13] == 'QA', 'QAR', if( csvRow[13] == 'MU', 'MUS', if( csvRow[13] == 'TN', 'TND', if( csvRow[13] == 'MA', 'MAD', if( csvRow[13] == 'SG', 'SGD', if( csvRow[13] == 'AU', 'AUD', if( csvRow[13] == 'MY', 'MYR', if( csvRow[13] == 'CA', 'CAD',if( csvRow[13] == 'CL', 'CLP', if( csvRow[13] == 'MX', 'MXD', if( csvRow[13] == 'DE', 'EUR', if( csvRow[13] == 'LB', 'LBP',csvRow[13] )))))))))))))))))}" />
2 posts
Page 1 of 1