OFBiz › OFBiz - User

[OFBiz] Users - XLS to XML

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

10 messages Options

michal-2

[OFBiz] Users - XLS to XML

Do you guys know any free tools either for windows or linux to convert excel files to xml and then import them to Ofbiz??

I was looking on google and couldn't find anything that would work.

BTW, What is the best way to import list of products to ofbiz?

Thanks

Michal

_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

Jacopo Cappellato

Re: [OFBiz] Users - XLS to XML

Hi Michal,

I'd suggest:

From Excel: export a csv file
Using the OFBiz Data file tool: transform it into an entity XML file
Using the OFBiz XML entity import tool: import everything in OFBiz

Jacopo

michal wrote:

> Do you guys know any free tools either for windows or linux to convert
> excel files to xml and then import them to Ofbiz??
> I was looking on google and couldn't find anything that would work.
>
> BTW, What is the best way to import list of products to ofbiz?
>
> Thanks
> Michal
>
>
> ------------------------------------------------------------------------
>
>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

michal-2

Re: [OFBiz] Users - XLS to XML

Thanks. I will try and see how it works.
does the columns have to be in a specific order or it doesnt matter ?

----- Original Message -----
From: "Jacopo Cappellato" <[hidden email]>
To: "OFBiz Users / Usage Discussion" <[hidden email]>
Sent: Wednesday, November 09, 2005 11:48 AM
Subject: Re: [OFBiz] Users - XLS to XML

> Hi Michal,
>
> I'd suggest:
>
> From Excel: export a csv file
> Using the OFBiz Data file tool: transform it into an entity XML file
> Using the OFBiz XML entity import tool: import everything in OFBiz
>
> Jacopo
>
> michal wrote:
>> Do you guys know any free tools either for windows or linux to convert
>> excel files to xml and then import them to Ofbiz??
>> I was looking on google and couldn't find anything that would work.
>>
>> BTW, What is the best way to import list of products to ofbiz?
>>
>> Thanks
>> Michal
>>
>>
>> ------------------------------------------------------------------------
>>
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>
>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

David E. Jones

Re: [OFBiz] Users - XLS to XML

Or perhaps just make a Word mail-merge template to create the XML
snippets...

-David

On Nov 9, 2005, at 12:06 PM, michal wrote:

> Thanks. I will try and see how it works.
> does the columns have to be in a specific order or it doesnt matter ?
>
> ----- Original Message ----- From: "Jacopo Cappellato" <[hidden email]>
> To: "OFBiz Users / Usage Discussion" <[hidden email]>
> Sent: Wednesday, November 09, 2005 11:48 AM
> Subject: Re: [OFBiz] Users - XLS to XML
>
>
>
>> Hi Michal,
>> I'd suggest:
>> From Excel: export a csv file
>> Using the OFBiz Data file tool: transform it into an entity XML file
>> Using the OFBiz XML entity import tool: import everything in OFBiz
>> Jacopo
>> michal wrote:
>>
>>> Do you guys know any free tools either for windows or linux to
>>> convert excel files to xml and then import them to Ofbiz??
>>> I was looking on google and couldn't find anything that would work.
>>> BTW, What is the best way to import list of products to ofbiz?
>>> Thanks
>>> Michal
>>> --------------------------------------------------------------------
>>> ----
>>> _______________________________________________
>>> Users mailing list
>>> [hidden email]
>>> http://lists.ofbiz.org/mailman/listinfo/users
>>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/users
>

_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

michal-2

Re: [OFBiz] Users - XLS to XML

In reply to this post by Jacopo Cappellato

I found this option in webtools

Data Filename or URL: Is URL?:
Definition Filename or URL: Is URL?:
Data File Definition Name:
Save to file:
Save to entity xml file:

I entered path for the files and it didnt work. I guess my path was wrong.
Should i enter absolute path on the server or ?? Could you give me some
instructions ?
Michal

----- Original Message -----
From: "Jacopo Cappellato" <[hidden email]>
To: "OFBiz Users / Usage Discussion" <[hidden email]>
Sent: Wednesday, November 09, 2005 11:48 AM
Subject: Re: [OFBiz] Users - XLS to XML

> Hi Michal,
>
> I'd suggest:
>
> From Excel: export a csv file
> Using the OFBiz Data file tool: transform it into an entity XML file
> Using the OFBiz XML entity import tool: import everything in OFBiz
>
> Jacopo
>
> michal wrote:
>> Do you guys know any free tools either for windows or linux to convert
>> excel files to xml and then import them to Ofbiz??
>> I was looking on google and couldn't find anything that would work.
>> BTW, What is the best way to import list of products to ofbiz?
>> Thanks
>> Michal
>>
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>
>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

Jacopo Cappellato

Re: [OFBiz] Users - XLS to XML

Hi michal,

