Access to add and change pages is restricted. See: https://cwiki.apache.org/confluence/display/OFBIZ/Wiki+access

Skip to end of metadata
Go to start of metadata

 

Here are a few ways: it depends of what you have at hand and what you want to do.

Use the Entity xml data resource 

Prepare xml file

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.

Entity Xml Data Template
<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:

Entity Xml Data Resource Exemple
<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

  1. 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:

        <entity-resource type="data" reader-name="ext" loader="main" location="data/MyImportData.xml"/>
    1.  See  How to preparing data for more information.
  2. Directly from webtools  in OFBiz, go to 

    1. https://localhost:8443/webtools/control/entityImport to load file by file or copy/paste text

    2. https://localhost:8443/webtools/control/EntityImportDir to load a directory content

  3. Call the OFBiz service importEntityFileDirectory if you have an automation process

Use the OFBiz's Data File Tools to import a CSV or tab-delimited file

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. If you prefer CSV (Comma Separated Values), change the delimiter to "," and the text-delimiter to "&quot;" in the XML below.

 

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.

<data-file name="Product" separator-style="delimited" type-code="UTF-8"
    delimiter="&#009;" 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

<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&apos;&apos; 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.&lt;br&gt;&lt;br&gt;" weight="0.1000000015" weightUomId="WT_lb" chargeShipping="Y" productName="Atwater Carey EMT Shears" quantityIncluded="1" isVirtual="N" returnable="Y" isVariant="N" />

Importing XLS files

Microsoft Excel prior to 2007 used the proprietary XLS file format. XLS files can be read by Java and Groovy applications using the POI library. See Import Data Using Apache POI api and the JIRA issue OFBIZ-1810 for more information.

The new XLSX format is fundamentally XML files packaged in the Zip file format. You could extract data from the XLSX file and apply an XSLT transform to create an OFBiz XML file. 

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:

Excel row of data to import
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
Same Excel row of data converted to entity data
  <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

4 Comments

  1. The documentation should be expanded to incorporate the features mentioned here:

    OFBIZ-5113 - Getting issue details... STATUS

  2. Next you need to clean up the entities that match your current DB. 
    you can use https://cwiki.apache.org/OFBIZ/ofbiz-tutorial-a-beginners-development-guide.html
    as a reference.

    This refers to a page that has moved. It is not clear what one is supposed to do. What is supposed to be cleaned up - source data or  bad data loaded into OFBiz???

  3. Tab delimited data is not that easy to read. CSV - comma separated values

  4. It might be a good idea to break this up into an overview page and sub-pages for each method. In the current format, they sort of run together and it is not clear that each method is complete.