Data File Tools and Hierarchical Flat Files

[Forrest Rae]

Hi Guys,

I'm a bit stumped by this, and I can't imagine I'm the first person to
need to import a bunch of party data from a flat file (CSV).  I'm trying
to figure out the Data File Tools.  It makes one small mention of
hierarchical flat files:

"can support hierarchical flat files (ie where parent/child
relationships are implied by sub-records)."

I have a CSV file, basically contains a bunch of potential customers
from a CRM, exported in the following columns:

PartyGroupName
Address
City
stateProvinceGeoId
postalCode
countryGeoId
TelecomNumber

Super easy to just import the PartyGroupName, that's much is clear:

<record name="PartyGroup" limit="many">
 <field name="groupName" type="String" position="0"/>
</record>

Problem is the PostalAddress and TelecomNumber.  Do I really need to
create contactMechs and tie everything together?  How do I model this in
the XML schema if not?  The documentation doesn't make another mention
of this support for hierarchical flat files.

-Forrest

https://cwiki.apache.org/confluence/display/OFBENDUSER/OFBiz%27s+Data+File+Tools

[Forrest Rae]

I had a bit more of a play with the tool, read the source, and as far as
I can tell, it doesn't do anything with relationships.  Basically, it
will work for the Product entity (as all the examples are based) as
there is no real relationship involved here.

Probably best to throw this thing out the window and write one from
scratch that calls the appropriate services I suppose.  Anyone taken
this approach before?

-Forrest

On 12/11/14, 8:52 PM, Forrest Rae wrote:

> Hi Guys,
>
> I'm a bit stumped by this, and I can't imagine I'm the first person to
> need to import a bunch of party data from a flat file (CSV).  I'm trying
> to figure out the Data File Tools.  It makes one small mention of
> hierarchical flat files:
>
> "can support hierarchical flat files (ie where parent/child
> relationships are implied by sub-records)."
>
> I have a CSV file, basically contains a bunch of potential customers
> from a CRM, exported in the following columns:
>
> PartyGroupName
> Address
> City
> stateProvinceGeoId
> postalCode
> countryGeoId
> TelecomNumber
>
> Super easy to just import the PartyGroupName, that's much is clear:
>
> <record name="PartyGroup" limit="many">
>   <field name="groupName" type="String" position="0"/>
> </record>
>
> Problem is the PostalAddress and TelecomNumber.  Do I really need to
> create contactMechs and tie everything together?  How do I model this in
> the XML schema if not?  The documentation doesn't make another mention
> of this support for hierarchical flat files.
>
> -Forrest
>
> https://cwiki.apache.org/confluence/display/OFBENDUSER/OFBiz%27s+Data+File+Tools
>

[Adrian Crum-3]

The easiest way to use the tool is to import the CSV file into a
specialized table to contain the data. After the import, invoke a
service that processes the data in the table.

Adrian Crum
Sandglass Software
www.sandglass-software.com

On 12/12/2014 4:38 PM, Forrest Rae wrote:

> I had a bit more of a play with the tool, read the source, and as far as
> I can tell, it doesn't do anything with relationships.  Basically, it
> will work for the Product entity (as all the examples are based) as
> there is no real relationship involved here.
>
> Probably best to throw this thing out the window and write one from
> scratch that calls the appropriate services I suppose.  Anyone taken
> this approach before?
>
> -Forrest
>
> On 12/11/14, 8:52 PM, Forrest Rae wrote:
>> Hi Guys,
>>
>> I'm a bit stumped by this, and I can't imagine I'm the first person to
>> need to import a bunch of party data from a flat file (CSV).  I'm trying
>> to figure out the Data File Tools.  It makes one small mention of
>> hierarchical flat files:
>>
>> "can support hierarchical flat files (ie where parent/child
>> relationships are implied by sub-records)."
>>
>> I have a CSV file, basically contains a bunch of potential customers
>> from a CRM, exported in the following columns:
>>
>> PartyGroupName
>> Address
>> City
>> stateProvinceGeoId
>> postalCode
>> countryGeoId
>> TelecomNumber
>>
>> Super easy to just import the PartyGroupName, that's much is clear:
>>
>> <record name="PartyGroup" limit="many">
>>   <field name="groupName" type="String" position="0"/>
>> </record>
>>
>> Problem is the PostalAddress and TelecomNumber.  Do I really need to
>> create contactMechs and tie everything together?  How do I model this in
>> the XML schema if not?  The documentation doesn't make another mention
>> of this support for hierarchical flat files.
>>
>> -Forrest
>>
>> https://cwiki.apache.org/confluence/display/OFBENDUSER/OFBiz%27s+Data+File+Tools
>>
>>

