Importing data from Excel, CSV

[Hugh O'Donnell]

Hi all,

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

[cjhowe]

I'm not sure where there is a tutorial, and i haven't
been keeping up with the various efforts around
importing, but if it's a one time thing that doesn't
generally automated, I would suggest simply using
excel's various functions to create the
entity-engine-xml element for your data.

concatenate(), right(), left(), trim() etc

or if you have MS Access and have an odbc for your
database to import it using linked tables in Access.


--- Hugh O'Donnell <[hidden email]> wrote:

> Hi all,
>
> 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
>

[Iain Fogg]

Hugh,

I had a similar problem, although my data was in XML format. I ended up
writing a custom perl script to insert my data directly into the SQL
tables of my Postgres DB. Maybe a better method would have been to split
my original data into XML records digestable by OFBiz. However, I often
need to  download and install new records in real-time (with a customer
in front of me) and  having to run a script to format the data for
OFBiz, and then go to WebTools to import the data is too time-consuming.

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 :-)

Cheers, Iain

Hugh O'Donnell wrote:

> Hi all,
>
> 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
>
>

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.27/602 - Release Date: 25/12/2006

[Walter Vaughan]

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
&amp; 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=Product

I 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

[Ashish Vijaywargiya-3]

Hugh,

I would like to suggest you to convert your Excel files into CSV format and
then play with Data file tool present in the Webtools --> Work With Data
File Tool.

These links will help you to understand the concept behind Data File tool
and to import external data into ofbiz entities.

http://docs.ofbiz.org/display/~jacopoc/OFBiz%27s+Data+File+Tools

http://ofbizwiki.go-integral.com/Wiki.jsp?page=ImportingData


http://ofbizwiki.go-integral.com/Wiki.jsp?page=DataFile

http://mail-archives.apache.org/mod_mbox/incubator-ofbiz-user/200610.mbox/%3c20061005074611.25742.qmail@...%3e
(Please see the discussion on this thread.Jacopo has explained it in a very
good way)


Let us know your findings.

On 12/24/06, Hugh O'Donnell <[hidden email]> wrote:

>
> Hi all,
>
> 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
>

--
Regards
Ashish Vijaywargiya
Aditisoft Technology Laboratory

[Andrew Zeneski]

There are built in routines to import delimited text files into OFBiz  
much like the XML files. Each table gets its own file and the first  
line of the file should be the names of the entity fields (not the  
SQL table fields).

Check out the entityext component.

Andrew

On Dec 25, 2006, at 5:27 PM, Iain Fogg wrote:

> Hugh,
>
> I had a similar problem, although my data was in XML format. I  
> ended up writing a custom perl script to insert my data directly  
> into the SQL tables of my Postgres DB. Maybe a better method would  
> have been to split my original data into XML records digestable by  
> OFBiz. However, I often need to  download and install new records  
> in real-time (with a customer in front of me) and  having to run a  
> script to format the data for OFBiz, and then go to WebTools to  
> import the data is too time-consuming.
>
> 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 :-)
>
> Cheers, Iain
>
> Hugh O'Donnell wrote:
>> Hi all,
>>
>> 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
>>
>>
>
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.409 / Virus Database: 268.15.27/602 - Release Date:  
> 25/12/2006
>