cancel
Showing results for 
Search instead for 
Did you mean: 

How to convert Excel Sheet to XSD using XML SPY

former_member203627
Participant
0 Kudos

Hi

Does anyone know how to convert Excel file to XSD using XML Spy...

I have huge data type list to create so was wondering if I can convert Excel file to XSD, that way can save lots of time.

If anyone has help to do this please forward to me.

regards

Ria

Accepted Solutions (0)

Answers (6)

Answers (6)

prasannakrishna_mynam
Contributor
0 Kudos

Hello,

Well though u have genarated the XSD from Excel, because of the name space in XSD, you are not able to import that XSD. Try as mentioned below.

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

<!-- W3C Schema generated by XMLSpy v2009 (http://www.altova.com) -->

<!--<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> -->

<!change this name space to the one of IR genarated one>>

<ns0:ur MessageType:ns0="ur namespace as specified in swcv ">

<xs:element name="_x0026_lt_x003b_ProfitCenter_x0026_gt_x003b_">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:enumeration value="BUKRS" />

<xs:enumeration value="CCODE" />

<xs:enumeration value="DDATE" />

</xs:restriction>

</xs:simpleType>

</xs:element>

<xs:element name="Row">

<xs:complexType>

<xs:sequence>

<xs:element ref="_x0026_lt_x003b_ProfitCenter_x0026_gt_x003b_" />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element name="Import">

<xs:complexType>

<xs:sequence>

<xs:element ref="Row" maxOccurs="unbounded" />

</xs:sequence>

</xs:complexType>

</xs:element>

</ns0:ur>

well this has to resolve your problem, check the below link too

All the best...if no answer i better post this question..!!

Regards..

Krishna..

former_member203627
Participant
0 Kudos

Thanks for your answers.

prasannakrishna_mynam
Contributor
0 Kudos

Hello Ria,

I thought from my last post you might get the answer you are looking for, ok let me give a example based on the hints i have provided you in my last post.

You have XSD with 4 elements

BUKRS

CCODE

DDATE

PCENTER

enclose these with global type as below

ProfitCenter XML FORMAT: <ProfitCenter>

BUKRS <BUKRS>

CCODE .

DDATE .

PCENTER

Here ProfitCenter is your root element, create Datatype in IR as ProfitCenter.

Try this....

Regards..

Krishna..

former_member203627
Participant
0 Kudos

Hi Krishna

Thanks for explaining me with example

Follwing is the excel sheet data

ProfitCenter

Bukrs

CCODE

DDate

I tried with you inputs and following is the XSD generated. but I guess it is incorrect...

I am sorry to bug you alot....but I want to get this correct.

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

- <!-- W3C Schema generated by XMLSpy v2009 (http://www.altova.com)

-->

- <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

- <xs:element name="_x0026_lt_x003b_ProfitCenter_x0026_gt_x003b_">

- <xs:simpleType>

- <xs:restriction base="xs:string">

<xs:enumeration value="BUKRS" />

<xs:enumeration value="CCODE" />

<xs:enumeration value="DDATE" />

</xs:restriction>

</xs:simpleType>

</xs:element>

- <xs:element name="Row">

- <xs:complexType>

- <xs:sequence>

<xs:element ref="_x0026_lt_x003b_ProfitCenter_x0026_gt_x003b_" />

</xs:sequence>

</xs:complexType>

</xs:element>

- <xs:element name="Import">

- <xs:complexType>

- <xs:sequence>

<xs:element ref="Row" maxOccurs="unbounded" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

Thanks for your help

Regards

Ria

prasannakrishna_mynam
Contributor
0 Kudos

Hello Ria,

It is good you managed to convert to XSD. Now you have XSD and nee to import that into IR.

Your XSD contains no root elements, you need to have a root element inorder to import into IR.

check following steps:

1) If more than one data type is defined in an XSD definition, the XSD editor can only import one of the globally defined data types. If the XSD data type to be imported contains references to other data types, the XSD editor does not import these referenced data types. After import, the references no longer link to these data types. However, you can import data types that reference each other to the Integration Repository one after the other. The Integration Builder recognizes automatically if data types for previously invalid references have been imported.

2) The XSD editor only imports XSD files if it supports their XSD language. The targetNamespace in the XSD document must either correspond to the Repository namespace where you are creating the data type for your XSD definition, or be undefined.

