Problem with Big Excel data to Vlookup

[ALALUN]

Hi, I have two Excel "book1.xlsx" and "book2.xlsx", book1.xlsx have 1,000,000 rows and 10 columns, book2.xlsx have 100,000 rows and 5 columns, I need to used two Excel match the data each other, but the data is too big, can not read a collection, it will out of memory.

I can not try to OLEDB object, because it was two workbooks, I don't know how to write the SQL.

I tried to used Excel vlookup function, but is too slow.

I tried to Import two Excel data to Access and SQL execute, but the Excel data is Mixed digital text every column, if data vaule is not text in the first 8 rows, it will miss the text value of the rest data.

I was wondering if there was a solution？

 

[ALALUN]

Anybody had a solution?

 

[punitbirla]

can you give more detail on what exactly you want to achieve with the data in excel book1.xlsx and book2.xlsx. If something you can do is to try to create an excel macro (.xlsm) and write a vba code which will process data in both the excel files. That excel macro you can automate using BP.

 

[ALALUN]

can you give more detail on what exactly you want to achieve with the data in excel book1.xlsx and book2.xlsx. If something you can do is to try to create an excel macro (.xlsm) and write a vba code which will process data in both the excel files. That excel macro you can automate using BP.

hi, Thank you for your reply.
I am sorry to the Excel is too large so that it upload fail, just the png.
you can see the two Excel kkk.xlsx and k.xlsx. I need to use kkk.xlsx Sheet1 Column "Text2" to vlookup k.xlsx Sheet2 Column "Text2", and output to a BP Collection.

yes, I can create an excel macro to do that, but if I can just used BP, I thank it's perfect.

Can you understand what I said?

 

[punitbirla]

hi, Thank you for your reply.
I am sorry to the Excel is too large so that it upload fail, just the png.
you can see the two Excel kkk.xlsx and k.xlsx. I need to use kkk.xlsx Sheet1 Column "Text2" to vlookup k.xlsx Sheet2 Column "Text2", and output to a BP Collection.

yes, I can create an excel macro to do that, but if I can just used BP, I thank it's perfect.

Can you understand what I said?

I will suggest you to create an excel macro which will import only required columns from the 2 files and then apply vlookup formula in another sheet in the macro.
Now in BP you can open the macro and run the vba methods. after its completed you can read the lesser columns from BP. it wont hang.

 

[gzmatt]

I will suggest you to create an excel macro which will import only required columns from the 2 files and then apply vlookup formula in another sheet in the macro.
Now in BP you can open the macro and run the vba methods. after its completed you can read the lesser columns from BP. it wont hang.

Totally agree with this approach. I had experience to handle the big excel file in BP. Finally I gave up and go to use VBA