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.

Reading only specific columns from .XLS file

Postby 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!
mpan20
 
Posts: 3
Joined: Mon Sep 23, 2013 12:35 pm

Re: Reading only specific columns from .XLS file

Postby 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
Sr. Product Specialist
(402) 944.4242
(800) 949-4696
Support_Rick
Support Specialist
 
Posts: 164
Joined: Tue Jul 17, 2012 2:12 pm

Re: Reading only specific columns from .XLS file

Postby 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!
mpan20
 
Posts: 3
Joined: Mon Sep 23, 2013 12:35 pm


Return to Community Forum

Who is online

Users browsing this forum: Yahoo [Bot] and 3 guests

cron