Hi,
I'm reading a csv with a date column.
I need to group all the rows with the same date into a new excel file.
Grouping by date
Post any question you may have in regards to GoAnywhere MFT and let our talented support staff and other users assist you.
If you need a quicker response, please create a support ticket via the customer portal my.goanywhere.com or contact our support team by email at [email protected].
If you need a quicker response, please create a support ticket via the customer portal my.goanywhere.com or contact our support team by email at [email protected].
- Support_Rick Offline
- Support Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
- Contact:
Re: Grouping by date
Aaron,
Check into the "modifyRowset" Task.
Basically, you use the full file RowSet as input, then just select the rows you want (matching the date) as output to create a separate RowSet with just those selected rows. Then, write that out to the Excel.
Check into the "modifyRowset" Task.
Basically, you use the full file RowSet as input, then just select the rows you want (matching the date) as output to create a separate RowSet with just those selected rows. Then, write that out to the Excel.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Re: Grouping by date
Thanks.
Is there a way to get distinct values of a column? Or do I have to loop through it first, write it on another file and then read that file and process etc.
Is there a way to get distinct values of a column? Or do I have to loop through it first, write it on another file and then read that file and process etc.
- Support_Rick Offline
- Support Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
- Contact:
Re: Grouping by date
I'm not sure what you mean ...
If you only want certain columns from the original file, then the "modifyRowset" will allow you to combine, add, remove columns as you read it.
If you only want certain columns from the original file, then the "modifyRowset" will allow you to combine, add, remove columns as you read it.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Re: Grouping by date
Thanks, you're right I don't need the distinct method.
Re: Grouping by date
When I'm writing to excel, even though i'm passing a single row, it's writing all the dataset in one go. Is there a way to write one row at a time?
Code: Select all
<project name="Aaron Test" mainModule="Main" version="2.0" logLevel="verbose">
<module name="Main">
<readCSV inputFile="C:\GoAnywhereSample\sample.csv" outputRowSetVariable="csvrowdata" fieldDelimiter="comma" skipFirstRow="true" version="1.0" logLevel="verbose" onError="abort" />
<forEachLoop itemsVariable="${csvrowdata}" currentItemVariable="currow" disabled="false">
<print label="Print currow" version="1.0">
<![CDATA[${currow[1]}]]>
</print>
<setVariable label="setvar: yyyy" name="yyyy" value="${Substring(currow[1], 7, 4)}" version="2.0" disabled="false" />
<setVariable label="setvar: dd" name="dd" value="${Substring(currow[1], 4, 2)}" version="2.0" disabled="false" />
<setVariable label="setvar: mm" name="mm" value="${Substring(currow[1], 1, 2)}" version="2.0" disabled="false" />
<print label="Print mm-dd-yyyy" version="1.0" disabled="false">
<![CDATA[${mm}-${dd}-${yyyy}]]>
</print>
<writeExcel inputRowSetVariable="${currow}" outputFile="C:\GoAnywhereSample\${mm}-${dd}-${yyyy}.xlsx" whenFileExists="append" excelFormat="excel2007" sheetName="ERN Reports" includeHeadings="true" version="2.0" disabled="false">
<title rowNumber="1" backgroundColor="Dark Green" fontSize="14" fontColor="Dark Red" bold="true">
<![CDATA[ERN Reports]]>
</title>
</writeExcel>
<if condition="${currow[1] != 'Date'}" disabled="true" />
</forEachLoop>
</module>
</project>