[Jacques Le Roux]

I did not read these pages again (a long time now), but they might help, and you may give us your opinion:

https://cwiki.apache.org/confluence/display/OFBIZ/Handling+of+External+data
https://cwiki.apache.org/confluence/display/OFBIZ/Import+Data+Using+Apache+POI+api

Jacques

Le 12/12/2014 18:02, Adrian Crum a écrit :

> The easiest way to use the tool is to import the CSV file into a specialized table to contain the data. After the import, invoke a service that
> processes the data in the table.
>
> Adrian Crum
> Sandglass Software
> www.sandglass-software.com
>
> On 12/12/2014 4:38 PM, Forrest Rae wrote:
>> I had a bit more of a play with the tool, read the source, and as far as
>> I can tell, it doesn't do anything with relationships. Basically, it
>> will work for the Product entity (as all the examples are based) as
>> there is no real relationship involved here.
>>
>> Probably best to throw this thing out the window and write one from
>> scratch that calls the appropriate services I suppose.  Anyone taken
>> this approach before?
>>
>> -Forrest
>>
>> On 12/11/14, 8:52 PM, Forrest Rae wrote:
>>> Hi Guys,
>>>
>>> I'm a bit stumped by this, and I can't imagine I'm the first person to
>>> need to import a bunch of party data from a flat file (CSV). I'm trying
>>> to figure out the Data File Tools.  It makes one small mention of
>>> hierarchical flat files:
>>>
>>> "can support hierarchical flat files (ie where parent/child
>>> relationships are implied by sub-records)."
>>>
>>> I have a CSV file, basically contains a bunch of potential customers
>>> from a CRM, exported in the following columns:
>>>
>>> PartyGroupName
>>> Address
>>> City
>>> stateProvinceGeoId
>>> postalCode
>>> countryGeoId
>>> TelecomNumber
>>>
>>> Super easy to just import the PartyGroupName, that's much is clear:
>>>
>>> <record name="PartyGroup" limit="many">
>>>   <field name="groupName" type="String" position="0"/>
>>> </record>
>>>
>>> Problem is the PostalAddress and TelecomNumber.  Do I really need to
>>> create contactMechs and tie everything together?  How do I model this in
>>> the XML schema if not?  The documentation doesn't make another mention
>>> of this support for hierarchical flat files.
>>>
>>> -Forrest
>>>
>>> https://cwiki.apache.org/confluence/display/OFBENDUSER/OFBiz%27s+Data+File+Tools
>>>
>>>
>

[Forrest Rae]

Heya Jacques,

I did read the pages, actually studied them extensively and read the
source code to the tool.  I wouldn't bother the list otherwise.  :)

Adrian, that's a good suggestion, thanks!

-Forrest

On 12/12/2014 12:27 PM, Jacques Le Roux wrote:

> I did not read these pages again (a long time now), but they might help,
> and you may give us your opinion:
>
> https://cwiki.apache.org/confluence/display/OFBIZ/Handling+of+External+data
> https://cwiki.apache.org/confluence/display/OFBIZ/Import+Data+Using+Apache+POI+api
>
>
> Jacques
>
> Le 12/12/2014 18:02, Adrian Crum a écrit :
>> The easiest way to use the tool is to import the CSV file into a
>> specialized table to contain the data. After the import, invoke a
>> service that processes the data in the table.
>>
>> Adrian Crum
>> Sandglass Software
>> www.sandglass-software.com

