THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
| Table of Contents |
|---|
Here are a few ways:it depends of what you have at hand and what you want to do.
- If you have a direct access to an external database, the easiest method is to connect to it through the OFBiz Entity Engine.
- Use of ETL tool to extract data from the lagacy database and import it using XML-RPC or OFBiz WebServices
- If the data is not in an external database but you have data in files that can be loaded
Use the Entity xml data resource
Prepare xml file
| Info |
|---|
Action tags are available only with OFBiz 13.07 or higher, before <create-update> was the default. |
With the entity engine, you can load your data from an xml file. If you don't specify any action the <create-update> will be used, it's still the default.
| Code Block | ||||
|---|---|---|---|---|
| ||||
<entity-engine-xml>
<action>
<EntityName field1="value" field2="value" .../>
<EntityName field1="value" ...>
<field2> <![CDATA[Big text]]></field2>
</EntityName>
</action>
</entity-engine-xml> |
The available actions are :
<create> : the entity loading mechanism checks all entity elements present under this action and if a research by the primary returns empty, creates it.
Otherwise nothing is done.<create-update> : the entity loading mechanism checks all entity elements present under this action and if a research by primary key return empty, creates it.
Otherwise it updates the existing element with the given field value.<create-replace> : the entity loading mechanism checks all entity elements present under this action and if a research by primary key return empty, creates it.
Otherwise it replaces the existing element with the given field value. If a field is present in the database but not on xml definition, it will be set to empty.<delete> : the entity loading mechanism checks all entity elements present under this action and if a research by primary key return empty, tries to delete it.
Warning the foreign keys haven't been tested. So if you broke a database constraint, all operations present in the file will be rolled back.
Here is an example:
| Code Block | ||||
|---|---|---|---|---|
| ||||
<entity-engine-xml>
<create>
<Example exampleId="EX13" exampleName="Example 13" exampleTypeId="INSPIRED" statusId="EXST_IN_DESIGN"/>
<Example exampleId="EX14" exampleName="Example 14" exampleTypeId="INSPIRED" statusId="EXST_IN_DESIGN"/>
</create>
<create-replace>
<Example exampleId="EX12" exampleName="Example 12 after replace" exampleTypeId="INSPIRED" statusId="EXST_IN_DESIGN"/>
</create-replace>
<create-update>
<Example exampleId="EX12" exampleName="Example 11 after update" exampleTypeId="INSPIRED" statusId="EXST_IN_DESIGN"/>
</create-update>
<delete>
<Example exampleId="EX09"/>
</delete>
<Example exampleId="EX10" exampleName="Example 10 after update"/>
</entity-engine-xml> |
You can create your entity-engine-xml to load file with your preferred method such as:
- OpenOffice with macro
- Ftl template
- XSLT
- your preferred ETL
Load your data files in OFBiz
To load, you have three possibilities
By entity XML import reader : on your component indicate to load your files by with ext reader. When you want load your data, run ./ant load-ext from your OFBIz home directory.
For instance in your component/ofbiz-component.xml:Code Block language xml <entity-resource type="data" reader-name="ext" loader="main" location="data/MyImportData.xml"/>
- See How to preparing data for more information.
Directly from webtools in OFBiz, go to
https://localhost:8443/webtools/control/entityImport to load file by file or copy/paste text
https://localhost:8443/webtools/control/EntityImportDir to load a directory content
- Call the OFBiz service importEntityFileDirectory if you have an automation process
Use the OFBiz's Data File Tools to import your cvs file in
It uses a map to put the data in the entity fields. This is also good for adding static data such as new products.
Here is a sample product import file row. this is a tab delimited file
| Code Block |
|---|
PRODUCT_ID SKU TITLE CATEGORIES IMAGE_URL BRAND DESCRIPTION DETAILS WEIGHT DIMENSIONS GOING REFURB FREIGHT ITEM_NAME MAP PRICE MSRP QTY_AVAIL EST_AVAIL EXPECTED_SHIPPING_COST EXPECTED_DROP_SHIP_FEE UPC ITEM_ID 100 213 Atwater Carey EMT Shears leisure and sport|camping/hiking|accessories http://images.doba.com/products/2/213.jpg Atwater Carey The choice of EMTs worldwide, the 5-1/2'' trauma shears contained in all Pro Series kits or sold separately features an oversized rivet to overcome the rigors of backcountry emergencies. Identified by the bright orange handles, these shears will cut through cloth, plastic, and even light-gauge metal.<br><br> 0.100000002 N N N Atwater Carey EMT Shears 0 3.92 4.99 6 0000-00-00 6.63 2 7.39657E+11 100 |
Here is a sample product table definition from the above import file.
| Code Block |
|---|
<data-file name="Product" separator-style="delimited" type-code="UTF-8"
delimiter="	" text-delimiter=""
description="this takes the product data from doba file"
has-header="true">
<record name="Product">
<field name="productId" ignored="false" type="String"/>
<field name="primaryProductCategoryId" expression="true"
default-value="dropShip" type="String"/>
<field name="productTypeId" expression="true"
default-value="FINISHED_GOOD" type="String"/>
<field name="sku" ignored="true" type="String"/>
<field name="description" ignored="false" type="String"/>
<field name="categories" ignored="true" type="String"/>
<field name="image_url" ignored="true" type="String"/>
<field name="brandName" ignored="false" type="String"/>
<field name="longDescription" ignored="false" type="String"/>
<field name="details" ignored="true" type="String"/>
<field name="weight" ignored="false" type="String"/>
<field name="weightUomId" expression="true" default-value="WT_lb"
type="String"/>
<field name="dimensions" ignored="true" type="String"/>
<field name="going" ignored="true" type="String"/>
<field name="refurb" ignored="true" type="String"/>
<field name="freight" ignored="true" type="String"/>
<field name="productName" ignored="false" type="String"/>
<field name="map" ignored="true" type="String"/>
<field name="price" ignored="true" type="String"/>
<field name="msrp" ignored="true" type="String"/>
<field name="quantityIncluded" expression="true" default-value="1"
type="String"/>
<field name="quantity" ignored="true" type="String"/>
<field name="est_avail" ignored="true" type="String"/>
<field name="expected_shipping_cost" ignored="true" type="String"/>
<field name="expected_drop_ship_fee" ignored="true" type="String"/>
<field name="upc" ignored="true" type="String"/>
<field name="item_id" ignored="true" type="String"/>
<field name="isVirtual" expression="true" default-value="N"
type="String"/>
<field name="isVariant" expression="true" default-value="N"
type="String"/>
</record>
</data-file>
|
Here is the line in entity xml
| Code Block |
|---|
<Product productId="100" primaryProductCategoryId="dropShip" productTypeId="FINISHED_GOOD" internalName="213" description="Atwater Carey EMT Shears" brandName="Atwater Carey" longDescription="The choice of EMTs worldwide, the 5-1/2'' trauma shears contained in all Pro Series kits or sold separately features an oversized rivet to overcome the rigors of backcountry emergencies. Identified by the bright orange handles, these shears will cut through cloth, plastic, and even light-gauge metal.<br><br>" weight="0.1000000015" weightUomId="WT_lb" chargeShipping="Y" productName="Atwater Carey EMT Shears" quantityIncluded="1" isVirtual="N" returnable="Y" isVariant="N" /> |
Load the csv and save it as an xls file. Use the PIO to read the xls file
You can then search of the product is new or already in and create or update the product. This one requires coding.
You can add a flt that lets a user find the xls and load it that way, or add a service that runs as a job to read from a folder say every midnight.
You can pull the CSV into worksheet and add columns between the data
This is only good for static data like first initialization.
To add in the xml to make it an entity import. An example:
| Code Block | ||||
|---|---|---|---|---|
| ||||
1651143 "31090002101" "2.4G Media Pointer/Presenter" "computer and laptop|input devices|mice" http://images.doba.com/products/4/31090002101.jpg "Genius" "Genius 2.4G Media Pointer, USB, Blue-black. 2.4GHz professional presenter with time management and lazer pointer. Mini receiver can be stored inside; up to 15 meters anti-interference. LCD time controller, easy to control your presentation. Integrated media functions work just like a simple remote control." "" "0.6000000238" "" N N N "2.4G Media Pointer/Presenter" 0 31.9 50.37 2 0000-00-00 7.41 0 91163218432 1969225 |
| Code Block | ||||
|---|---|---|---|---|
| ||||
<Product productId="1651143" productTypeId="FINISHED_GOOD" internalName="31090002101" productName="2.4G Media Pointer/Presenter" longDescription= "Genius 2.4G Media Pointer, USB, Blue-black. 2.4GHz professional presenter with time management and lazer pointer. Mini receiver can be stored inside; up to 15 meters anti-interference. LCD time controller, easy to control your presentation. Integrated media functions work just like a simple remote control." weight="0.6000000238" taxable="Y" chargeShipping="N" description="2.4G Media Pointer/Presenter" autoCreateKeywords="Y" isVirtual="N" isVariant="N" /> |
Then save it as a tab delimited file.
Use an editor that and search and replace tabs(\t) with spaces( ).
You can import this data by using the webtool import routines.
You can do this as a filehttps://localhost:8443/webtools/control/EntityImportDir or you can so this as https://localhost:8443/webtools/control/EntityImport
See also How to save customised data for import as demo data