yes the paths are absolute paths of the server.
These are the fields you should fill:

Data Filename or URL: --> the path to the csv file
Definition Filename or URL: --> the path to the def file
Data File Definition Name: --> the name of the definition in the def file
Save to entity xml file: --> the path to the xml file you want to create

For example see the attached definition file.
It can be used to parse a csv file in the format:

productId#internalName#productName#description#longDescription

Let's say the csv file is in: \tmp\data.csv
and the above def file on: \tmp\product_descriptions.xml
and that you want to create an entity xml file called: \tmp\dataxml.xml

You have to set:

Data Filename or URL: --> \tmp\data.csv
Definition Filename or URL: --> \tmp\product_descriptions.xml
Data File Definition Name: --> "descriptions"
Save to entity xml file: --> \tmp\dataxml.xml

The \tmp\dataxml.xml will be created, then you can easily import it into
OFBiz.

Hope this helps.

Jacopo

michal wrote:

> I found this option in webtools
>
> Data Filename or URL: Is URL?:
> Definition Filename or URL: Is URL?:
> Data File Definition Name:
> Save to file:
> Save to entity xml file:
>
> I entered path for the files and it didnt work. I guess my path was
> wrong. Should i enter absolute path on the server or ?? Could you give
> me some instructions ?
> Michal
>
>
> ----- Original Message ----- From: "Jacopo Cappellato" <[hidden email]>
> To: "OFBiz Users / Usage Discussion" <[hidden email]>
> Sent: Wednesday, November 09, 2005 11:48 AM
> Subject: Re: [OFBiz] Users - XLS to XML
>
>
>> Hi Michal,
>>
>> I'd suggest:
>>
>> From Excel: export a csv file
>> Using the OFBiz Data file tool: transform it into an entity XML file
>> Using the OFBiz XML entity import tool: import everything in OFBiz
>>
>> Jacopo
>>
>> michal wrote:
>>
>>> Do you guys know any free tools either for windows or linux to
>>> convert excel files to xml and then import them to Ofbiz??
>>> I was looking on google and couldn't find anything that would work.
>>> BTW, What is the best way to import list of products to ofbiz?
>>> Thanks
>>> Michal
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>> _______________________________________________
>>> Users mailing list
>>> [hidden email]
>>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>>
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>
>
>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/users
>

<?xml version="1.0" encoding="UTF-8" ?>

<data-files xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://www.ofbiz.org/dtds/datafiles.xsd">

<data-file name="descriptions" separator-style="delimited" delimiter="#" type-code="text">
<record name="Product" limit="many">
<field name="productId" type="String"/>
<field name="internalName" type="String"/>
<field name="productName" type="String"/>
<field name="description" type="String"/>
<field name="longDescription" type="String"/>
</record>
</data-file>

</data-files>

_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

michal-2

Re: [OFBiz] Users - XLS to XML

I followed your steps and It wasn't working. When i click on run the reloads
quickly and nothing happens.
I checked console.log and this what i get:
178905154 (http-0.0.0.0-8443-Processor4) [
ContextFilter.java:192:INFO ] [Request]:
/webtools/control/viewdatafile
178905155 (http-0.0.0.0-8443-Processor4) [
ControlServlet.java:98 :INFO ] The character encoding
of the request is: [null]. The character encoding we
will use for the request and response is: [UTF-8]
178905156 (http-0.0.0.0-8443-Processor4) [
ControlServlet.java:134:DEBUG] [[[viewdatafile]
Servlet Starting, doing setup- total:0.0,since
last(Begin):0.0]]
178905157 (http-0.0.0.0-8443-Processor4) [
ControlServlet.java:183:DEBUG] [[[viewdatafile] Setup
done, doing Event(s) and View(s)- total:0.0,since
last([viewdatafile] Se...):0.0]]
178905158 (http-0.0.0.0-8443-Processor4) [
RequestHandler.java:210:INFO ] [Processing Request]:
viewdatafile178905160 (http-0.0.0.0-8443-Processor4) [
RequestManager.java:123:INFO ]
[RequestManager.getRequestAttribute] Value for
attribute "null" of uri "viewdatafile" not found
178905160 (http-0.0.0.0-8443-Processor4) [
RequestHandler.java:372:INFO ]
[RequestHandler.doRequest]: Response is a view.
178905161 (http-0.0.0.0-8443-Processor4) [
RequestHandler.java:524:INFO ] servletName=control,
view=viewdatafile
178905162 (http-0.0.0.0-8443-Processor4) [
Region.java:117:INFO ] real path for
[/templates/main_template.jsp] is:
/sequoia/framework/webtools/webapp/webtools/templates/main_template.jsp
178905274 (http-0.0.0.0-8443-Processor4) [
ControlServlet.java:258:DEBUG] [[[viewdatafile] Done
rendering page, Servlet Finished- total:0.116,since
last([viewdatafile] Se...):0.116]]

