• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

importing XML into excel 2000 in a specific order across the rows

I want to import an exl file into excel but I want it to be in a specific order across the columns.
Col 1 - Cluster Col 2 School Col 3 BSC  Col 4 Type of case Col 5 case status co l6 surname col 7 first name
Col 8 DOB  Col9 Age  Col 10 year  col 11 Gender and so on - 28 column headings in all.
How do I do this?  
0
Dier02
Asked:
Dier02
  • 15
  • 4
2 Solutions
 
Dier02Author Commented:
Sorry - too many rums...an xml file into Excel 2000
0
 
R7AFCommented:
You can convert the XML to CSV. This can be done using an XSLT stylesheet. Tell a bit more about the process you have in mind. Is this something you'll do manually? Is it to be automated? What languages do you use? Can you give a sample of the XML?
0
 
Dier02Author Commented:
xml version="1.0" encoding="UTF-8" ?>
- <form1>
 <Case_outcome>Good</Case_outcome>
  <School>Blinky Bill SS</School>
  <Date_Opened>2007-10-09</Date_Opened>
  <Surname>Smith</Surname>
  <Firstname>Dave</Firstname>
  <EQID>1234567f</EQID>
  <DOB>12/04/96</DOB>
  <Age>12</Age>
  <ODSupp>none</ODSupp>
  <Gender>male</Gender>
  <Ethnicity>Caucasian</Ethnicity>
  <NESB>no</NESB>
  <MedDiag>ASD</MedDiag>
  <Cluster>Wangan</Cluster>
  <Refsource>School</Refsource>
  <Year>3</Year>
  <MentalHealth>OCD</MentalHealth>
  <SchoolsAttended>Blinky Bill SS</SchoolsAttended>
  <Date_closed>2007-10-11</Date_closed>
  <InteragencyInvolve>None</InteragencyInvolve>
  <DOCS>No</DOCS>
  <SDAdetails>obsessive</SDAdetails>
  <SDAdays>12</SDAdays>
  <Case_description>Dave is a funny kid</Case_description>
  <LearningSupport>SEU</LearningSupport>
  <GO>yes</GO>
  <Service>ABC</Service>
  <Fle_stored>MDSS</Fle_stored>
  </form1>
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Dier02Author Commented:
but thats not the order I want
0
 
Dier02Author Commented:
CLUSTER      
SCHOOL      
BSC      
TYPE OF CASE      
CASE      
SURNAME      
FIRST NAME       
ID NO.      
D.O.B.      
AGE      
YEAR      
GENDER      
ETHNICITY      
NESB      
SWD / MEDICAL DIAGNOSIS      
MENTAL HEALTH DIAGNOSIS      
GO      
LEARNING SUPPORT      
SCHOOLS ATTENDED      
"SDA DAYS"      
SDA DETAILS      
DOCS      
"INTERAGENCY INVOLVEMENT"      
OTHER DISTRICT SUPPORT      
DATE OPENED      
DATE CLOSED      
CASE DESCRIPTION      
CASE OUTCOMES      
FILE STORED            
0
 
Dier02Author Commented:
the above is
0
 
Dier02Author Commented:
What I am doing is creating a form in Adobe Acrobat 7's Designer and then having the forms data submitted to me via e-mail as XML.  I want to import the XML into an Excel spreadsheet which is a linked table to an Access database table.
Ideally I want to have the form responses from different people go into an outlook folder.  I want a script to check the folder and open the e-mails XML attachments and import them into the excel spreadsheet in the correct format.  But at this point I am happy to just have them able to be imported into Excel in the correct format.
0
 
Dier02Author Commented:
process initially to be done manually and english is my major language
0
 
R7AFCommented:
The XML looks like it is a single record. Is it possible that you're going to insert multiple records at the same time? In that case the XML should be more like this:

<forms>
   <form>
     ..... all data for row 1.....
   </form>
   <form>
     ..... all data for row 2.....
   </form>
   <form>
     ..... all data for row n.....
   </form>
</forms>

