Reading only specific columns from .XLS file

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
3 posts Page 1 of 1

mpan20

Posts: 5
Joined: Mon Sep 23, 2013 12:35 pm

Post by mpan20 » Mon Sep 23, 2013 1:04 pm
Upfront disclaimer: I'm very new to GoAnywhere Director. I have a problem where I need to read data from 2 columns (column 1 and column 4) of an .XLS file, sort it, and write it back to a .CSV file. I've created the project below, but I'm getting an error that the resource is not found. I used the rowset variable name ${excelData} as the database server and I'm guessing that's the issue. Can anyone advise on how to save Excel data to a rowset variable, run a SQL query on that variable, and then export the query results to a .CSV? Is there a better way to accomplish what I'm trying to?

This is the error I receive:
[8098 - sql] Resource not found: com.linoma.dpa.tasks.converters.excel.v2.ExcelRowSet@a8d2d1

Here's the project:
Code: Select all
<project name="EditBadgeFile" mainModule="Main" version="2.0">
	<variable name="excelData" value="" valueIsEncrypted="false" />
	<variable name="queryData" value="" valueIsEncrypted="false" />

	<module name="Main">

		<readExcel inputFile="D:\SC\Weekly Mon Personnel Report.xls" outputRowSetVariable="excelData" dataStartRowNumber="1" version="2.0">
			<data>
				<column index="1" name="emp" type="VARCHAR" />
				<column index="4" name="badge" type="VARCHAR" />
			</data>
		</readExcel>


		<sql resourceId="${excelData}" version="1.0">
			<query outputVariable="queryData">
				<statement>SELECT emp, badge from ${excelData} ORDER BY emp</statement>
			</query>
		</sql>


		<writeCSV inputRowSetVariable="${queryData}" outputFile="D:\SC\BADGE.csv" whenFileExists="overwrite" fieldDelimiter="comma" version="1.0">
			<data>
				<column index="1" />
				<column index="2" />
			</data>
		</writeCSV>

	</module>

</project>
Thanks for any advice!

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Fri Sep 27, 2013 12:24 pm
MPan20,

Reading a rowset does not provide you with an SQL Engine to read that rowset. To accomplish this task, you would need to:

1. Perform the readExcel like you have already.
2. Insert that rowset ${excelData} into a Temporary table on your IBMi, MSSQL or MySQL Servers
3. Use a Database SQL Select to retrieve the specific data you need from the Temporary table utilizing the ORDER BY clause to sort as desired. This will create another rowset, say ${excelSorted}
4. Perform a writeCSV utilizing the ${excelSorted} rowset variable as the input.

Hope this helps!
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

mpan20

Posts: 5
Joined: Mon Sep 23, 2013 12:35 pm

Post by mpan20 » Fri Sep 27, 2013 1:23 pm
Thanks so much for the info. I'll give that a shot. I had read in some other forum somewhere online that I could query directly from a rowset variable, so that's what I was trying. This makes more sense and should work great for what I need.

Thanks again!
3 posts Page 1 of 1