Also I have changed product_description.xml delimeter from # to , because
that is what I have in my csv data file.
Michal

----- Original Message -----
From: "Jacopo Cappellato" <[hidden email]>
To: "OFBiz Users / Usage Discussion" <[hidden email]>
Sent: Friday, November 11, 2005 12:03 PM
Subject: Re: [OFBiz] Users - XLS to XML

> Hi michal,
>
> yes the paths are absolute paths of the server.
> These are the fields you should fill:
>
> Data Filename or URL: --> the path to the csv file
> Definition Filename or URL: --> the path to the def file
> Data File Definition Name: --> the name of the definition in the def file
> Save to entity xml file: --> the path to the xml file you want to create
>
> For example see the attached definition file.
> It can be used to parse a csv file in the format:
>
> productId#internalName#productName#description#longDescription
>
> Let's say the csv file is in: \tmp\data.csv
> and the above def file on: \tmp\product_descriptions.xml
> and that you want to create an entity xml file called: \tmp\dataxml.xml
>
> You have to set:
>
> Data Filename or URL: --> \tmp\data.csv
> Definition Filename or URL: --> \tmp\product_descriptions.xml
> Data File Definition Name: --> "descriptions"
> Save to entity xml file: --> \tmp\dataxml.xml
>
> The \tmp\dataxml.xml will be created, then you can easily import it into
> OFBiz.
>
> Hope this helps.
>
> Jacopo
>
>
> michal wrote:
>> I found this option in webtools
>>
>> Data Filename or URL: Is URL?:
>> Definition Filename or URL: Is URL?:
>> Data File Definition Name:
>> Save to file:
>> Save to entity xml file:
>>
>> I entered path for the files and it didnt work. I guess my path was
>> wrong. Should i enter absolute path on the server or ?? Could you give
>> me some instructions ?
>> Michal
>>
>>
>> ----- Original Message ----- From: "Jacopo Cappellato" <[hidden email]>
>> To: "OFBiz Users / Usage Discussion" <[hidden email]>
>> Sent: Wednesday, November 09, 2005 11:48 AM
>> Subject: Re: [OFBiz] Users - XLS to XML
>>
>>
>>> Hi Michal,
>>>
>>> I'd suggest:
>>>
>>> From Excel: export a csv file
>>> Using the OFBiz Data file tool: transform it into an entity XML file
>>> Using the OFBiz XML entity import tool: import everything in OFBiz
>>>
>>> Jacopo
>>>
>>> michal wrote:
>>>
>>>> Do you guys know any free tools either for windows or linux to
>>>> convert excel files to xml and then import them to Ofbiz??
>>>> I was looking on google and couldn't find anything that would work.
>>>> BTW, What is the best way to import list of products to ofbiz?
>>>> Thanks
>>>> Michal
>>>>
>>>>
>>>> ------------------------------------------------------------------------
>>>>
>>>> _______________________________________________
>>>> Users mailing list
>>>> [hidden email]
>>>> http://lists.ofbiz.org/mailman/listinfo/users
>>>
>>>
>>>
>>> _______________________________________________
>>> Users mailing list
>>> [hidden email]
>>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>>
>>
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users
>>
>
>
>

--------------------------------------------------------------------------------

>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

michal-2

Re: [OFBiz] Users - XLS to XML

In reply to this post by Jacopo Cappellato

Thanks your instructions were very helpful. I have one more question about
importing:
After I created xml file, I went to XML Data Import entered the file name
clicked Import and then I got on the bottom of the page message saying "Got
1 entities to write to the datasource". I was importing products and when I
search for these products they are not in ofbiz. What other steps I have to
do to import data ?
Michal
----- Original Message -----
From: "Jacopo Cappellato" <[hidden email]>
To: "OFBiz Users / Usage Discussion" <[hidden email]>
Sent: Friday, November 11, 2005 12:03 PM
Subject: Re: [OFBiz] Users - XLS to XML

Jacopo Cappellato

Re: [OFBiz] Users - XLS to XML

Are you sure that the product has not been imported?
The message:

"Got 1 entities to write to the datasource"

is a "success" response.

Could you send the generated xml file?
Please, use the Webtools--> Entity SQL processor to search for the
product you have imported:

select * from PRODUCT where PRODUCT_ID = 'theProductId'

Jacopo

michal wrote:

