Importing from spreadsheet to MySQL

[nomadsoul]

I'm tasked with a hypothetical challenge and I'd like to know if it can be done. And if it can, I'd love some ideas (pseudo code is ok) of different ways I could do this maybe the solution could benefit others as well:

Imagine that there are 3 companies selling produce:

 

Company 1

Company 2

Company 3

 

Each of these companies are tracking the price and types of products they are selling.  Prices change each day so our website is asking each company to upload the type and  price of product each day -so buyers can compare prices.

So, imagine the seller will be greatly inconvenienced to try to log into the website and update the prices manually (they may have thousands of products).

If they are using their own automated spread sheet, then they could simply upload the excel sheet, and the website will parse through the existing list, compare with the old list (already in the website) and sync the prices and products that the seller has in their own BI software.

In this method, the website uses 1 name to describe each product.. and forces whatever name each of the various sellers use as a description, be mapped to the website common name (in the Mysql table or a master list?).  The buyer will be trying to compare a produce item, and if all the names are different, then it wouldn't work.

 

 

Company 1 sells  Granny Smith medium apples for .99 cents a pound

Company 2  Medium Green Granny apples for 1.05 a pound

Company 3  sells Green Granny apples for .85 cents a pound

 

Now.. the website lists that product as    Granny Smith Medium Apples  ...but they are the same apples.

 

Buyers will look at the site and find  Granny Smith Medium Apples  and want to see the pricing, but since each seller uses a different name.. we need each of those names to be mapped to our common name (Granny Smith Medium Apples).

 

Logging in and updating with a php form is not an option because the update has already been done for the company on the spreadsheet, we don't want to ask the customer to update everything again on the website because the customer may have thousands of products.

I think I might be overanalyzing the situation and there is probably a simple solution. 

I also thought of providing a spreadsheet template for each company with names matching the Mysql table

but as I mentioned these companies may have thousands of products. Names can be added and dropped or changed.

Maybe I could join 3 tables: MasterList, ImportedList, and UpdatedList, using diff, REPALCE or some regular expressions?

Like I say if you have some ideas, pseudocode is ok but if you know of any scripts related to this task let me know.

 

 

 

 

 

 

 

[fenway]

This isn't a new problem -- but there's no reliable solution.  At some point, you're going to have to create a mapping from companyN to your internal naming convention for each product X -- there's really no way about it.

 

If N is small, then even if X is large, deltaX -- the number of products that (a) change names or (b) are added [deleted doesn't count] -- is likely to be small as well.

 

The initial mappings will be painful -- but a multi-pass approach should reduce the workload by about 95%.  Afterwards, it's a one-off, which is easy to get a client to do. 

[nomadsoul]

Thanks fenway,

I suspected it would be this complex

[Christian F.]

It can be a very complex operation indeed, or it can be a moderately complex one. Depending upon the source lists, and how similar they are.

Speaking from personal experience with writing just such a system, you'll need to plan it out quite thoroughly before you start any code. Including considering future changes to the price lists, and how that will affect the system.

 

The worst part is to prevent false positives, and ensure that all true matches are found. As fenway stated, this initial mapping can be quite painful indeed. (Especially when working on price lists with about 3k items each. :'()