copying files based on rowset

ehersh: Posts: 56; Joined: Mon Sep 26, 2016 12:50 pm

by ehersh » Tue Mar 07, 2017 10:56 am

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.

Re: copying files based on rowset

Support_Rick Support Specialist

Posts: 592

Joined: Tue Jul 17, 2012 2:12 pm

Location: Phoenix, AZ

by Support_Rick » Tue Mar 07, 2017 11:00 am

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

Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

Re: copying files based on rowset

ehersh: Posts: 56; Joined: Mon Sep 26, 2016 12:50 pm

by ehersh » Fri Mar 10, 2017 12:08 pm

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="<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>

Re: copying files based on rowset

ehersh: Posts: 56; Joined: Mon Sep 26, 2016 12:50 pm

by ehersh » Tue Apr 04, 2017 8:25 am

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="<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

Re: copying files based on rowset

Support_Rick Support Specialist

Posts: 592

Joined: Tue Jul 17, 2012 2:12 pm

Location: Phoenix, AZ

by Support_Rick » Tue Apr 04, 2017 9:00 am

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

Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

Re: copying files based on rowset

ehersh: Posts: 56; Joined: Mon Sep 26, 2016 12:50 pm

by ehersh » Tue Apr 04, 2017 9:44 am

OK changed it to the following with the same issue

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

Re: copying files based on rowset

ehersh: Posts: 56; Joined: Mon Sep 26, 2016 12:50 pm

by ehersh » Tue Apr 04, 2017 2:46 pm

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

Code: Select all

<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>

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].

Re: copying files based on rowset

ehersh: Posts: 56; Joined: Mon Sep 26, 2016 12:50 pm

by ehersh » Fri Apr 07, 2017 1:28 pm

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="<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].

Re: copying files based on rowset

ehersh: Posts: 56; Joined: Mon Sep 26, 2016 12:50 pm

by ehersh » Mon Apr 24, 2017 11:58 am

I was able to get back to this and I think I figured it out so thought I would share. If anyone has any suggestions of a better way to accomplish this I would be excited to hear about it.

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.

Code: Select all

<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>