[Mike Z]

Take the csv, convert it to xml, and just manually load it from the command
line.

Of course,  order is significant.   You have to load in catalog and
categories prior to loading products.   Each of these can be a separate xml
(recommended) or you can create one gigantic xml that does it all.

If you want top speed then this is how to do it.  How to convert from csv
to xml?  Hopefully you are handy with Perl, shell, or another language to
convert.   Take a typical product, create a template with variables, and
use it to mass create the xml file.  The beauty of this method is once you
are done creating the xml files, you can reload them over and over.

Another option is to use an ETL like talons which can automate the whole
process, but you must be familiar with the entities.   This would take
expertise in using both the ETL and ofbiz entities and order.

Sent from my BlackBerry® PlayBook™
www.blackberry.com

------------------------------
*From:* "Forrest Rae" <[hidden email]>
*To:* "[hidden email]" <[hidden email]>
*Sent:* December 12, 2014 1:20 PM
*Subject:* Re: Data File Tools and Hierarchical Flat Files

Heya Jacques,

I did read the pages, actually studied them extensively and read the
source code to the tool.  I wouldn't bother the list otherwise.  :)

Adrian, that's a good suggestion, thanks!

-Forrest

On 12/12/2014 12:27 PM, Jacques Le Roux wrote:

> I did not read these pages again (a long time now), but they might help,
> and you may give us your opinion:
>
> https://cwiki.apache.org/confluence/display/OFBIZ/Handling+of+External+data
> https://cwiki.apache.org/confluence/display/OFBIZ/Import+Data+Using+Apache+POI+api
>
>
> Jacques
>
> Le 12/12/2014 18:02, Adrian Crum a écrit :
>> The easiest way to use the tool is to import the CSV file into a
>> specialized table to contain the data. After the import, invoke a
>> service that processes the data in the table.
>>
>> Adrian Crum
>> Sandglass Software
>> www.sandglass-software.com

[Mike Z]

Adding more to this:

This is how you load an XML file from the command line:

./ant run-install-file -Ddata-file=[XML_FILE]

Can be done from a running system.


On Sat, Dec 13, 2014 at 1:46 PM, Mike Z <[hidden email]> wrote:

>
> Take the csv, convert it to xml, and just manually load it from the
> command line.
>
> Of course,  order is significant.   You have to load in catalog and
> categories prior to loading products.   Each of these can be a separate xml
> (recommended) or you can create one gigantic xml that does it all.
>
> If you want top speed then this is how to do it.  How to convert from csv
> to xml?  Hopefully you are handy with Perl, shell, or another language to
> convert.   Take a typical product, create a template with variables, and
> use it to mass create the xml file.  The beauty of this method is once you
> are done creating the xml files, you can reload them over and over.
>
> Another option is to use an ETL like talons which can automate the whole
> process, but you must be familiar with the entities.   This would take
> expertise in using both the ETL and ofbiz entities and order.
>
> Sent from my BlackBerry® PlayBook™
> www.blackberry.com
>
> ------------------------------
> *From:* "Forrest Rae" <[hidden email]>
> *To:* "[hidden email]" <[hidden email]>
> *Sent:* December 12, 2014 1:20 PM
> *Subject:* Re: Data File Tools and Hierarchical Flat Files
>
> Heya Jacques,
>
> I did read the pages, actually studied them extensively and read the
> source code to the tool.  I wouldn't bother the list otherwise.  :)
>
> Adrian, that's a good suggestion, thanks!
>
> -Forrest
>
> On 12/12/2014 12:27 PM, Jacques Le Roux wrote:
> > I did not read these pages again (a long time now), but they might help,
> > and you may give us your opinion:
> >
> > https://cwiki.apache.org/confluence/display/OFBIZ/Handling+of+External+data
> > https://cwiki.apache.org/confluence/display/OFBIZ/Import+Data+Using+Apache+POI+api
> >
> >
> > Jacques
> >
> > Le 12/12/2014 18:02, Adrian Crum a écrit :
> >> The easiest way to use the tool is to import the CSV file into a
> >> specialized table to contain the data. After the import, invoke a
> >> service that processes the data in the table.
> >>
> >> Adrian Crum
> >> Sandglass Software
> >> www.sandglass-software.com
>
>

