copying files based on rowset

[ehersh]

Good day, I am not sure how to get started on this I was hoping I could get pointed in the correct direction. We have numerous files in a directory. Each file name may be different, however one piece of their name will have a customer number. That customer number is in a spreadsheet. Is what I need to do is load the column of customer numbers to a variable and copy only the files with a customer number from that column of the spreadsheet to another directory.

[Support_Rick]

Read the spreadsheet (Rowset)
Loop through the Rowset and retrieve the customer number
Copy (Create file list) based on Customer number (Filter = *CustomerNo*)

[ehersh]

Thank you Rick, it appears to work. Per usual I will leave my code here, so if anyone has any suggestions to improve efficiency or wants to look at it for their own development feel free. I will be doing more heavy duty testing however.

Code: Select all

---

<project name="test read excel" mainModule="Main" version="2.0" logLevel="debug">
   <description>ESH test read excel and load to file list</description>
   <module name="Main" logLevel="debug">
      <createWorkspace version="1.0" />
      <readExcel inputFile="resource:smb://ScionFS01_Groups_CIS/Eligibility/Test_Files/filelist.xlsx" outputRowSetVariable="newrow" dataStartRowNumber="2" headingsRowNumber="1" version="2.0">
         <data>
            <column index="2" name="filename" />
         </data>
      </readExcel>
      <forEachLoop itemsVariable="${newrow}" currentItemVariable="currentrow" disabled="false">
         <copy destDir="resource:smb://ScionFS01_Groups_CIS/Eligibility/Test_Files/archive1" version="1.0" disabled="false">
            <fileset dir="resource:smb://ScionFS01_Groups_CIS/Eligibility/Test_Files">
               <regexFilter>
                  <include pattern="${newrow[2]}" caseSensitive="false" />
               </regexFilter>
            </fileset>
         </copy>
      </forEachLoop>
      <deleteWorkspace version="1.0" />
   </module>
</project>


[ehersh]

finally got back to this. OK I did try that, however it appears that after I pick up a file to the file list on the next iteration it overwrites the file list so I am down to 0 files. Below is my code.

Code: Select all

---

<project name="test_FILE_TRANSFER" mainModule="Main" version="2.0" logLevel="debug">
   <description>This job will read the transfer.xlsx spreadsheet payee column and load to file list</description>

   <module name="Main" logLevel="debug">

      <createWorkspace version="1.0" />


      <readExcel inputFile="resource:smb://testdir_Groups/transfer/Test_Files/transfer.xlsx" outputRowSetVariable="newrow" dataStartRowNumber="2" headingsRowNumber="1" version="2.0">
         <data>
            <column index="3" name="column filename" />
         </data>
      </readExcel>

      <forEachLoop itemsVariable="${newrow}" currentItemVariable="currentrow">

         <createFileList fileListVariable="filelist" numFilesFoundVariable="filecnt" version="1.0">
            <fileset dir="resource:smb://testdir_Groups/transfer/Test_Files">
               <wildcardFilter />
               <regexFilter>
                  <include pattern="${newrow[3]}_1_4122013527" />
               </regexFilter>
               <dateFilter />
               <sizeFilter />
            </fileset>
         </createFileList>

      </forEachLoop>

Below is a snippit of the log

File List variable 'filelist' was created containing 0 file(s)
4/4/17 8:12:26 AM INFO Finished task 'createFileList 1.0'
4/4/17 8:12:27 AM INFO Executing task 'createFileList 1.0'
4/4/17 8:12:28 AM INFO File List variable 'filelist' was created containing 1 file(s)
4/4/17 8:12:28 AM INFO Finished task 'createFileList 1.0'
4/4/17 8:12:47 AM INFO Executing task 'createFileList 1.0'
4/4/17 8:12:48 AM INFO File List variable 'filelist' was created containing 0 file(s)
4/4/17 8:12:48 AM INFO Finished task 'createFileList 1.0'
4/4/17 8:12:52 AM ERROR Job 1397290122377 was canceled by admin user 'eshersh'
4/4/17 8:12:52 AM INFO End Date and Time: 4/4/17 8:12:52 AM

[Support_Rick]

