Read Excel FIle then Move Files inside Folders

Help
1

Hey guys.

I have an excel which contains invoice numbers and types “Credit Invoices” and “Sales Invoices”.
I have a folder that contains all the invoices but I need to sort them into the correct folders.

I need a robot to

  1. Read the excel file,
  2. Look into the folder, find the invoice then move it to a specific folders ie “Credit Invoices”

Can someone please help me understand how to read the excel, store that info, then tell the robot to move the files.

Thanks in advance.

2

@Savvas_Peter Is there any specific name for the invoice pdf?

3

Hey no the files are not in PDF all invoices are Excel files.

We have the file name in the Excel summary which shows the invoice type ie Credit or Sales invoice.
So we know the file names in excel we then need the robot to store the info and then move the invoice into a specific folder.

4

Hey please see Attachment with screenshotsMoving Files using Excel.docx (229.3 KB)

5

@Savvas_Peter Could you please provide a sample for invoice?

6

DEAL_SHEETS_REPORT_A08659_06092020.xls (1.4 KB)

7

@Savvas_Peter
Sorry for the delay , Do you get the solution?

8

If the invoice type is part of the name, then check if file name contains keyword(if filname.Indexof(“keyword”)), then move file to path. Use move file activity.

9

No solution yet… DId you have nay more guidance for me?

10

@Savvas_Peter
you can read an Excel using the Read Range activity
and move a file using the Copy activity :smiling_face_with_three_hearts:

11

@Savvas_Peter Yes, I got it

  1. We need an invoice name therefore I’m using String manipulation

    invoice name = Dell_Service_A56778_67809
    Use Assign activity

    //get invoice system path
    InvoiceList = Directory.GetFiles(Path)
    Path=Enter folder path where all the invoices are present
    O/p= c://documents/invoiceList/Dell_Service_A56778_67809.csv

    //system path will replace with “” . So we’ll get
    Dell_Service_A56778_67809.csv
    FileName=item.ToString.Replace(Path,“”)

    //Excel sheet has Dell_Service_A56778_67809 data so we need to remove an invoice extension

    File=FileName.Replace(“.csv”,“”)
    O/p=Dell_Service_A56778_67809

  2. Use Excel application scope and read range activity to read the excel sheet data

  3. Use if condition activity to compare invoice name and the row data which has invoice name in the sheet

  4. if the row data and invoice file name matches then move the file in a particular folder for that use Move file activity.

Note: Instead of String manipulation you can use two lines of .net code

Hope this will help you to complete this task

Best Regards,
Vrushali

12

Hey do you have time for a teams meeting or skype?
I am having a few issues.

13

@Savvas_Peter Yes,why not but before that could you tell me where you got stuck ?

14

@Savvas_Peter If you require workflow for this let me know I’ll share with you.

15

A workflow would be greatly appreciated!

16

@Savvas_Peter Here it is
Workflow :
InvoiceSorting.xaml (14.2 KB)

Invoice Folder:

invoiceListSSForum
invoiceListSSForum1004×384 5.85 KB

Excel Sheet Invoice name:

excelsheetInvoiceForum
excelsheetInvoiceForum701×314 6.49 KB

Hope this will help you

Best Regards,
Vrushali

17

Thanks but I cant open the file.

18

error
error748×348 12 KB

19

@Savvas_Peter This happening due to dependency.
Try to reopen it.

20

Hey I tried to reopen but it wont work, could you send me screenshots so I could see how you entered it?