copying files based on rowset
- Support Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
Loop through the Rowset and retrieve the customer number
Copy (Create file list) based on Customer number (Filter = *CustomerNo*)
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
<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="<path to>/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="<path to>/Test_Files/archive1" version="1.0" disabled="false">
<fileset dir="<path to>/Test_Files">
<regexFilter>
<include pattern="${newrow[2]}" caseSensitive="false" />
</regexFilter>
</fileset>
</copy>
</forEachLoop>
<deleteWorkspace version="1.0" />
</module>
</project>
<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="<path to>/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="<path to>/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 Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
<readExcel inputFile="<path to>/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>
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].
<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="<path to>/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="<path to>/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="<path to>/files/Test_Files/prod" version="1.0" disabled="false">
<fileset dir="<path to>/files/Test_Files">
<wildcardFilter />
<regexFilter>
<include pattern="${newrow[3]}_1_4122013527" />
</regexFilter>
<dateFilter />
<sizeFilter />
</fileset>
</copy>
<copy label="Copy to Archive" destDir="<path to>/files/Test_Files/archive" preserveDate="true" destinationFilesVariable="copied_file" version="1.0" disabled="false">
<fileset dir="<path to>/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].
I used a nested for each loop. the outer loop will go through the file list. So for each file in the file list the inner loop will go through each row in the row set. As it goes through each row it will do an if statement. If the current file in the outer loop contains the value from the inner loop it copies then deletes the file, if not it exits the if statement.
<project name="newtest_FILE_TRANSFER" mainModule="Main" version="2.0" logLevel="debug">
<description>This job will read the test.xlsx spreadsheet customer column and load to file list</description>
<module name="Main" logLevel="debug">
<createWorkspace version="1.0" disabled="false" />
<readExcel inputFile="<path to>/Test_Files/test.xlsx" outputRowSetVariable="newrow" skipInvalidRecords="true" skipEmptyRows="true" dataStartRowNumber="2" headingsRowNumber="1" sheetNames="sheet1" processedInputFilesVariable="rowdata" version="2.0">
<data>
<column index="3" name="payee" />
</data>
</readExcel>
<createFileList fileListVariable="filelist" numFilesFoundVariable="filecnt" version="1.0">
<fileset dir="<path to>/test_files">
<wildcardFilter>
<exclude pattern="*.xlsx" />
</wildcardFilter>
<regexFilter />
<dateFilter />
<sizeFilter />
</fileset>
</createFileList>
<forEachLoop itemsVariable="${filelist}" currentItemVariable="currentfile" disabled="false">
<forEachLoop itemsVariable="${newrow}" currentItemVariable="currentrow" currentIterationVariable="loop" currentIndexVariable="test" disabled="false">
<setVariable name="customer" value="${newrow[3]}" version="2.0" disabled="false" />
<if condition="${Contains(String(currentfile), customer)}">
<copy sourceFile="${currentfile}" destDir="<path to>/test_files/prod" version="1.0" disabled="false" />
<delete inputFilesVariable="${currentfile}" version="1.0" disabled="false" />
</if>
</forEachLoop>
</forEachLoop>
<deleteWorkspace version="1.0" disabled="false" />
</module>
</project>