Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1067
  • Last Modified:

Importing XML into Excel 2007

I'm trying to import an xml into excel 2007 but I am getting results where the xml is on more than one line.
Here is the schema:
  <?xml version="1.0" ?>
- <!--  edited with XML Spy v4.4 U (http://www.xmlspy.com) by Beccy Boyd (Telelogic Technologies UK Ltd)
  -->
- <xs:schema targetNamespace="http://www.DOORSTDIntegration.com/IntegrationDatasetConfiguration.xsd" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://www.DOORSTDIntegration.com/IntegrationDatasetConfiguration.xsd" xmlns:mstns="http://www.DOORSTDIntegration.com/IntegrationDatasetConfiguration.xsd" elementFormDefault="qualified" attributeFormDefault="qualified" id="IntegrationDatasets">
- <xs:element name="IntegrationDatasets" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
- <xs:complexType>
- <xs:choice>
  <xs:element name="DataFormatVersion" minOccurs="0" />
- <xs:element name="IntegrationDataset" minOccurs="0" maxOccurs="unbounded">
- <xs:complexType>
- <xs:sequence>
  <xs:element name="DOORSDatabaseId" type="xs:string" minOccurs="0" />
  <xs:element name="DOORSDatabaseName" type="xs:string" minOccurs="0" />
  <xs:element name="TDDatabaseName" type="xs:string" minOccurs="0" />
  <xs:element name="IntegrationDatasetId" type="xs:string" minOccurs="0" />
  <xs:element name="IntegrationDatasetName" type="xs:string" minOccurs="0" />
  <xs:element name="DOORSDatasetId" type="xs:string" minOccurs="0" />
  <xs:element name="TDDatasetId" type="xs:string" minOccurs="0" />
  <xs:element name="ControllingApp" type="xs:string" minOccurs="0" />
  <xs:element name="LastSynchronizedDate" type="xs:string" minOccurs="0" />
  <xs:element name="LastModifiedDate" type="xs:string" minOccurs="0" />
- <xs:element name="AttributeMapping" minOccurs="0" maxOccurs="unbounded">
- <xs:complexType>
- <xs:sequence>
  <xs:element name="DOORSAttrib" type="xs:string" minOccurs="0" />
  <xs:element name="TDAttrib" type="xs:string" minOccurs="0" />
  <xs:element name="Source" type="xs:string" minOccurs="0" />
  </xs:sequence>
  </xs:complexType>
  </xs:element>
- <xs:element name="SyncSchedule" minOccurs="0" maxOccurs="unbounded">
- <xs:complexType>
- <xs:sequence>
  <xs:element name="StartDate" type="xs:string" minOccurs="0" />
  <xs:element name="StartTime" type="xs:string" minOccurs="0" />
  <xs:element name="Frequency" type="xs:string" minOccurs="0" />
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  <xs:element name="DatasetVersion" type="xs:string" />
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:choice>
  </xs:complexType>
- <xs:key name="IntegrationDatasetsKey1" msdata:PrimaryKey="true">
  <xs:selector xpath=".//mstns:IntegrationDataset" />
  <xs:field xpath="mstns:IntegrationDatasetId" />
  </xs:key>
  </xs:element>
  </xs:schema>

After importing to excel each of the fields of the fields is created on a  new row.  I want it to be in the same row.
<xs:element name="DOORSDatabaseId" type="xs:string" minOccurs="0" />
  <xs:element name="DOORSDatabaseName" type="xs:string" minOccurs="0" />
  <xs:element name="TDDatabaseName" type="xs:string" minOccurs="0" />
  <xs:element name="IntegrationDatasetId" type="xs:string" minOccurs="0" />
  <xs:element name="IntegrationDatasetName" type="xs:string" minOccurs="0" />
  <xs:element name="DOORSDatasetId" type="xs:string" minOccurs="0" />
  <xs:element name="TDDatasetId" type="xs:string" minOccurs="0" />
  <xs:element name="ControllingApp" type="xs:string" minOccurs="0" />
  <xs:element name="LastSynchronizedDate" type="xs:string" minOccurs="0" />
  <xs:element name="LastModifiedDate" type="xs:string" minOccurs="0" />
- <xs:element name="AttributeMapping" minOccurs="0" maxOccurs="unbounded">

Here is a sample of the data:
  <?xml version="1.0" standalone="yes" ?> 
- <IntegrationDatasets xmlns="http://www.DOORSTDIntegration.com/IntegrationDatasetConfiguration.xsd">
  <DataFormatVersion>3</DataFormatVersion> 
- <IntegrationDataset>
  <DOORSDatabaseId>44d0d78c39e54b23</DOORSDatabaseId> 
  <DOORSDatabaseName>36681@ZCARH0WC</DOORSDatabaseName> 
  <TDDatabaseName>HTTP://QC9.CA.NORTEL.COM:8080/QCBIN</TDDatabaseName> 
  <IntegrationDatasetId>IDS_633138109903985683_3</IntegrationDatasetId> 
  <IntegrationDatasetName>Dilgeet_Int_ds</IntegrationDatasetName> 
  <DOORSDatasetId>DDS_633138101141834162_0</DOORSDatasetId> 
  <TDDatasetId>TDS_633138107782232903_2</TDDatasetId> 
  <ControllingApp>DOORS</ControllingApp> 
  <LastModifiedDate>11-05-2007 22:39:11</LastModifiedDate> 
  <DatasetVersion>2.1</DatasetVersion> 
- <LastSyncStatus>
  <DateAndTime>12-05-2007 22:08:36</DateAndTime> 
  <Status>Completed</Status> 
  <Duration>00:05:08</Duration> 
  </LastSyncStatus>
  <LastSynchronizedDate>12-05-2007 22:08:36</LastSynchronizedDate> 
- <AttributeMapping>
  <DOORSAttrib>Object Identifier</DOORSAttrib> 
  <TDAttrib>Object Identifier</TDAttrib> 
  <Source>DOORS</Source> 
  </AttributeMapping>
- <AttributeMapping>
  <DOORSAttrib>Nortel-Compliance</DOORSAttrib> 
  <TDAttrib>compliance</TDAttrib> 
  <Source>DOORS</Source> 
  </AttributeMapping>
- <AttributeMapping>
  <DOORSAttrib>Object Text</DOORSAttrib> 
  <TDAttrib>Description</TDAttrib> 
  <Source>DOORS</Source> 
  </AttributeMapping>
- <SyncSchedule>
  <StartDate>12-05-2007</StartDate> 
  <StartTime>22:03:00</StartTime> 
  <Frequency>Once Only</Frequency> 
  </SyncSchedule>
  </IntegrationDataset>

Open in new window

result-from-import.xlsx
0
cfehr-2
Asked:
cfehr-2
  • 4
  • 3
1 Solution
 
cfehr-2Author Commented:
Is there another way I can do this?
Basically what I have is two xmls that have a unique key between the two of them.
I want to merge those two xmls on that key.
I would prefer to put the result into a spreadsheet.
0
 
abelCommented:
If you want to fine-tune the import, then this article may be for you. It explains how to programmatically import XML data in excel and transform it on the fly using XSLT to better suit the target: http://support.microsoft.com/kb/307021
0
 
cfehr-2Author Commented:
N/A
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
abelCommented:
> No solution for 2007
I don't understand. What do you mean by that? Have you tried the link I showed you?

It is the custom at EE to not delete questions that have a discussion thread. If there's no solution, then that's the solution: no solution. But you didn't follow-up with additional comments for what you were after or why the info did not apply, so I have to object to the delete request.

See how to close questions for more info or ask here if anything is unclear.
0
 
cfehr-2Author Commented:
The solution proposed was for 2002/2003 and not excel 2007.
I no longer want to pursue this as I have started using swiftxml.
0
 
abelCommented:
aha, I see. The solution worked for any office version, from 2002. But if you want to drop this, I consider it against EE policy: after all, even when you decide to drop it and take a different path, the question on itself is still valid and warrants an answer. Maybe not for you, but for the EE Archive this is important.
0
 
abelCommented:
Please use #24174836 as answer. I was the only one to comment on this question and I haven't been given sufficient information why that comment would not be a viable answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now