Batch Uploading

Dear Web Assist,

As I have mentioned before I have tried to do a bulk upload of multi thousand products into the database with mixed success. I thought I'd see if I could help out and trace the problem I was having with the PS working after batch upload. I deleted the entire setup and started from scratch setting up a new store.

I then uploaded csv tables to ps3_products, ps3_categories and ps3_productcategories products, categories and category links. All my products are in and show up fine in a show "all products" search. If I click on a category though it goes to sleep for 5 minutes while it searches then turns up empty. The first time I asked about this you suggested that it was timing out on too big a search, however when I click on all products it is very quick, almost instant, so I don't think that's the problem. In fact I'm sure it's not, see below.

If I go to admin and look at product details they show up and connected to the correct category s uses that particular table, ps3_productcategories. There must be something about bulk uploading that throws it off. (Realize that I manually configured this table as a csv so there may be something about the way I did that but it's pretty straight forward, only two columns.)

As a test I deleted the ps3_productcategories table which is the table that links all the products with their categories. Clicking on a category now does not time the system out, it just shows up empty. In admin I then added some products to their categories. Now clicking on the particular category turns up the products in quick order. Very nice actually. I am now convinced that this table is the culprit. There seems to be something about it that causes problems for a batch upload.

Of course once could batch upload products and categories then go back and manually connect products to categories but for a true uploadable store this one glitch should be looked into. You don't need to respond to me on this as I was just taking a deeper look into this problem and thought I would assess you of my findings. (I haven't seen anyone else with a similar problem so maybe it's just another one of my ID ten T errors.)

Another thing I am curious about, in the products result page images are contained in common.css in productResultsWrapper .left .imageWrapper . I was curious as to whether one of these settings could be made to control the box size thereby forcing the larger detail image into the smaller box and reducing the need to upload two images for each product.

WA... Please help fix with this! This should be something that you can let everyone know how to do and get more people using it. This seems like a very key item in a store.

It is difficult to say what the problem may have been with your batch upload, perhaps the CSV was missing a database column or was not configured correctly.

I am sending a CSV dump that I have created of the ps3_categoreies, ps3_productcategoreies and ps3_products tabels from our powerstore_demo site, please use this as a template for creating your csv files.

Hi Jason,

Thanks for the spreadsheets. I thought I'd give it a try and see how it worked. The first think I noticed was that you are using a semicolon instead of a comma. Sure makes the sheets harder to work with. Nonetheless I soldiered on. I tried an import in both products and categories. I got the same results with both. Obviously I'm doing something wrong with the import. I've tried several different ways and get the same results. I've pretty much got the import down with csv but not ssv. (semicolon separated value) Any indea on what I'm doing wrong in the import?

Thanks


LOAD DATA LOCAL INFILE 'C:\\xampp\\tmp\\phpA1.tmp' INTO TABLE `ps3_categories` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'(
`CategoryID;"CategoryName";"CategoryParent"`
)

MySQL said: Documentation
#1054 - Unknown column 'CategoryID;"CategoryName";"CategoryParent"' in 'field list'

I was using phpMyAdmin to both export the csv file and do a test import. I was using the default settings in phpMyAdmin.

I am sending screen shots of the export and import settings I used in phpMyAdmin.

Jason,

Anyway that you could do a batch export as an Excel file so that this database would be easier to work with? I'm also having lots of problems with this. Actually the exact same problem with the Products showing up in my store!

I was also wondering if there would be any issues with me replacing the ProductID if I'm doing a batch upload? I essentially want to make the Product ID to be the Item # in my catalog. I'll match this with my category tables as well.

Sure here is a CSV for MS Excell export.

When importing this, make sure to change the "Fields terminated By" setting to a comma.

since the ProductID is a maintained by the DB as an auto number filed, you should not change this to be the same as item number. Use the Item # as in the SKU column.

Thanks Jason!
I have one more question about this. In the ps3_productcategories table is the first column/ProductCategoryID just an autogenerated column? And what is the relationship with the ps3_products table then? And then exactly how do I relate this to the ps3_productcategories table? I know that the ps3_categories table has the product category IDs - which are autogenerated. Then the category name and the the sub-category linking for these.

I'm just trying to figure out how I link up my products categories from the ps3_products table via the ps3_productategories table when I do the batch upload? (A database map - like the one with the Database Kit sure would be nice for this Power Store database!)

Hi Carlene,

I've been working on this for quite a while and while I don't have any answers I may have a little insight.

The ps3_productcategories table is as you correctly surmised made up of 3 auto-generated fields, it's own, one form ps3_products and one from ps3_categories. There really is no commonality between the tables that allows for easy linking so you have to create you own.

Since productID is a sequential number it gives no basis for linking. What I did was to create a table with a column for ProductID and one for ProductSKU then manually link CategoryID to ProductSKU. When finished I could delete the column for ProductSKU and I had the required csv for import. Not so bad if you have a small list but then you are better off to upload products one at a time.

(WARNING - Don't try this at home, it will lead to premature aging, an enhancement of negative vocabulary and possibly divorce.)

The csv files that Jason supplied are not csv but rather semicolon separated values. What this means is that rather than a separate column for each heading they are all actually in one great big column, each item separated by a semi colon.

I tried several times to get it to import and was finally successful by clicking on the first CSV and checking, Ignore Duplicate Rows. It did work, the import was successful and the products and categories connected properly. Unfortunately for us that is not a practical solution. It is okay to export a sheet using semicolon separated values but not at all practical to create one that way. It would be a humongous task to try to create a spread sheet for thousands of items with only one column and separating each item category with a semicolon. At least I wouldn't want to try it. One misalignment and the whole sheet is shot and good luck finding the error. Much easier to create the csv using separate columns for each field. But this is where my problem comes in. I have been able to import products and categories but so far importing productcategories this way does not provide the proper linking.

But that gives me an idea. I could save each column as text, use a program I have to put quotes around everything, then again to add a semicolon after each item. Then I could combine the two columns into one and try an import using semicolon separated values. That just might work.

There has to be an easier way but I'm obviously not bright enough to find it. Hopefully someone else out there has successfully created csv's and imported them into all three tables and would like to share.

Carlene:
here are the relationships created using the ps3_categories, ps3_productcategories. the products table is pretty strait forward so I haven't mapped it out.

ps3_categories:
CategoryID - primary Key, Auto Number
CategoryName - text, name of category
CategoryParent - foreign Key to CategoryID, for creating sub categories

ps3_productcategories - used to relate a product to its category:
ProductCategoryID - primary Key, Auto Number
CategoryID - Foreign key to ps3_categories.CategoryID
ProductID - Foreign key to ps3_products.ProductID


balexander389215:

in my response to Carlene earlier, I created Excel CSV files, you can use those if it is more convenient for you.