Modify Rowset calculation
Posted: Tue Feb 03, 2015 4:51 pm
Hello,
I would like to use Modify Rowset to change the values in a column, by dividing the original value by 100.
I created a Modify Rowset task, using existing columns, then hoped to modify a column by using the value such as ${oldCSVFile[4]} /100. However the result was a string, not a decimal though I did a type conversion to decimal when reading the original csv. The error message showed I was trying to insert a string into my Excel decimal field - For input string: "9773 /100". What I wanted from the Modify Rowset was the value 97.73, the original values 9773 divided by 100. Possibly I have to add a new column rather than modify the existing one?
CAn you help?
Thank you!
Here is the pertinent section of my project:
I would like to use Modify Rowset to change the values in a column, by dividing the original value by 100.
I created a Modify Rowset task, using existing columns, then hoped to modify a column by using the value such as ${oldCSVFile[4]} /100. However the result was a string, not a decimal though I did a type conversion to decimal when reading the original csv. The error message showed I was trying to insert a string into my Excel decimal field - For input string: "9773 /100". What I wanted from the Modify Rowset was the value 97.73, the original values 9773 divided by 100. Possibly I have to add a new column rather than modify the existing one?
CAn you help?
Thank you!
Here is the pertinent section of my project:
Code: Select all
<readFixedWidth label="Read Data File" inputFilesVariable="${myFileList}" outputRowSetVariable="Data" skipInvalidRecords="true" skipFirstRow="false" recordDelimiter="LF" version="1.0" logLevel="verbose">
<data trim="none">
<column index="1" name="Record Code" size="2" type="CHAR" />
<column index="2" name="Account Number" size="12" type="CHAR" />
<column index="3" name="Check Number" size="10" type="BIGINT" pattern="####000000" />
<column index="4" name="Amount" size="12" type="DECIMAL" alignment="right" />
<column index="5" name="Paid Date" size="8" type="CHAR" />
<column index="6" name="Issue Date" size="8" type="CHAR" />
<column index="7" name="Item Ref Number" size="12" type="CHAR" />
<column index="8" name="Additional Data" size="15" type="CHAR" />
<column index="9" name="Record Type" size="1" type="CHAR" alignment="right" />
</data>
</readFixedWidth>
<modifyRowSet inputRowSetVariable="${Data}" outputRowSetVariable="OutData" existingColumns="true" version="1.0">
<modifyColumn index="4" name="Amount" value="${Data[4]} /100" type="DECIMAL" />
</modifyRowSet>
<createWorkspace label="Workspace" version="1.0" />
<writeExcel label="Write Excel File" inputRowSetVariable="${OutData}" outputFile="USBankReconciliationFile${RecMonth}.xls" whenFileExists="overwrite" excelFormat="excel2003" templateFile="/download/excel/USBankReconciliation.xls" sheetName="ChecksPaid" includeHeadings="true" version="2.0">
<data startRowNumber="2">
<column index="9" horizontalAlignment="right" />
<column index="5" pattern="##/##/####" />
</data>
</writeExcel>