Posted by
Walter Vaughan on
URL: http://ofbiz.116.s1.nabble.com/Importing-data-from-Excel-CSV-tp143155p143158.html
Iain Fogg wrote:
> In any case, I found the hardest part was simply working out which piece
> of data needed to go to which part of the OFBiz data model. The flip
> side is that you get to know the OFBiz data model pretty well :-)
>> I'm quite new to ofbiz, but an experienced Java Developer. I have a
>> catalog I want to import into the database, but the Excel/CSV file
>> I've been given contains columns that need to be inserted into
>> different tables. I have looked through the Wiki and other pages on
>> Data Import, but I can't seem to find any straightforward exmaples. I
>> would do it manually, but there are over 2500 records to enter. Does
>> anyone know of a good tutorial on this?
>> Thanks,
>> Hugh
Once the light goes off (on?) things become much easier.
From experience, your life will be tons happier with entity-engine-xml's.
Eat the data like you are eating an elephant (bite at a time), and you will be
happy. Break your data down into at least 4 entity-engine-xml formatted files.
Since you said "catalog" I am going to assume you want to add items to the
ecommerce portion of ofBiz.
The following example is how we entered a part for a 1916 Buick (yeah, we have
parts for 90 year old cars).
==1st== You need a top level category and all the subcategories
======= Every one of our parts is in the RUBBER Category plus many others
<ProductCategory productCategoryId="1916BU"
productCategoryTypeId="CATALOG_CATEGORY" primaryParentCategoryId="D1910BU"
description="1916 All Models" longDescription="" fromDate="2001-05-13
12:00:00.0" />
<ProductCategoryRollup productCategoryId="1916BU"
parentProductCategoryId="D1910BU" sequenceNum="6" fromDate="2001-05-13
12:00:00.0" />
==2nd== Generate PRODUCTS
<Product productId="70-1114-57" productTypeId="FINISHED_GOOD"
primaryProductCategoryId="RUBBER" internalName="70-1114-57" productName="Misc.
Side Window Parts" description="" longDescription="" taxable="Y"
chargeShipping="Y" autoCreateKeywords="N" isVirtual="N" isVariant="N" />b
==3rd== Then you need prices (You gotta have both of these)
<ProductPrice productId="70-1114-57" productPriceTypeId="DEFAULT_PRICE"
productPricePurposeId="PURCHASE" currencyUomId="USD" productStoreGroupId="_NA_"
fromDate="2001-05-13 12:00:00.0" price="1.10" />
<ProductPrice productId="70-1114-57" productPriceTypeId="LIST_PRICE"
productPricePurposeId="PURCHASE" currencyUomId="USD" productStoreGroupId="_NA_"
fromDate="2001-05-13 12:00:00.0" price="1.10" />
==4th== Then hook the category to the product
<ProductCategoryMember productCategoryId="1916BU" productId="70-1114-57"
comments="Filler strip, sash channel, for all applications fitting glass to sash
channel not requiring weatherlip type of filler. Check your old filler strip to
find thickness needed. This is the highest quality linen back, rubber cork
composition for a professional job on swingout w/shields, door vent windows
& conv't. side windows having metal frames as well as many side or door
windows on closed bodies. Please specify thickness required. Be sure to measure
length carefully, as this item is NOT returnable." fromDate="2001-05-13
12:00:00.0" />
If you have referential integrity issues the entity-engine will let you know.
The entire .xml will be rejected if there are problems, but when you get your
data right, you can feel pretty sure you have got the right data in the right
places. It eats records at about 500-1000 per second, so you'll know pretty
quick with your data set if things are going well.
At this point you should have a working ecommerce site, now it's just a matter
of going back and adding in the images url's to the PRODUCT entity import, or
using SQL to update the PRODUCT table fields. Anything that is not a primary key
or restrained is open territory for an UPDATE statement.
This page is your friend..
https://your.ofbiz.server:8443/webtools/control/ViewRelations?entityName=ProductI also recommend you Purchase the "Advanced Framework Training" vides from
Undersun consulting. They won't address this issue, but with your existing Java
skills, you'll really get a grip on ofBiz...
http://www.undersunconsulting.com/ecommerce/control/product/~category_id=USC_PROMO/~product_id=OFBADVFWKPKG--
Walter