I have two excel sheets both have more than 40000 rows.. sheet1 has 8 columns, sheet2 has 16 columns. Based on values of two columns in sheet1 I need to get corresponding rows from sheet2. Which package would work best?

Hi @Semih 3318 ,

You can use either of the following method in your case for a faster execution.

1. Use the Data Table package.
2. By using the Database package, connect to the excel file as database. 

Hello @Padmakumar 

I use excel with two sheets, i get them as data table, lets say dt1 and dt2

I add DataTable:Join action, there is column named Siparis Numarasi, i took it as that, and also tried [Siparis Numarasi], but it gives error..

What error you are getting?

it says Cannot find column with name "[Siparis Numarasi]" in data table. Please check whether the column name is valid. If the problem persists, please contact your System Administrator

When i look inside of the cell its written as Siparis Numarasi , with 1 space

when i copy the header cell its written as Siparis  Numarasi with 2 spaces.

Anyway i tried both, and with brackets, still i get the same  error

Can you rename just that column by removing the Space in between and try? Also, please check what is the first record you are getting while iterating. I seriously doubt that it might have taken the Column headers as the starting row.

Now it throws error for dt2

Cannot find column with name "[ORDER_ID]" in data table. Please check whether the column name is valid. If the problem persists, please contact your System Administrator

I wrote both ORDER_ID and also with brackets, it gives that error

Was the previous error from dt1? Have you tried displaying the first record of the table?

The first error was with dt1 yes, when i updated the header like SiparisNumarasi, it didnt give error,

but dt2 give error which i tried to join dt1 SiparisNumarasi and dts ORDER_ID

@Padmakumar since i couldnt get it work, i tried excel as database,

SELECT Sheet1.*, Sheet2.* FROM Sheet2 INNER JOIN Sheet1 ON Sheet1.SiparisNumarasi = Sheet2.ORDER_ID AND Sheet1.[Islem Tutari] = Sheet2.TRX_AMOUNT

i get an error

[Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'Sheet2'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet2' is not a local object, check your network connection or contact the server administrator.

Can you rename it as OrderID and give it a try?

i got error Cannot find column with name "SiparisNumarasi" in data table. which was about dt1

its weird, i did not understand.

and also i am not sure if this would be correct even though if it worked

Cause i need something like in my last question which is  related to database, i need to check two columns, and get the related row, to the sheet1

What can I do? database doesnt work either

Seems weird for me too. You can check the data table variable value through the Debug option and see the value it is getting before going to the loop. I still don't think it is correctly picking the Column headers. 

This SELECT query won't work. You can try separating them by providing separate session names or try like below.

Note: In this example, the sources are two different excel files.

SELECT *
FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a
LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b
ON a.col2 = b.col2

I wrote l’ke this 

SELECT * Sheet1.*, Sheet2.* FROM "C:\RPA\VPos\BankalarVPOS_output\BankalarVPOS.xlsx"S "Sheet1$$" a INNER JOIN "C:\RPA\VPos\BankalarVPOS_output\Valor_Report_13112023.xlsx" "Sheet2$$" b ON a.[Siparis Numarasi] = b.ORDER_ID AND a.[Islem Tutari] = b.TRX_AMOUNT

error 

[Microsoft][ODBC Excel Driver] Syntax error in FROM clause.

SELECT * FROM 'C:\RPA\VPos\BankalarVPOS_output\BankalarVPOS.xlsx'.'Sheet1$$' a LEFT JOIN 'C:\RPA\VPos\BankalarVPOS_output\BankalarVPOS.xlsx'.'Sheet2$$' b ON a.[Siparis Numarasi] = b.ORDER_ID

it says [Microsoft][ODBC Excel Driver] ''C:\RPA\VPos\BankalarVPOS_output\BankalarVPOS.xlsx'' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

If it is possible, could you please provide the copy of this file with some sample data? So that, I can try it out from my end and gets back to you.