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
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:
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
Thanks for any advice!<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>
- Support Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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!
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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
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!
Thanks again!
3 posts
Page 1 of 1