> Thanks your instructions were very helpful. I have one more question
> about importing:
> After I created xml file, I went to XML Data Import entered the file
> name clicked Import and then I got on the bottom of the page message
> saying "Got 1 entities to write to the datasource". I was importing
> products and when I search for these products they are not in ofbiz.
> What other steps I have to do to import data ?
> Michal
> ----- Original Message ----- From: "Jacopo Cappellato" <[hidden email]>
> To: "OFBiz Users / Usage Discussion" <[hidden email]>
> Sent: Friday, November 11, 2005 12:03 PM
> Subject: Re: [OFBiz] Users - XLS to XML
>
>
>> Hi michal,
>>
>> yes the paths are absolute paths of the server.
>> These are the fields you should fill:
>>
>> Data Filename or URL: --> the path to the csv file
>> Definition Filename or URL: --> the path to the def file
>> Data File Definition Name: --> the name of the definition in the def file
>> Save to entity xml file: --> the path to the xml file you want to create
>>
>> For example see the attached definition file.
>> It can be used to parse a csv file in the format:
>>
>> productId#internalName#productName#description#longDescription
>>
>> Let's say the csv file is in: \tmp\data.csv
>> and the above def file on: \tmp\product_descriptions.xml
>> and that you want to create an entity xml file called: \tmp\dataxml.xml
>>
>> You have to set:
>>
>> Data Filename or URL: --> \tmp\data.csv
>> Definition Filename or URL: --> \tmp\product_descriptions.xml
>> Data File Definition Name: --> "descriptions"
>> Save to entity xml file: --> \tmp\dataxml.xml
>>
>> The \tmp\dataxml.xml will be created, then you can easily import it into
>> OFBiz.
>>
>> Hope this helps.
>>
>> Jacopo
>>

_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

michal-2

Re: [OFBiz] Users - XLS to XML

Everything went fine. The product I was importing got into ofbiz.

I have another issue, which i kind of solved but maybe there is easier
solution to it.

Lets say I have three fields with this data:

(this data is not in ofbiz, I have it in xls)

productID,internalName,longDescription

1 pipe 3"

2 pipe 5"

Now when I save this in excel as tab, comma delimited values it looks like
that:

1,pipe,"3""

2,pipe,"3""

So then when I use data file tool in ofbiz I end up with double quotes for
values that are in "( "3"") in entity.xml file.

Is there a way to define text qualifier? How do you go around this issue?

My approach which isn't that good( I think there is better way haha)is:

I wrote script in perl that takes file with delimited values and process it;
changing commas to ! and getting rid of " around text.

Mike

----- Original Message -----
From: "Jacopo Cappellato" <[hidden email]>
To: "OFBiz Users / Usage Discussion" <[hidden email]>
Sent: Friday, November 18, 2005 1:13 AM
Subject: Re: [OFBiz] Users - XLS to XML

> Are you sure that the product has not been imported?
> The message:
>
> "Got 1 entities to write to the datasource"
>
> is a "success" response.
>
> Could you send the generated xml file?
> Please, use the Webtools--> Entity SQL processor to search for the product
> you have imported:
>
> select * from PRODUCT where PRODUCT_ID = 'theProductId'
>
>
> Jacopo
>
> michal wrote:
>> Thanks your instructions were very helpful. I have one more question
>> about importing:
>> After I created xml file, I went to XML Data Import entered the file name
>> clicked Import and then I got on the bottom of the page message saying
>> "Got 1 entities to write to the datasource". I was importing products and
>> when I search for these products they are not in ofbiz. What other steps
>> I have to do to import data ?
>> Michal
>> ----- Original Message ----- From: "Jacopo Cappellato" <[hidden email]>
>> To: "OFBiz Users / Usage Discussion" <[hidden email]>
>> Sent: Friday, November 11, 2005 12:03 PM
>> Subject: Re: [OFBiz] Users - XLS to XML
>>
>>
>>> Hi michal,
>>>
>>> yes the paths are absolute paths of the server.
>>> These are the fields you should fill:
>>>
>>> Data Filename or URL: --> the path to the csv file
>>> Definition Filename or URL: --> the path to the def file
>>> Data File Definition Name: --> the name of the definition in the def
>>> file
>>> Save to entity xml file: --> the path to the xml file you want to create
>>>
>>> For example see the attached definition file.
>>> It can be used to parse a csv file in the format:
>>>
>>> productId#internalName#productName#description#longDescription
>>>
>>> Let's say the csv file is in: \tmp\data.csv
>>> and the above def file on: \tmp\product_descriptions.xml
>>> and that you want to create an entity xml file called: \tmp\dataxml.xml
>>>
>>> You have to set:
>>>
>>> Data Filename or URL: --> \tmp\data.csv
>>> Definition Filename or URL: --> \tmp\product_descriptions.xml
>>> Data File Definition Name: --> "descriptions"
>>> Save to entity xml file: --> \tmp\dataxml.xml
>>>
>>> The \tmp\dataxml.xml will be created, then you can easily import it into
>>> OFBiz.
>>>
>>> Hope this helps.
>>>
>>> Jacopo
>>>
>
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/users

_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users