Use the Current Item Variable, not the Rowset when accessing the information from within the excel spreadsheet.

[ehersh]

OK changed it to the following with the same issue

[code<forEachLoop itemsVariable="${newrow}" currentItemVariable="currentfile">[/code]

[ehersh]

I am trying to just create a file list using the row parameter

Code: Select all

---

<readExcel inputFile="resource:smb://ScionFS01_Groups_CIS/Eligibility/Test_Files/transfer.xlsx" outputRowSetVariable="newrow" dataStartRowNumber="2" headingsRowNumber="1" version="2.0">
         <data>
            <column index="3" name="column filename" />
         </data>
      </readExcel>


      <createFileList fileListVariable="filelist" numFilesFoundVariable="filecnt" version="1.0">
         <fileset dir="resource:smb://Groups/tes/test_files">
            <wildcardFilter />
            <regexFilter>
               <include pattern="${newrow[3]}_1_4122013527" />
            </regexFilter>
            <dateFilter />
            <sizeFilter />
         </fileset>
      </createFileList>

and I get the following error


4/4/17 2:39:25 PM ERROR Invalid cursor position: BEFORE_FIRST
Original expression: newrow[3]. Full stack trace written to '1397290123806_error_1.log'
4/4/17 2:39:25 PM INFO Finished project 'test Claim test_TRANSFER'
4/4/17 2:39:25 PM ERROR [8099 - Create File List] An unexpected error occurred.
Invalid cursor position: BEFORE_FIRST
Original expression: newrow[3].

[ehersh]

I am looping through the file list and I am getting an error. Below is my code.

Code: Select all

---

<project name="test file transfer" mainModule="Main" version="2.0" logLevel="debug">
   <description>This job will read the test_file.xlsx spreadsheet customer column and load to file list</description>

   <module name="Main" logLevel="debug">

      <createWorkspace version="1.0" disabled="false" />


      <readExcel inputFile="resource:smb://TEST_Groups_CIS/files/Test_Files/test_file.xlsx" outputRowSetVariable="newrow" dataStartRowNumber="2" headingsRowNumber="1" version="2.0">
         <data>
            <column index="3" name="column filename" />
         </data>
      </readExcel>


      <createFileList fileListVariable="filelist" numFilesFoundVariable="filecnt" version="1.0">
         <fileset dir="resource:smb://TEST_Groups_CIS/files/Test_Files">
            <wildcardFilter />
            <regexFilter />
            <dateFilter />
            <sizeFilter />
         </fileset>
      </createFileList>

      <forEachLoop itemsVariable="${filelist}" currentItemVariable="currentfile" currentIterationVariable="loops" disabled="false">

         <copy label="Copy to SFTP folder" sourceFilesVariable="${currentfile}" destDir="resource:smb://TEST_Groups_CIS/files/Test_Files/prod" version="1.0" disabled="false">
            <fileset dir="resource:smb://TEST_Groups_CIS/files/Test_Files">
               <wildcardFilter />
               <regexFilter>
                  <include pattern="${newrow[3]}_1_4122013527" />
               </regexFilter>
               <dateFilter />
               <sizeFilter />
            </fileset>
         </copy>


         <copy label="Copy to Archive" destDir="resource:smb://TEST_Groups_CIS/files/Test_Files/archive" preserveDate="true" destinationFilesVariable="copied_file" version="1.0" disabled="false">
            <fileset dir="resource:smb://TEST_Groups_CIS/files/Test_Files">
               <wildcardFilter />
               <regexFilter>
                  <include pattern="${newrow[3]}_1_4122013527" />
               </regexFilter>
               <dateFilter />
               <sizeFilter />
            </fileset>
         </copy>


         <delete file="${currentrow}" version="1.0" disabled="false" />


         <print version="1.0" disabled="false">
            <![CDATA[${loops}]]>
         </print>


         <deleteWorkspace version="1.0" disabled="false" />

      </forEachLoop>
   </module>

</project>


The error message is Project '/Test Projects/test/customers/test file transfer' failed. Job number is '1397290139575'.
[8099 - Copy to SFTP folder] An unexpected error occurred. Invalid cursor position: BEFORE_FIRST Original expression: newrow[3].