Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
-
toniduquette
- Posts: 7
- Joined: Thu Aug 21, 2014 3:43 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:
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>
-
Support_Rick
Support Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
Try making the following modification:
<modifyColumn index="4" name="Amount" value="${ Data[4] /100 }" type="DECIMAL" />
-- all calculations have to be inside the ${ }
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
-
toniduquette
- Posts: 7
- Joined: Thu Aug 21, 2014 3:43 pm
Thanks very much - that one gets me every time, I put the braces around the var not the expression!
-
toniduquette
- Posts: 7
- Joined: Thu Aug 21, 2014 3:43 pm
Hi Rick,
I changed the syntax as you suggested:
<createWorkspace label="Workspace" version="1.0" />
<modifyRowSet inputRowSetVariable="${Data}" outputRowSetVariable="OutData" existingColumns="true" version="1.0">
<modifyColumn index="4" name="Amount" value="${ Data[4] /100 }" type="DECIMAL" />
</modifyRowSet>
But I still get this error:
[8099 - modifyRowSet] An unexpected error occurred. Variable not found: Data[4] /100
I have tried with & without spaces before or after the variable etc. Here's the log file showing the rowset exists...
2/27/15 3:24:16 PM INFO Start Date and Time: 2/27/15 3:24:16 PM
2/27/15 3:24:16 PM INFO Job Number: 1418422964994
2/27/15 3:24:16 PM INFO Project Name: /Duquette/ARP Inbound to Excel
2/27/15 3:24:16 PM INFO Submitted By: DUQUETTE
2/27/15 3:24:16 PM INFO GoAnywhere Director 4.6.1 running on OS/400 V6R1M0 (PowerPC)
2/27/15 3:24:16 PM INFO Executing project 'ARP Inbound to Excel'
2/27/15 3:24:16 PM INFO Project location: /linoma/goanywhere/userdata/projects/Duquette/ARP Inbound to Excel.xml
2/27/15 3:24:16 PM INFO Executing module 'Main'
2/27/15 3:24:16 PM INFO Executing task 'createFileList 1.0 (Create List)'
2/27/15 3:24:16 PM INFO FileList variable 'myFileList' was created containing 1 file(s)
2/27/15 3:24:16 PM INFO Finished task 'createFileList 1.0 (Create List)'
2/27/15 3:24:16 PM INFO Executing task 'readFixedWidth 1.0 (Read Data File)'
2/27/15 3:24:16 PM INFO Data parsed successfully and the rowset variable 'Data' was created
2/27/15 3:24:16 PM INFO Finished task 'readFixedWidth 1.0 (Read Data File)'
2/27/15 3:24:16 PM INFO Executing task 'createWorkspace 1.0 (Workspace)'
2/27/15 3:24:16 PM INFO Workspace directory for this job is set to '/linoma/goanywhere/userdata/workspace/1418422964994'.
2/27/15 3:24:16 PM INFO Finished task 'createWorkspace 1.0 (Workspace)'
2/27/15 3:24:16 PM INFO Executing task 'modifyRowSet 1.0'
2/27/15 3:24:16 PM ERROR Variable not found: Data[4] /100
2/27/15 3:24:16 PM INFO Finished project 'ARP Inbound to Excel'
2/27/15 3:24:16 PM ERROR [8099 - modifyRowSet] An unexpected error occurred.
Variable not found: Data[4] /100
Do you have further help?
Thanks!
-
Support_Rick
Support Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
Can you send me your FULL Project XML and JobLog so I can see what's going on?
Also, can you send a snippet .. maybe 4-5 lines of the Input file you're using that is becoming ${data}.
Thanks!
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
-
toniduquette
- Posts: 7
- Joined: Thu Aug 21, 2014 3:43 pm
Thank you.
Attached are the project xml and a sample of the data
-
toniduquette
- Posts: 7
- Joined: Thu Aug 21, 2014 3:43 pm
Hi Rick,
I neglected to attach the joblog, here it is.
-
Support_Rick
Support Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
Upgrade your project to version 2.0 then try it again... click on the "UPGRADE" button from within your project while editing.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696