Database import of items weighing under 1 oz

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   We're getting ready to load a bunch of new items to one of our ProductCart sites. These are scrapbooking papers that must be ordered in packs of 25 sheets. Our in-house system handles these as individual sheets sold in multiples of 25. So the weight is for 1 sheet, something like 0.48 ounces.

We want to load the items using a CSV file of data exported from our inventory system (with the other fields like main image and description filled in by us in the CSV file). However, we had an issue a couple of weeks ago with an item that was .8 ounces, that showed up in ProductCart as 0 ounces (we discovered this when we saw an order with no shipping charged, and it was due to the weight).

I know we can use the Control Panel item maintenance to set the weight for items by specifying how many units make up a pound. But we have over 100 SKUs that fall into this group of sub-ounce weights, and manually setting weights for all of them via the web interface will take a few hours.

Any advice?

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
BrianRoden Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 07-September-2007 Status: Offline Points: 0	Post Options Post Reply Quote BrianRoden Report Post Thanks(0) Quote Reply Topic: Database import of items weighing under 1 oz Posted: 17-August-2009 at 10:54am
	We're getting ready to load a bunch of new items to one of our ProductCart sites. These are scrapbooking papers that must be ordered in packs of 25 sheets. Our in-house system handles these as individual sheets sold in multiples of 25. So the weight is for 1 sheet, something like 0.48 ounces. We want to load the items using a CSV file of data exported from our inventory system (with the other fields like main image and description filled in by us in the CSV file). However, we had an issue a couple of weeks ago with an item that was .8 ounces, that showed up in ProductCart as 0 ounces (we discovered this when we saw an order with no shipping charged, and it was due to the weight). I know we can use the Control Panel item maintenance to set the weight for items by specifying how many units make up a pound. But we have over 100 SKUs that fall into this group of sub-ounce weights, and manually setting weights for all of them via the web interface will take a few hours. Any advice?

BrianRoden Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 07-September-2007 Status: Offline Points: 0	Post Options Post Reply Quote BrianRoden Report Post Thanks(0) Quote Reply Posted: 18-August-2009 at 5:26pm
	OK, I found the field pcprod_QtyToPound referenced in ModifyProduct.asp. But that's not a field that can be specified in the import file. Am I going to need to do an SSIS import job to a temp table that includes the fractional ounce weight, then run an update query like UPDATE products SET pcprod_QtyToPound=(16 / othertable.weight) FROM products INNER JOIN othertable ON products.SKU=othertable.SKU ?

Carebear Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 18-July-2009 Status: Offline Points: 0	Post Options Post Reply Quote Carebear Report Post Thanks(0) Quote Reply Posted: 25-August-2009 at 11:36am
	I have the same question as above. Anyone have an answer?

BrianRoden Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 07-September-2007 Status: Offline Points: 0	Post Options Post Reply Quote BrianRoden Report Post Thanks(0) Quote Reply Posted: 25-August-2009 at 12:13pm
	I ended up running the following query after importing the new items: update products set pcprod_QtyToPound = floor(16 / (i.itemweight16)) from products p inner join saleshist..laistdp i ON p.sku=i.ItemNum where (i.itemweight 16) < 1 and p.weight<=1 saleshist..laistdp is a table in another DB on our in-house SQL Server that has product weights and dimensions from our AS/400 warehouse system. We have the ProductCart database on our in-house SQL box, with merge replication to the web server. This lets our warehouse inventory update the local SQL Server nightly, which then replicates in the early a.m. hours to the storefront site.