Compare data from database and other table

[PALKUMARI_PATEL]

Hi ,

Need urgent help on this : Compare part of the string (Changes each of the time) and rest of the string stays as it is

My team is working on to create a query for getting the records . So What I need in query is something I’ve confusion.

I have Excel records (Invoice numbers, Some text , Amount ) that have to match on Website and if match then have to attach one file on website along with that invoice number .

Query needs for find file name on shared drive.
File name would be ABC123456 898989(123456 is the last 6 digit of some fields for particular invoice, 898989 is the tracking number)

So, how should I pass the query for each invoice ? (Inside For each activity ? ) or what exactly I need for that ?

Current query returning Invoice number , ABC123456, 898989 .

[Lak_Ui (Lakshmi)]

If i understand correctly based on the invoice number in the excel you will have to find the appropriate file to upload.Right?

[PALKUMARI_PATEL]

Yes, Correct.

Just have to find correct file name by using the particular code for each invoice.

[PALKUMARI_PATEL]

@Nithinkrishna Can you help me here ?

Let me know if you need further details on this.

[Lak_Ui (Lakshmi)]

use for each to loop through the excel data to get the invoice

var = from file in Directory.GetFiles(directory path)
let fileName = Path.GetFileName(file)
where fileName.Contains(invoice number fetched from excel datatable )
select fileName.SingleOrDefault();

Try this

[PALKUMARI_PATEL]

Can you send me an sampleexample ?

I got the file names from Sahred drive.
Now how to compare the file name from excel for particular invoice and from sQL’s column 2 (File_Name) ?

Can you send me an sample file ?

[Lak_Ui (Lakshmi)]

@Lak_Ui

var = from file in Directory.GetFiles(directory path)
let fileName = Path.GetFileName(file)
where fileName.Contains(invoice number fetched from excel datatable )
select fileName.SingleOrDefault();

you can use this code for the same for directory path use the shared drive path.
if you are using database. use execute query activity to get the table data as datatable. Loop through the datatable and use row(“Invoice number”) to get the invoice

[PALKUMARI_PATEL]

I a already loping through Each row of Excel Column (Invoice), So, have to find the file for that invoice.

I am confused here .

[Nithinkrishna]

Hey @PALKUMARI_PATEL

Able to figure this out ?

Thanks
#nK

[PALKUMARI_PATEL]

Not yet. Would you mind to share an sample file ?

I am looping through each invoice (For each invoice row in Excel database)
I have SQL Data COlumn 1 has Invoice number , Column 2 has file name (Some may be blanks)

Now not able to achieve below step,
Get the file name for particular invoice and check if it is available on shared Folder ?

Can you help me here with an example?

[Nithinkrishna]

@PALKUMARI_PATEL

Get the file name for particular invoice

From where you are trying to get this please ?

[PALKUMARI_PATEL]

I’ve have to look into Database’s data and have to get the name if available .
IF I have file name from database then I have to check in my shared drive if it is available? and need to proceed from there.

[Nithinkrishna]

So you are looking for a SQL query ?

[PALKUMARI_PATEL]

I have SQL Query. I have fetched SQL data. Now I have two columns as output of SQL Data.

How should I use that to find the filename for particular invoice number that I am using from Excel ?

[Nithinkrishna]

Hey @PALKUMARI_PATEL

You can use Filter Data Table activity!

Thanks
#nK

[PALKUMARI_PATEL]

@Nithinkrishna Filter data table for what ?

I used filter data table to filter Excel file because I have to grab only some of the invoices based on some condition.

[Nithinkrishna]

You need to do the same in the DB as well right ?

[PALKUMARI_PATEL]

No.

Can you just check the question ? I explained what I want to achieve here and let me know what you need .

Thanks