[Ron Wheeler]

I have an ETL program and would be happy to work in configuring it to
produce XML files suitable for OFBiz.
It currently has a plug-in that produces HTML output of tables to HTML
with attributes that make it easy to style with css.
I need to remove the HTML prologue,HTML epilogue and the attributes to
have just the table in XML to make a new plug-in to output a simpler XML
structure suitable for OFBiz.

It currently reads CSV and Excel so it would be a good front-end for
OFBiz integrations where data is extracted from other systems and needs
to be fed to the OFBiz
It also has a lot of transformation and validation plug-ins if you need
to do things to alter the data (rename columns, set defaults, add new
columns, reformat dates, map data using mapping files, etc) and validate
the output (table lookups, field length, uniqueness, etc.)
It produces an audit report of the actions done and an error log that
writes error messages in a format that makes sense to non-technical
people ( "while validating products found duplicate ids in row 7 and
167" ) who have to fix the original system and don't want to decipher
SQL errors "Constraint violation while creating record".

It was developed to support the transfer of HRIS (payroll data) to LMS
(Learning Management Systems).

Ron
Ron
On 13/12/2014 4:46 PM, Mike Z wrote:

> Take the csv, convert it to xml, and just manually load it from the command
> line.
>
> Of course,  order is significant.   You have to load in catalog and
> categories prior to loading products.   Each of these can be a separate xml
> (recommended) or you can create one gigantic xml that does it all.
>
> If you want top speed then this is how to do it.  How to convert from csv
> to xml?  Hopefully you are handy with Perl, shell, or another language to
> convert.   Take a typical product, create a template with variables, and
> use it to mass create the xml file.  The beauty of this method is once you
> are done creating the xml files, you can reload them over and over.
>
> Another option is to use an ETL like talons which can automate the whole
> process, but you must be familiar with the entities.   This would take
> expertise in using both the ETL and ofbiz entities and order.
>
> Sent from my BlackBerry® PlayBook™
> www.blackberry.com
>
> ------------------------------
> *From:* "Forrest Rae" <[hidden email]>
> *To:* "[hidden email]" <[hidden email]>
> *Sent:* December 12, 2014 1:20 PM
> *Subject:* Re: Data File Tools and Hierarchical Flat Files
>
> Heya Jacques,
>
> I did read the pages, actually studied them extensively and read the
> source code to the tool.  I wouldn't bother the list otherwise.  :)
>
> Adrian, that's a good suggestion, thanks!
>
> -Forrest
>
> On 12/12/2014 12:27 PM, Jacques Le Roux wrote:
>> I did not read these pages again (a long time now), but they might help,
>> and you may give us your opinion:
>>
>> https://cwiki.apache.org/confluence/display/OFBIZ/Handling+of+External+data
>> https://cwiki.apache.org/confluence/display/OFBIZ/Import+Data+Using+Apache+POI+api
>>
>>
>> Jacques
>>
>> Le 12/12/2014 18:02, Adrian Crum a écrit :
>>> The easiest way to use the tool is to import the CSV file into a
>>> specialized table to contain the data. After the import, invoke a
>>> service that processes the data in the table.
>>>
>>> Adrian Crum
>>> Sandglass Software
>>> www.sandglass-software.com

--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102

[Mike Z]

Normally with an ETL you would use it to read an xml/CSV file (I.e from a
supplier ) and directly update the DB, rather than create an intermediary
xml file.  Interesting option though.

Sent from my BlackBerry® PlayBook™
www.blackberry.com

------------------------------
*From:* "Ron Wheeler" <[hidden email]>
*To:* "[hidden email]" <[hidden email]>
*Sent:* December 13, 2014 2:45 PM
*Subject:* Re: Data File Tools and Hierarchical Flat Files

I have an ETL program and would be happy to work in configuring it to
produce XML files suitable for OFBiz.
It currently has a plug-in that produces HTML output of tables to HTML
with attributes that make it easy to style with css.
I need to remove the HTML prologue,HTML epilogue and the attributes to
have just the table in XML to make a new plug-in to output a simpler XML
structure suitable for OFBiz.

It currently reads CSV and Excel so it would be a good front-end for
OFBiz integrations where data is extracted from other systems and needs
to be fed to the OFBiz
It also has a lot of transformation and validation plug-ins if you need
to do things to alter the data (rename columns, set defaults, add new
columns, reformat dates, map data using mapping files, etc) and validate
the output (table lookups, field length, uniqueness, etc.)
It produces an audit report of the actions done and an error log that
writes error messages in a format that makes sense to non-technical
people ( "while validating products found duplicate ids in row 7 and
167" ) who have to fix the original system and don't want to decipher
SQL errors "Constraint violation while creating record".

It was developed to support the transfer of HRIS (payroll data) to LMS
(Learning Management Systems).

Ron
Ron
On 13/12/2014 4:46 PM, Mike Z wrote:

> Take the csv, convert it to xml, and just manually load it from the command
> line.
>
> Of course,  order is significant.   You have to load in catalog and
> categories prior to loading products.   Each of these can be a separate xml
> (recommended) or you can create one gigantic xml that does it all.
>
> If you want top speed then this is how to do it.  How to convert from csv
> to xml?  Hopefully you are handy with Perl, shell, or another language to
> convert.   Take a typical product, create a template with variables, and
> use it to mass create the xml file.  The beauty of this method is once you
> are done creating the xml files, you can reload them over and over.
>
> Another option is to use an ETL like talons which can automate the whole
> process, but you must be familiar with the entities.   This would take
> expertise in using both the ETL and ofbiz entities and order.
>
> Sent from my BlackBerry® PlayBook™
> www.blackberry.com
>
> ------------------------------
> *From:* "Forrest Rae"
> *To:* "[hidden email]"
> *Sent:* December 12, 2014 1:20 PM
> *Subject:* Re: Data File Tools and Hierarchical Flat Files
>
> Heya Jacques,
>
> I did read the pages, actually studied them extensively and read the
> source code to the tool.  I wouldn't bother the list otherwise.  :)
>
> Adrian, that's a good suggestion, thanks!
>
> -Forrest
>
> On 12/12/2014 12:27 PM, Jacques Le Roux wrote:
>> I did not read these pages again (a long time now), but they might help,
>> and you may give us your opinion:
>>
>> https://cwiki.apache.org/confluence/display/OFBIZ/Handling+of+External+data
>> https://cwiki.apache.org/confluence/display/OFBIZ/Import+Data+Using+Apache+POI+api
>>
>>
>> Jacques
>>
>> Le 12/12/2014 18:02, Adrian Crum a écrit :
>>> The easiest way to use the tool is to import the CSV file into a
>>> specialized table to contain the data. After the import, invoke a
>>> service that processes the data in the table.
>>>
>>> Adrian Crum
>>> Sandglass Software
>>> www.sandglass-software.com

--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102

[Ron Wheeler]

You can do that as well (talk to database or web service) but sometimes
it is easier to work through the Data Import function of the software
since the vendor (OFBiz in this case) may put some effort into
maintaining a stable Data Import format while the underlying services
and data structures change with each release.

Sometimes both are unstable or you may find that the Data Import does
not have all of the functionality of direct access to the underlying
service or database but that is really outside the control of the system
administrator of the system.

The key thing is to have a transformation tool that makes it easy to get
data into a format that the target system needs and a validation
capability that makes it easy for an administrator with no IT experience
to understand and fix or report data errors in the originating system
(Payroll data that has a person assigned to a boss that is no longer in
the system or an supplier invoice that has the wrong SKU) without having
to deal with obscure error messages or blocking the rest of the
transactions.

Ron


On 14/12/2014 5:59 PM, Mike Z wrote:

> Normally with an ETL you would use it to read an xml/CSV file (I.e from a
> supplier ) and directly update the DB, rather than create an intermediary
> xml file.  Interesting option though.
>
> Sent from my BlackBerry® PlayBook™
> www.blackberry.com
>
> ------------------------------
> *From:* "Ron Wheeler" <[hidden email]>
> *To:* "[hidden email]" <[hidden email]>
> *Sent:* December 13, 2014 2:45 PM
> *Subject:* Re: Data File Tools and Hierarchical Flat Files
>
> I have an ETL program and would be happy to work in configuring it to
> produce XML files suitable for OFBiz.
> It currently has a plug-in that produces HTML output of tables to HTML
> with attributes that make it easy to style with css.
> I need to remove the HTML prologue,HTML epilogue and the attributes to
> have just the table in XML to make a new plug-in to output a simpler XML
> structure suitable for OFBiz.
>
> It currently reads CSV and Excel so it would be a good front-end for
> OFBiz integrations where data is extracted from other systems and needs
> to be fed to the OFBiz
> It also has a lot of transformation and validation plug-ins if you need
> to do things to alter the data (rename columns, set defaults, add new
> columns, reformat dates, map data using mapping files, etc) and validate
> the output (table lookups, field length, uniqueness, etc.)
> It produces an audit report of the actions done and an error log that
> writes error messages in a format that makes sense to non-technical
> people ( "while validating products found duplicate ids in row 7 and
> 167" ) who have to fix the original system and don't want to decipher
> SQL errors "Constraint violation while creating record".
>
> It was developed to support the transfer of HRIS (payroll data) to LMS
> (Learning Management Systems).
>
> Ron
> Ron
> On 13/12/2014 4:46 PM, Mike Z wrote:
>> Take the csv, convert it to xml, and just manually load it from the command
>> line.
>>
>> Of course,  order is significant.   You have to load in catalog and
>> categories prior to loading products.   Each of these can be a separate xml
>> (recommended) or you can create one gigantic xml that does it all.
>>
>> If you want top speed then this is how to do it.  How to convert from csv
>> to xml?  Hopefully you are handy with Perl, shell, or another language to
>> convert.   Take a typical product, create a template with variables, and
>> use it to mass create the xml file.  The beauty of this method is once you
>> are done creating the xml files, you can reload them over and over.
>>
>> Another option is to use an ETL like talons which can automate the whole
>> process, but you must be familiar with the entities.   This would take
>> expertise in using both the ETL and ofbiz entities and order.
>>
>> Sent from my BlackBerry® PlayBook™
>> www.blackberry.com
>>
>> ------------------------------
>> *From:* "Forrest Rae"
>> *To:* "[hidden email]"
>> *Sent:* December 12, 2014 1:20 PM
>> *Subject:* Re: Data File Tools and Hierarchical Flat Files
>>
>> Heya Jacques,
>>
>> I did read the pages, actually studied them extensively and read the
>> source code to the tool.  I wouldn't bother the list otherwise.  :)
>>
>> Adrian, that's a good suggestion, thanks!
>>
>> -Forrest
>>
>> On 12/12/2014 12:27 PM, Jacques Le Roux wrote:
>>> I did not read these pages again (a long time now), but they might help,
>>> and you may give us your opinion:
>>>
>>> https://cwiki.apache.org/confluence/display/OFBIZ/Handling+of+External+data
>>> https://cwiki.apache.org/confluence/display/OFBIZ/Import+Data+Using+Apache+POI+api
>>>
>>>
>>> Jacques
>>>
>>> Le 12/12/2014 18:02, Adrian Crum a écrit :
>>>> The easiest way to use the tool is to import the CSV file into a
>>>> specialized table to contain the data. After the import, invoke a
>>>> service that processes the data in the table.
>>>>
>>>> Adrian Crum
>>>> Sandglass Software
>>>> www.sandglass-software.com
>
> --
> Ron Wheeler
> President
> Artifact Software Inc
> email: [hidden email]
> skype: ronaldmwheeler
> phone: [phone], ext 102
>

--
Ron Wheeler
President
Artifact Software Inc
email: [hidden email]
skype: ronaldmwheeler
phone: [phone], ext 102