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
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.
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:
You can create your entity-engine-xml to load file with your preferred method such as:
- OpenOffice with macro
- Ftl template
- 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:
- 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 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 """ in the XML below.
Here is a sample product table definition from the above import file.
Here is the line in entity xml
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:
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