Reading only specific columns from .XLS file
Posted: 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:
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>