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
Post Reply

intl_sp

Posts: 11
Joined: Wed Oct 01, 2014 8:49 am
Post by intl_sp » Wed Nov 26, 2014 9:33 am
Quote
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 :

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 ?
Top

intl_sp

Posts: 11
Joined: Wed Oct 01, 2014 8:49 am
Post by intl_sp » Tue Dec 09, 2014 12:18 pm
Quote
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

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, &apos;&apos;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] )))))))))))))))))}" />
Top
Post Reply
2 posts Page 1 of 1
Return to “Community Forum”