Are you familiar with XSLT? It's not difficult to create an XSLT stylesheet that transforms the XML into CSV. This is one option, but is has its limits and challenges, mostly getting the XSLT engine working on your server. See the link for an example how this works.

http://www.topxml.com/dotnet/articles/xslt/default.asp

Another option is to use asp/dotnet directly to insert the xml-data in a spreadsheet, but that's not my expertise.
0
 
Dier02Author Commented:
The server is a work server and I dont have access to it so I need a file that transforms the XLM into the format I want for my Excel workbook.  I dont want to have to manually change the XML file, just import the transformed XML into Excel
0
 
Dier02Author Commented:
I want to data to go in a row accross the spreadsheet with the headings above starting at A1,B1,C1...n1
0
 
Dier02Author Commented:
Could I create an XML map in Excel 2007 and export that then import that into the excel 2000 worksheet and then open the xml file I want to import (the one with the data)?
0
 
R7AFCommented:
So if I understand correctly, you do the transformation and import using your local machine. I suppose you have IIS running and you know how to handle ASP pages and stuff.

You can try the following code:
http://www.xmlfiles.com/articles/sample_chapters/sams_xmlforaspnet/default.asp
Look at listing 7.3. This is the ASP code you need. You have to edit path- and filenames. Put all three files in the same folder. If this doesn't work, you probably don't have the right .net version installed. I'm not that familiar with .net, but I have alternative code if you need it.

This code requires two files as input: the xml and the xslt. The xml you have. The XSLT is as follows. Notice that I use "form" instead of "form1", and in case you want to import multiple "form" records, you can enclose them in a <forms> tag. If you really want to use "form1" just change the code. But if this means that the next record has "form2", then that means you have to edit the xslt every time. You don't want that.

If you apply this stylesheet, you'll notice that it processes the rows in the order of the XML. This is not what you want, but that's step 2. First try this, and if you have this working, we'll look at the order.

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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="text"/>
      
      <xsl:template match="forms">
            <xsl:apply-templates select="form"/>
      </xsl:template>
      
      <xsl:template match="form">
            <xsl:for-each select="*">
                  <xsl:value-of select="."/>
                  <xsl:if test="position() != last()">
                        <xsl:value-of select="','"/>
                  </xsl:if>
            </xsl:for-each>
            <xsl:text>&#10;</xsl:text>
      </xsl:template>
      
</xsl:stylesheet>
0
 
Dier02Author Commented:
I am working in a work environment where I cannot install anything to the server or to the local computer.  As a consequence I can't install .net
0
 
Dier02Author Commented:
How do you use the xlst file in relation to the xml file.  For example I open Excel and there is a data menu with import, export, xml source and xml map properties.  What do I do next to utilise the xslt? Or do I simply open the XML file and the XSLT parses it automatically?
0
 
R7AFCommented:
If you have Excel 2003 or 2007 you can open an XML file. In the XML file you can define a link to the XSLT like this:

<?xml-stylesheet type="text/xsl" href="form.xsl"?>

When you open the xml file, Excel asks whether to apply the stylesheet or not. This won't work however, because this xsl creates CSV, which can be imported, but not transformed inside Excel. So you'll need another stylesheet for that purpose, one that's a bit more complicated.
0
 
Dier02Author Commented:
I think I found an easier way to do it simply by creating a form in Adobe Designer which is XML based then using the source in Excel to import the XML that I e-mailed from the designer form and this gives me the map I want.  I then can choose the complete form and add the parts of it that I need.  I have several people sending me parts of the one entry.  I use the map to include their part into the one row which contains all the other imported xml entries.  Clear as mud yes?
Simply - people email exl to me.  I compile it into one row in Excel using my master map which I created in designer and e-mailed to myself to get the XML layout.  I imported the layout then added-in the data as others sent it to me.  
0
 
Dier02Author Commented:
but these still a problem.  It only allows me to replace the data and not append new data.  What do I need to do to modify this in properties as the append button is greyed out.
0
 
Dier02Author Commented:
worked out how to do it.  Just import the map using the XML soource pane then drag the folder icon over to the first cell of the row, then import the XML and then delete the map.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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