Vlookup a HUGE database

I need to use the Vlookup (or Lookup) function. Heres my problem though...my database is 700,000 entries, WAY more than the Excel limit of 65,500...so I cant open my database in a single worksheet in Excel.

I know everyone is thinking "Access", but I have no clue how to use Access and I need this by Saturday.

Basically, the database contains 3 columns:

UPC | Unit | Item Description

For example:

070137100188 | 8.3 oz | Redbull Energy Drink
025984345265 | 12 oz | Buweiser 12-pack
Repeat 700,000 times...

I have an Excel file that has all the UPCs in the A column, and I need to add the item description + unit into column B = Redbull Energy Drink (8.3 oz)

So is there a way to split the database over several worksheets, and use Lookup to search several tables instead of 1? Or do you guys recommend another way of doing it?

The database file is a CSV file, formated like this= 070137100188,8.3 oz,"Redull Energy Drink"


Thanks!

Hi

Its possible to use a closed csv file as a database for excel. In the attached file I've created a small csv file. Put that file into c:\temp. Note that it has headings (UPS, Sizee, Description).

Now open the spreadsheet, and run the macro. It will complete columns B and C with the data from the csv file.

If this works OK, then you could modify your csv file to have headings as above, then bring in your UPC codes into column A of the spreadsheet, modify the code to show the new csv file name, and see how it goes.

I've not done anything to cover missing UPC codes from your csv file at this stage.

rylo

Works great. Now how can I change it so that it only copies only 1 row of data.

The CSV file is now:

SKU,Item


So I want to lookup the SKU and copy over the Item

Not sure I follow.

Are there multiple items for each code? If so, then how do you know which one to bring in? Or are they the same?

Or do you mean that you only want to bring back 1 column of data for each item? If so, then revise the select statement in the code to only select the 1 column you want.


rylo

Ok, i editted the select in the vba and it works...

however, excel keeps freezing. I ran the code and left it alone all night. This morning, Excel was frozen. The CSV is of about 1 million items and the SKUs inside the xls file are 9,000.

Can you help me?

Hi

A couple of thoughts.

1) Try cutting down the number of items from 9000 to blocks of about 1000 and see if that gets it done.
2) Add a counter to the code and have it going to the status bar to see if the thing is really stuck, or just processing slowly.

As you have now blown your Saturday deadline, and the volumes are so large, it may be better to work out how to do this in access. Be much easier.


rylo

Hi Rylo,
I guess I'll be the first to thank you for your kind help.
This is *exactly* the issue that I have. I think Djvice was working with the same UPC db file that I dl from the web, too. I loaded the files in the zip here and got the lookup macro to work fine.

But then I tried with my data file. The dl data file "items.csv" has 1,048,571 rows of data. I renamed djvice.xls to mylookup.xls. I edited the macro to this

Please Login or Register  to view this content.

where items.csv is my data file. For the header, in the first three cells of the first row of this data file I have inserted "UPC", "size" and "description". When I run the macro I get this error message from MS VB:

Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed

Help, please. TIA

Leafgreen

Can you please review the forum rules, especially rule 2 about hijacking another post for you own.

Can you please create a new post, and post a link to this post as a reference.

I've locked this post.

rylo