You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Grahame Jordan <gb...@theforce.com.au> on 2008/03/17 03:42:19 UTC
Import Data
Hi,
Being a noob I am just wondering how to import existing data into the
application.
I have an existing product/pricelist, the sales guys are using Goldmine
and accounts are using Quickbooks.
Initially I want to be using the pricelist ASAP.
I have the svn trunk checked out and running.
Many Thanks
Grahame Jrodan
Re: Import Data
Posted by Ritz123 <ri...@gmail.com>.
Sure. Please do.
jacques.le.roux wrote:
>
> From: "Ritz123" <ri...@gmail.com>
>> I have mentioned a few things that I learned and thought would be useful
>> to
>> others about Ofbiz entities and in general at ritesht.blogspot.com
>>
>> Hope that helps.
>
> If you agree, I will review and put a link from
> http://docs.ofbiz.org/display/OFBIZ/FAQ+-+Tips+-+Tricks+-+Cookbook+-+HowTo
>
> Jacques
>
>
--
View this message in context: http://www.nabble.com/Import-Data-tp16087500p16211792.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: Import Data
Posted by Jacques Le Roux <ja...@les7arts.com>.
From: "Ritz123" <ri...@gmail.com>
> I have mentioned a few things that I learned and thought would be useful to
> others about Ofbiz entities and in general at ritesht.blogspot.com
>
> Hope that helps.
If you agree, I will review and put a link from http://docs.ofbiz.org/display/OFBIZ/FAQ+-+Tips+-+Tricks+-+Cookbook+-+HowTo
Jacques
Infinity Values fail for Postgres
Posted by "skip@thedevers" <sk...@thedevers.org>.
if you pass a value of infinity (Double.POSITIVE_INFINITY) you get an error
like this Note that averageCost,Infinity is being converted to "1.#INF" for
the Postgres db (Using Postgres 8.2):
org.ofbiz.entity.GenericEntityException: while inserting:
[GenericEntity:InventorySoldSummary][aceptblAverage,0.0(java.lang.Double)][a
cptblMths,12(java.lang.Long)][averageCost,Infinity(java.lang.Double)][avgSal
esPrice,Infinity(java.lang.Double)][avgShipDays,2.0(java.lang.Double)][creat
edStamp,2008-03-19
19:17:14.984(java.sql.Timestamp)][createdTxStamp,2008-03-19
19:17:14.984(java.sql.Timestamp)][enumId,EOQ_NOT_SOLD(java.lang.String)][fac
ilityId,NT-Kent(java.lang.String)][lastUpdatedStamp,2008-03-19
19:17:14.984(java.sql.Timestamp)][lastUpdatedTxStamp,2008-03-19
19:17:14.984(java.sql.Timestamp)][month1,0.0(java.lang.Double)][month10,0.0(
java.lang.Double)][month11,0.0(java.lang.Double)][month12,0.0(java.lang.Doub
le)][month2,0.0(java.lang.Double)][month3,0.0(java.lang.Double)][month4,0.0(
java.lang.Double)][month5,0.0(java.lang.Double)][month6,0.0(java.lang.Double
)][month7,0.0(java.lang.Double)][month8,0.0(java.lang.Double)][month9,0.0(ja
va.lang.Double)][newestSaleDays,153(java.lang.Long)][normMth1,0.0(java.lang.
Double)][normMth10,0.0(java.lang.Double)][normMth11,0.0(java.lang.Double)][n
ormMth12,0.0(java.lang.Double)][normMth2,0.0(java.lang.Double)][normMth3,0.0
(java.lang.Double)][normMth4,0.0(java.lang.Double)][normMth5,0.0(java.lang.D
ouble)][normMth6,0.0(java.lang.Double)][normMth7,0.0(java.lang.Double)][norm
Mth8,0.0(java.lang.Double)][normMth9,0.0(java.lang.Double)][numberofsales,2(
java.lang.Long)][oldestSaleDays,153(java.lang.Long)][productId,BE22461580A15
(java.lang.String)][qtrWeightAvg,0.0(java.lang.Double)][qtyToOrder,0.0(java.
lang.Double)][quantityAverage,0.0(java.lang.Double)][quantityMax,1.0(java.la
ng.Double)][quantityMin,-1.0(java.lang.Double)][quantityTotal,0.0(java.lang.
Double)][recommended,0.0(java.lang.Double)][stdDeviation,1.0(java.lang.Doubl
e)][supplierPartyId,SHOP(java.lang.String)][weightAvgQty,0.0(java.lang.Doubl
e)] (SQL Exception while executing the following:INSERT INTO
public.INVENTORY_SOLD_SUMMARY (PRODUCT_ID, FACILITY_ID, SUPPLIER_PARTY_ID,
QUANTITY_TOTAL, ACPTBL_MTHS, QUANTITY_MIN, QUANTITY_MAX, ACEPTBL_AVERAGE,
WEIGHT_AVG_QTY, QTR_WEIGHT_AVG, QUANTITY_AVERAGE, STD_DEVIATION,
RECOMMENDED, QTY_TO_ORDER, AVAILABLE, QTY_BACK_ORDERED, AVERAGE_COST,
AVG_SALES_PRICE, NUMBEROFSALES, OLDEST_SALE_DAYS, NEWEST_SALE_DAYS,
AVG_SHIP_DAYS, MONTH1, MONTH2, MONTH3, MONTH4, MONTH5, MONTH6, MONTH7,
MONTH8, MONTH9, MONTH10, MONTH11, MONTH12, NORM_MTH1, NORM_MTH2, NORM_MTH3,
NORM_MTH4, NORM_MTH5, NORM_MTH6, NORM_MTH7, NORM_MTH8, NORM_MTH9,
NORM_MTH10, NORM_MTH11, NORM_MTH12, ENUM_ID, LAST_UPDATED_STAMP,
LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ERROR:
invalid input syntax for type numeric: "1.#INF"))
Works fine for localderby.
I fixed this in this instance by passing -1.00 which has no valid value for
this case, but it looks like a bug to me because I can imagine the need to
store infinity.
Skip
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.21.7/1333 - Release Date: 3/18/2008
8:10 AM
Re: Import Data
Posted by Ritz123 <ri...@gmail.com>.
For Data Import, you need to understand the entity relationships (data model)
both of the original application as well as Ofbiz. Demo*.xml files as well
as the book and online resources will help you understand the ofbiz data
model. Once you do that, there are couple of options that I went with to
actually import the data
1. To use scriptella.javaforge.net - a very handy and flexible framework for
massaging/importing data
2. Using Entity engine - I managed to separate Entity Engine out and write
utility which allows you to specify mapping fields from one data source to
the Ofbiz one.
I have mentioned a few things that I learned and thought would be useful to
others about Ofbiz entities and in general at ritesht.blogspot.com
Hope that helps.
Grahame Jordan wrote:
>
> Hi,
>
> Being a noob I am just wondering how to import existing data into the
> application.
>
> I have an existing product/pricelist, the sales guys are using Goldmine
> and accounts are using Quickbooks.
>
> Initially I want to be using the pricelist ASAP.
> I have the svn trunk checked out and running.
> Many Thanks
>
> Grahame Jrodan
>
>
--
View this message in context: http://www.nabble.com/Import-Data-tp16087500p16184992.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: Import Data
Posted by Mridul Pathak <mr...@hotwaxmedia.com>.
You can refer to applications/ecommerce/data/DemoProduct.xml. It has Demo
Data for Facility, Catalog, ProductCategories, Variety of Products
(Physical, Digital, Virtual and Varient, Subscription, Fin Account etc.)
and the association between them. I think this will help you to understand
the database structure as well.
On Tue, Mar 18, 2008 at 9:10 AM, Grahame Jordan <gb...@theforce.com.au> wrote:
> Hi,
>
> Thanks for your replies, they are of great help.
>
> So far I have written some functions in perl to take the data from the
> original database and write it all in xml.
> This looks good so far however I am not familiar with the requirements
> of the database structure etc.
>
> Am I taking the right approach by creating Product_Categories ->
> Products -> Price.
>
>
> Cheers
>
>
> Grahame
>
>
> skip@thedevers wrote:
> > The hard part is getting the external data into an xml format and then
> into
> > the ofbiz schema. Two of the approaches that work well is to make use
> of
> > Microsoft Access. I am not a big fan, but it is fairly easy to use.
> What I
> > did was to write a custom java program that generated the xml files,
> used
> > the Web Tools - Import to import the data into temporary tables, then
> run a
> > service to move the data from the temporary tables into Ofbiz. I used
> the
> > data import tools from Opentaps as a starting point and spent probably a
> > month fine tuning them. I did it this way because I had three
> businesses to
> > do. For a one off, I think the Access tools are a better choice.
> >
> > Quickbooks has a developers toolset that can help you.
> >
> > Skip
> >
> > -----Original Message-----
> > From: Brajesh Patel [mailto:brajeshpatel07@gmail.com]
> > Sent: Sunday, March 16, 2008 11:08 PM
> > To: user@ofbiz.apache.org
> > Subject: Re: Import Data
> >
> >
> > hi Grahame,
> >
> > In web tools there's a section for Entity XML Tools for
> > importing and exporting data from files. For importing data from files
> > you can use "XML Data Import" option. Here you'll have to specify the
> > absolute path of the data file and check the options as per
> > your requirements (we've used create "Dummy" FKs).
> >
> > Thanks & Regards
> >
> > On Mon, Mar 17, 2008 at 8:12 AM, Grahame Jordan <gb...@theforce.com.au>
> wrote:
> >
> >
> >> Hi,
> >>
> >> Being a noob I am just wondering how to import existing data into the
> >> application.
> >>
> >> I have an existing product/pricelist, the sales guys are using Goldmine
> >> and accounts are using Quickbooks.
> >>
> >> Initially I want to be using the pricelist ASAP.
> >> I have the svn trunk checked out and running.
> >> Many Thanks
> >>
> >> Grahame Jrodan
> >>
> >>
> >
> >
> >
> > --
> > Brajesh Patel
> > HotWax Media Indore(MP)
> > 09926276854
> > hotwaxmedia.com
> >
> > No virus found in this incoming message.
> > Checked by AVG.
> > Version: 7.5.519 / Virus Database: 269.21.7/1329 - Release Date:
> 3/14/2008
> > 12:33 PM
> >
> > No virus found in this outgoing message.
> > Checked by AVG.
> > Version: 7.5.519 / Virus Database: 269.21.7/1329 - Release Date:
> 3/14/2008
> > 12:33 PM
> >
> >
>
>
--
Thanks & Regards
Mridul Pathak
Hotwax Media
http://www.hotwaxmedia.com
mridul.pathak@hotwaxmedia.com
__________________________________
Office : 509.855.4113
Mobile : +919425926892
Re: Import Data
Posted by Grahame Jordan <gb...@theforce.com.au>.
Hi,
Thanks for your replies, they are of great help.
So far I have written some functions in perl to take the data from the
original database and write it all in xml.
This looks good so far however I am not familiar with the requirements
of the database structure etc.
Am I taking the right approach by creating Product_Categories ->
Products -> Price.
Cheers
Grahame
skip@thedevers wrote:
> The hard part is getting the external data into an xml format and then into
> the ofbiz schema. Two of the approaches that work well is to make use of
> Microsoft Access. I am not a big fan, but it is fairly easy to use. What I
> did was to write a custom java program that generated the xml files, used
> the Web Tools - Import to import the data into temporary tables, then run a
> service to move the data from the temporary tables into Ofbiz. I used the
> data import tools from Opentaps as a starting point and spent probably a
> month fine tuning them. I did it this way because I had three businesses to
> do. For a one off, I think the Access tools are a better choice.
>
> Quickbooks has a developers toolset that can help you.
>
> Skip
>
> -----Original Message-----
> From: Brajesh Patel [mailto:brajeshpatel07@gmail.com]
> Sent: Sunday, March 16, 2008 11:08 PM
> To: user@ofbiz.apache.org
> Subject: Re: Import Data
>
>
> hi Grahame,
>
> In web tools there's a section for Entity XML Tools for
> importing and exporting data from files. For importing data from files
> you can use "XML Data Import" option. Here you'll have to specify the
> absolute path of the data file and check the options as per
> your requirements (we've used create "Dummy" FKs).
>
> Thanks & Regards
>
> On Mon, Mar 17, 2008 at 8:12 AM, Grahame Jordan <gb...@theforce.com.au> wrote:
>
>
>> Hi,
>>
>> Being a noob I am just wondering how to import existing data into the
>> application.
>>
>> I have an existing product/pricelist, the sales guys are using Goldmine
>> and accounts are using Quickbooks.
>>
>> Initially I want to be using the pricelist ASAP.
>> I have the svn trunk checked out and running.
>> Many Thanks
>>
>> Grahame Jrodan
>>
>>
>
>
>
> --
> Brajesh Patel
> HotWax Media Indore(MP)
> 09926276854
> hotwaxmedia.com
>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 7.5.519 / Virus Database: 269.21.7/1329 - Release Date: 3/14/2008
> 12:33 PM
>
> No virus found in this outgoing message.
> Checked by AVG.
> Version: 7.5.519 / Virus Database: 269.21.7/1329 - Release Date: 3/14/2008
> 12:33 PM
>
>
RE: Import Data
Posted by "skip@thedevers" <sk...@thedevers.org>.
The hard part is getting the external data into an xml format and then into
the ofbiz schema. Two of the approaches that work well is to make use of
Microsoft Access. I am not a big fan, but it is fairly easy to use. What I
did was to write a custom java program that generated the xml files, used
the Web Tools - Import to import the data into temporary tables, then run a
service to move the data from the temporary tables into Ofbiz. I used the
data import tools from Opentaps as a starting point and spent probably a
month fine tuning them. I did it this way because I had three businesses to
do. For a one off, I think the Access tools are a better choice.
Quickbooks has a developers toolset that can help you.
Skip
-----Original Message-----
From: Brajesh Patel [mailto:brajeshpatel07@gmail.com]
Sent: Sunday, March 16, 2008 11:08 PM
To: user@ofbiz.apache.org
Subject: Re: Import Data
hi Grahame,
In web tools there's a section for Entity XML Tools for
importing and exporting data from files. For importing data from files
you can use "XML Data Import" option. Here you'll have to specify the
absolute path of the data file and check the options as per
your requirements (we've used create "Dummy" FKs).
Thanks & Regards
On Mon, Mar 17, 2008 at 8:12 AM, Grahame Jordan <gb...@theforce.com.au> wrote:
> Hi,
>
> Being a noob I am just wondering how to import existing data into the
> application.
>
> I have an existing product/pricelist, the sales guys are using Goldmine
> and accounts are using Quickbooks.
>
> Initially I want to be using the pricelist ASAP.
> I have the svn trunk checked out and running.
> Many Thanks
>
> Grahame Jrodan
>
--
Brajesh Patel
HotWax Media Indore(MP)
09926276854
hotwaxmedia.com
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.21.7/1329 - Release Date: 3/14/2008
12:33 PM
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.21.7/1329 - Release Date: 3/14/2008
12:33 PM
Re: Import Data
Posted by Brajesh Patel <br...@gmail.com>.
hi Grahame,
In web tools there's a section for Entity XML Tools for
importing and exporting data from files. For importing data from files
you can use "XML Data Import" option. Here you'll have to specify the
absolute path of the data file and check the options as per
your requirements (we've used create "Dummy" FKs).
Thanks & Regards
On Mon, Mar 17, 2008 at 8:12 AM, Grahame Jordan <gb...@theforce.com.au> wrote:
> Hi,
>
> Being a noob I am just wondering how to import existing data into the
> application.
>
> I have an existing product/pricelist, the sales guys are using Goldmine
> and accounts are using Quickbooks.
>
> Initially I want to be using the pricelist ASAP.
> I have the svn trunk checked out and running.
> Many Thanks
>
> Grahame Jrodan
>
--
Brajesh Patel
HotWax Media Indore(MP)
09926276854
hotwaxmedia.com