3) To import a data type from an XSD file to the Integration Repository, you must first check whether the XSD file contains more than one global data type definition. You must only import one definition in each import.

4) Use the Integration Builder to create a data type with the same name as the data type to be imported from the XSD file. If there is a targetNamespace in the XSD file, it must correspond to the Repository namespace where you are creating this data type.

5) Choose Tools Import XSD and select an XSD file from the dialog that follows.

6) If the imported data type contains references to other data types, these are displayed in a message. To import these data types, repeat the procedure in the previous two steps.

Go through these steps, thre 4th step you need to focus on as it is needed for you to create the datatype with the same name as in your root xsd.

Try this and let me know you managed to get that.

Regards,

Krishna..

former_member203627
Participant
0 Kudos

Hi Krishna

Can you give me one example.

I couldn't understand what you were saying about the globle and reference data types.

Let me calrify you why I want to have this. I have a huge number of list of fields in one data type which I have maintained in excel sheet and instead of creating them one by one I was wondering to have a easy way out. And I thought XML SPy can one of the tools which can create list of fields in data type and I can import the same in IR to save lots of time.

I understand the one which I created has some problem as it is not showing all three fields in the data

type.

Example

My data type name is ProfitCenter and fields for this data type would be

BUKRS

CCODE

DDATE

PCENTER

etc

So I want to create and XSD of ProfitCenter which will have 4 or more fields in it. And which I should be uploading to IR and then use that in mapping.

I hope I havn't confused you with the requirement. Please clarify...this would be great help if you can help.

regards

Ria

prasannakrishna_mynam
Contributor
0 Kudos

Hello Ria,

In XML Spy there is no direct way to convert from Excel to XSD, but you can achive this by following these steps, i have done some research on this and can able to get the target format.

1. In Excel save your file as CSV(You have this in save as options).

2. in XML Spy go to Convert Menu and select Import text file.

Convert->Import Text

3.Select 2 nd option (i.e Conver CSV to XML)

4. You will get the XML format, make sure your XML is valid.

5. Go to DTD/SCHEMA->Genarate DTD/Schema and follow the steps to genarate XSD.

Hope you will get that, All the BEST...

Regards...

Krishna.

Former Member
0 Kudos

Hi,

I tried doing the conversion of .csv to xml.But it is not working.I am not getting the valid xml file.

Thanks,

Tiny

former_member203627
Participant
0 Kudos

Hi Krishna,

I did follow the steps you mentioned, and it did created xsd file. But when I try to import the same in integeration builder I am getting folloiwng error. I guess I might need to do somthing else as the name space and other relevant information is not availble in XSD that's why it is not compatible with IR.

Activation of the change list canceled Check result for External Definition table | http://ccamatil.com/amatil_i_test/hema/: Document check found errors Cause: Unable to recognize the loaded document as valid WSDL Tag schema is not permitted as a root element. definitions is expected

Excel File which is csv format has following data

bukrs

ccode

ddate

Whereas the xsd generated is as follows

<?xml version="1.0" encoding="ISO-8859-1"?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="bukrs">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:enumeration value="ccode" />

<xs:enumeration value="ddate" />

</xs:restriction>

</xs:simpleType>

</xs:element>

<xs:element name="Row">

<xs:complexType>

<xs:sequence>

<xs:element ref="bukrs" />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element name="Import">

<xs:complexType>

<xs:sequence>

<xs:element ref="Row" maxOccurs="unbounded" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

Please help ! ! ! !

How can I have excel file data converted to XSD which is compatible in IR so that it can generate data types..... Somewhere I would need to mention datatype name ?????

Regards

Ria

sunil_singh13
Active Contributor
0 Kudos

Hi Ria,

Save your Excel File as xml (Save as xml file ).

Drag this file in XML SPY. Select option DTD/Schema in that select Generate XSD.

Thanks

Sunil Singh

former_member203627
Participant
0 Kudos

.