Start Free Trial

asked on

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?

ASKER

Sorry - too many rums...an xml file into Excel 2000

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?

ASKER

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>

ASKER

but thats not the order I want

ASKER

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

ASKER

the above is

ASKER

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.

ASKER

process initially to be done manually and english is my major language

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.

ASKER

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

ASKER

I want to data to go in a row accross the spreadsheet with the headings above starting at A1,B1,C1...n1

ASKER

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)?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

ASKER

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?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

ASKER

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.

ASKER

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.