Easy Populate CSV and Excel: how do I update all my product data quickly?

[wmorris]

Hi,

I am using Easy Populate csv and I converted it over to Excel.

The one I downloaded from my site has 430 items.
My actual warehouse has over 2,000 items.

I have to type each item number to get the qty. It takes forever.

What is the best way on Excel that the downloaded spreadsheet from my database can only pull the item numbers and its quantities from my warehouse spreadsheet all in one click?

[mc12345678]

I believe there is a bit of a mix up in terminology with regards to this issue, besides that the issue is more about how to use Excel than it is about a ZC specific problem, but let's see what can be done to at least clarify the issue.

In the end of things, you have two Excel spreadsheets each representing some product info. They both have a common field with the other (primary key) and you want to pull information from one spreadsheet into the other spreadsheet for the matching "product".

This is a perfect reason to use the excel function "vlookup" the criteria to use that function is that the column to be looked up needs to be the left most column of a range of cells that includes your primary key and the column to be returned back and the other is that the primary key needs to be unique for each row and sorted. If not unique then just the first row is returned. For stock quantities that's not an issue provided that the duplicate primary key represents a linked product and not some different product.

Anyways, let's say you want to update your store (small list) using the values from your warehouse (large list), then in the column for quantity (or for testing create a column adjacent to that), you would enter in an equation like:

=vlookup($A2,'otherspreadsheet'!$A2:$Z431, 3)

What this does is use the value in cell A2 (first column, second row which is where one might expect the primary key to be with the first row being the field headers) and compare/search the column A of the other spreadsheet for the value that matches the value in A2 of the current sheet. If it is found, then it will use/return the data from the 3rd column of the column range of A-Z. Z was arbitrarily chosen and the bottom of the range of 431 was chosen to be the 430 products mentioned above plus the first row as a header.

Anyways, if the warehouse quantity is in fact in that 3rd column, then it would be represented in this cell with the equation. If the equation works, then copy and paste it down for the remaining rows. Then copy and paste special (values) to lock in the numbers for saving the spreadsheet.

Hopefully that helps and I didn't bark up the wrong tree, kind of took a shot at what I understood based on the provided information.

[mc12345678]

Ohh, as for a "one-click solution"? Probably need to repeat the process recording the actions as a macro that then may need a little modification so that it could work each and every time regardless of the size of the table(s), though the filename action may be a slightly different story having to choose from either always using a specific filename or being asked to identify the file(s) to use for this "comparison"/upgrade.

[wmorris]

Ohh, as for a "one-click solution"? Probably need to repeat the process recording the actions as a macro that then may need a little modification so that it could work each and every time regardless of the size of the table(s), though the filename action may be a slightly different story having to choose from either always using a specific filename or being asked to identify the file(s) to use for this "comparison"/upgrade.

Ok, EP4 (Easy Populate)v4 ZC in Excel (Item#)Column A (Qty)Column T
My main warehouse in excel (Item#)Column A (Qty)Column J

[torvista]

one-click solution

How about a no-click solution?

For taking multiple sources of data and doing absolutely anything you can think of with any data in any columns/fields to produce some other file or even stuff it straight into another database, I use Pentaho Data Integration (Kettle) Community.

Can be a pain to get working, but worth the learning curve, it's free and frankly flipping awesome, does anything to anything, from any source to any destination.
Once you have created the job/data transformation, you can make it auto-running/on a schedule.

[wmorris]

How about a no-click solution?

For taking multiple sources of data and doing absolutely anything you can think of with any data in any columns/fields to produce some other file or even stuff it straight into another database, I use Pentaho Data Integration (Kettle) Community.

Can be a pain to get working, but worth the learning curve, it's free and frankly flipping awesome, does anything to anything, from any source to any destination.
Once you have created the job/data transformation, you can make it auto-running/on a schedule.

I am sorry it doesn't work for me.

[mc12345678]

I am sorry it doesn't work for me.

For clarity, do you mean that having that other software and that it's process is something that couldn't fit your goal or do you mean you had difficulty getting it to work?

[wmorris]

For clarity, do you mean that having that other software and that it's process is something that couldn't fit your goal or do you mean you had difficulty getting it to work?

Actually I got it to work. Thanks Anyways.

[wmorris]

For clarity, do you mean that having that other software and that it's process is something that couldn't fit your goal or do you mean you had difficulty getting it to work?

Well I have an issue.

I downloaded my InventoryProductStatus in Excel. Then I open an CSV editor. I managed to get my Item numbers pulled for the quantities. using the =vlookup in excel then I copied the changes from excel to the CSV editor then saved it then uploaded to Easy Populate 4 and see any changes and there were no change in the Admin.

[wmorris]

Well I have an issue.

I downloaded my InventoryProductStatus in Excel. Then I open an CSV editor. I managed to get my Item numbers pulled for the quantities. using the =vlookup in excel then I copied the changes from excel to the CSV editor then saved it then uploaded to Easy Populate 4 and see any changes and there were no change in the Admin.

Nevermind I got it figured out. I forgot import it into the Database.