Question

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?

  • 27 November 2023
  • 16 replies
  • 144 views

Userlevel 3
Badge +10

For example, there is Price and OrderID in sheet1. if i find the same price and order id in a row in sheet2, i need to get that row, and copy some cells into sheet1 opposite of the related row.

I was trying to do with ExcelAdvanced, but it took more 17 hours for bot finishing running.

So i am trying to find faster way. 


16 replies

Userlevel 7
Badge +13

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. 
 
Userlevel 3
Badge +10

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

Userlevel 7
Badge +13

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?

 
Userlevel 3
Badge +10

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

Userlevel 7
Badge +13

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.

 
Userlevel 3
Badge +10

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

Userlevel 7
Badge +13

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?

 
Userlevel 3
Badge +10

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

Userlevel 3
Badge +10

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

 

Userlevel 7
Badge +13

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

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

Userlevel 3
Badge +10

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

Userlevel 7
Badge +13

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. 

 

 

 

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

 

 

 

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
 
 
Userlevel 3
Badge +10

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.

Userlevel 7
Badge +13

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

 
Userlevel 3
Badge +10

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.

Userlevel 7
Badge +13

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.

 
 

Reply