?
Solved

Importing XML into Excel 2007

Posted on 2009-04-13
10
Medium Priority
?
1,046 Views
Last Modified: 2012-05-06
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
Comment
Question by:cfehr-2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
10 Comments
 

Author Comment

by:cfehr-2
ID: 24138354
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
 
LVL 39

Accepted Solution

by:
abel earned 1500 total points
ID: 24174836
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
 

Author Comment

by:cfehr-2
ID: 24306097
N/A
0
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
LVL 39

Expert Comment

by:abel
ID: 24306167
> 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
 

Author Comment

by:cfehr-2
ID: 24306808
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
 
LVL 39

Expert Comment

by:abel
ID: 24311419
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
 
LVL 39

Expert Comment

by:abel
ID: 24347220
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

Featured Post

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question