Grouping by date

If you have a new question you’d like our support staff to post a response to, please visit our customer community, GoAnywhere Insiders, to pose the question in our Discussion Boards. We have a thread “Ask Our Tech Experts” that our support team monitors on a regular basis, or you can start a new discussion where other GoAnywhere users and support staff can weigh in. Log in or create your new account at https://insiders.goanywhere.com.

If you need an immediate response, please create a support ticket or contact our support team by email at [email protected].
6 posts Page 1 of 1
Post Reply

aaron

Posts: 5
Joined: Mon Oct 24, 2016 11:45 am
Post by aaron » Wed Oct 26, 2016 7:47 am
Quote
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.
Top

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ
Post by Support_Rick » Wed Oct 26, 2016 7:59 am
Quote
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.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Top

aaron

Posts: 5
Joined: Mon Oct 24, 2016 11:45 am
Post by aaron » Wed Oct 26, 2016 8:04 am
Quote
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.
Top

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ
Post by Support_Rick » Wed Oct 26, 2016 8:08 am
Quote
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.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Top

aaron

Posts: 5
Joined: Mon Oct 24, 2016 11:45 am
Post by aaron » Wed Oct 26, 2016 8:27 am
Quote
Thanks, you're right I don't need the distinct method.
Top

aaron

Posts: 5
Joined: Mon Oct 24, 2016 11:45 am
Post by aaron » Wed Oct 26, 2016 11:52 am
Quote
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] != &apos;Date&apos;}" disabled="true" />
		</forEachLoop>
	</module>

</project>
Top
Post Reply
6 posts Page 1 of 1
Return to “Community Forum”