Remapping using php and csv files

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
makenoiz
Forum Newbie
Posts: 3
Joined: Fri Nov 25, 2011 1:23 pm

Remapping using php and csv files

Post by makenoiz »

I'm an intermediate php coder however I have a new situation. I need to do some remapping of categories from CSV files we get from our supplier for our php ecommerce store. We use mySQL back end.
We are importing data from a Supplier and need to remap the category names for our online store. There are 16,000 entries in the CSV file.

This is what I need to do.

1) grab the CSV from the suppliers (from their ftp location)
2) remap the suppliers category names on the acquired CSV to our stores category names. I can either create a new table with the category name relation ships (probably best?) or read my mapping CSV ?
3) save the new CSV to our server where our auto import program can grab it.

Eventually, Id like a cron to run the new script but for now Ill do it manually during testing period.


Thats it! but... as simple as it sounds, I dont know how to start this.

Below is an example of what needs to be done just to clarify.


[text]Our Category(A) | Their Category(B) | Product (C) | Product Cat (D) | New Product Cat (E)
1. dog/leash/long | Pet/walking | Item A | Pet/sleeping|bed | ** This is what we need to get**
2. dog/leash|long | Pet/walking | Item B | Pet/walking
3. dog/leash|long | Pet/sleeping bed | Item C | Pet/walking[/text]


Here is my pseudo code:

Code: Select all

for each value in Column D i=1++(run the test on each value in Column D)
  for each value in B  i=1++ ( check each value in Column B)
     if B(i) = D(i) then E(i) =A(i)
  end for
end for

The new path in this example for Item A would be A3 and would be written to E1.

I have 15,000 entries to do.

Thank you for any help
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Remapping using php and csv files

Post by thinsoldier »

I'm gonna go out on a limb and suggest importing the csv data to another table then using sql JOIN queries to compare values. It would also allow you to perform other useful queries later if necessary. Also mysql can probably compare the values and find matches must faster than php.
Warning: I have no idea what I'm talking about.
makenoiz
Forum Newbie
Posts: 3
Joined: Fri Nov 25, 2011 1:23 pm

Re: Remapping using php and csv files

Post by makenoiz »

Yup - I did it an still stumped. Im not sure how to handle the concatenated values and thought I should use php ...

I have 2 mysql tables now: one contains category mapping relationships between a supplier and our store: Basically what we call their categories eg ~ denotes sub category level:

Cateogry Mapping Relationship Table

Supplier Cat..........| Our Cat.....
dogs~leashes~long.....| pets~walking
dogs~leashes~long.....| pets~travel
dogs~leashes~short....| pets~walking
dogs~leashes~nylon....| pets~walking
dogs~feeding .........| pets~feeding

the other table contains supplier item ids with the categories that the supplier has the products in. Multiple categories are concatenated in the same field with a ','.

Such as the following:

Supplier Item Table

Supplier item ID...| Supplier item Categories
28374 ............| dogs~leashes~long,dogs~leashes~nylon


Result desired is the new item Category as related to the Supplier item.
Supplier item..| Supplier item Categories ..............| New item Categories
28374 ........| dogs~leashes~long,dogs~leashes~nylon ..|pets~travel,pets~walking

Im not sure how to handle the concatenated field and I would appreciate any help at all

thank you
makenoiz
Forum Newbie
Posts: 3
Joined: Fri Nov 25, 2011 1:23 pm

Re: Remapping using php and csv files

Post by makenoiz »

Ok the MySQL guys have suggest I do it in php due to the concatenated values in the Supplier item Categories. So I'm trying to work through the code right now. I'm just not sure how to handle the concatenated field.
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Remapping using php and csv files

Post by thinsoldier »

Can you post what their actual concatenated values look like?
What's the value separator?


You could use php to explode their concat values then still save those values in a mysql table for simple querying later.

I'm pushing the mysql direction because I very recently had to do something similar and found that saving the csv to mysql allowed answer more than half of my questions about the data quickly and then painstakingly dig through and compare values to solve the rest of my problems in php.

They probably suggested dealing with the concat values in php because to do it in mysql you might wind up using the LIKE command or something else they consider academically to be "slow".
Warning: I have no idea what I'm talking about.
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Remapping using php and csv files

Post by thinsoldier »

makenoiz wrote: the other table contains supplier item ids with the categories that the supplier has the products in. Multiple categories are concatenated in the same field with a ','.
I'd probably convert their csv data to a collection of properly normalized tables.

So there'd be tables like:
_importItems
_importCategories
_importItems_importCategories
^ this table would contain just 2 columns. 1 is `item_id` of imported supplier item records and 2 is `cat_id` of imported supplier categories assigned to the corresponding item.
^This is what your comma separated list gets turned into, 1 record per id in that list.

So if the csv data had a item of "blue diamond leash" with like 6 categories assigned to it, it might look like this:
"522","blue diamond leash","catA|catB|catC|catD|catQ|catY","foo","bar","baz",....etc

I'd insert the id (522) and name and any other single values into the _importItems table.
Then, using php, I'd split all the categories into individual entries in the _importCategories table.
Then I'd insert into the cross-reference table records that look like this:

$sql = "INSERT INTO _importItems_importCategories SET item_id=522, cat_id = ". getIDofForeignCat( "catA" );

this will result in records like:
522 | 1
522 | 2
522 | 3
522 | 19
522 | 21
523 | 1
523 | 17
524 | 1
524 | 3
524 | 19
525 | 2
525 | 21


Now I'm assuming you have some kind of array of mappings from your category id's to their category id's

If I want to find all the Leather Dog Leashes and I know (however it is you know) that my id I want is 80 and 80 in my system corresponds to 19 in their system I can just look in _importItems_importCategories for anything with a cat_id of 19 and that will give me the id of everything in category 19.

If I'm looking at an item and I know that item 200 in my system is actually item 522 in their system and I want to know all the categories that item 522 belongs to I can just look in _importItems_importCategories for everything with an item_id of 522, INNER JOIN that with the _importCategories table to transform the catID into the names of the categories:
522 1,2,3,19,21 becomes
Name: Expert Leather Leash of Madness
Category: red, blue, animal control, diamonds, leather
Warning: I have no idea what I'm talking about.
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: Remapping using php and csv files

Post by thinsoldier »

makenoiz wrote: Cateogry Mapping Relationship Table

Supplier Cat..........| Our Cat.....
dogs~leashes~long.....| pets~walking
dogs~leashes~long.....| pets~travel
dogs~leashes~short....| pets~walking
dogs~leashes~nylon....| pets~walking
dogs~feeding .........| pets~feeding
Do you map dogs directly to pets or do you map the full combination of "dogs~leashes~long" === "pets~walking"?
Do you literaly have 2 records like:
dogs~leashes~short....| pets~walking
dogs~leashes~nylon....| pets~walking

Why not use an id to represent your category here?
dogs~leashes~short....| 4
dogs~leashes~nylon....| 4
[table MyCategories]
1 pets~health
2 pets~feeding
3 pets~travel
4 pets~walking
Warning: I have no idea what I'm talking about.
Post Reply