Excel importing and exporting XML UWP data

[robject]

All right. Everyone has access to OpenOffice or Excel, right? So we should all be able, in theory, to import and export XML UWP data.

Can any kind soul show me how to do this with a short example?

OK, I created a simple example and opened it up into Excel, which asked me a couple of questions then popped the data into columns with an auto-generated "XML Map". Looks pretty cool.

</font><blockquote>code:</font><hr /><pre style="font-size:x-small; font-family: monospace;"><?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<uwp xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<starport>A</starport>
<size>6</size>
<atm>7</atm>
<hyd>8</hyd>
<pop>5</pop>
<gov>6</gov>
<ll>7</ll>
<tl>9</tl>
</uwp></pre>[/QUOTE]

 

[TheEngineer]

Hi !

You would need a top level element, so that Excel recognizes list data.

</font><blockquote>code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<uwpset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<uwp>
<starport>A</starport>
<size>6</size>
<atm>7</atm>
<hyd>8</hyd>
<pop>5</pop>
<gov>6</gov>
<ll>7</ll>
<tl>9</tl>
</uwp>
<uwp>
<starport>A</starport>
<size>6</size>
<atm>7</atm>
<hyd>8</hyd>
<pop>5</pop>
<gov>6</gov>
<ll>7</ll>
<tl>9</tl>
</uwp>
</uwpset></pre>[/QUOTE]After that data could be edited, imported and exported in both ways..

OK ?

regards,

Mert

 

[Gruffty]

OK, now I'm used to working with Excel, but that lot above here just flew straight over my head and did a doo-doo on my baldy patch.

So.....

If I copy and paste that code above into Excel, will it bugger the works?

 

[Gruffty]

Nope, tried it and all that did was put all the code into a single cell.

 

[Gruffty]

Or does each line of code have to go into a separate cell?

I'm not really any good at this, am I?......

 

[TheEngineer]

Hi !

No,no.
Put the code into a textfile named e.g. data.xml.

Then use Excel to open this one (choose xml format).
Use the option "xml list" during import.

Then everything should look fine and the UWP data listed in rows...

OK ?

 

[Gruffty]

Ahhhhh, danke!

 

[Gruffty]

Nope. Still getting all the code in one cell, even after saving the code as a text file called data.xml.

I must be doing something wrong......

 

[TheEngineer]

Youre welcome

 

[Gruffty]

<bangs head on wall: bangbangbangbang!>

I think we need an "Excel for Gruffty cos He's Stoopid" book.

It's just not working. Help! A real idiot's guide to doing this would be handy, right about now..........

 

[TheEngineer]

Take care of yourself.
Travellers are so rare

Step by step:
- be sure that the data XML containes:
</font><blockquote>code:</font><hr /><pre style="font-size:x-small; font-family: monospace;"><?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<uwps>
<uwp>
<starport>A</starport>
<size>6</size>
<atm>7</atm>
<hyd>8</hyd>
<pop>5</pop>
<gov>6</gov>
<ll>7</ll>
<tl>9</tl>
</uwp>
<uwp>
<starport>A</starport>
<size>6</size>
<atm>7</atm>
<hyd>8</hyd>
<pop>5</pop>
<gov>6</gov>
<ll>7</ll>
<tl>9</tl>
</uwp>
</uwps></pre>[/QUOTE]- Open Excel (emm, which version do you use ?)
- File -> Open -> Filetyp XML-Files -> select data.xml
- dialog "Open XML" should appear with three option buttons
- choose xml list
- Data should be imported to Excel
- Gruffty is lucky again

Works ?

 

[Gruffty]

Thanks TheEngineer, that's just what I needed. However the computer at work won't do it. I'll have to wait until I get home in the morning.

 

[Gruffty]

OK. I am using Excel 2000 at home. I can't find .xml in the file types when I go to open the file.

This is what I did:

Copied the code from TheEngineer's post, 2 posts above this one.
Open Notepad and pasted the code in.
Went through the code and took out all the gaps between the different bits of code and put each bit of code on its own line (like the original).
Saved the file as "data.xml"
A little icon appeared on my desktop, showing a world with a "<" on one side and ">" on the other.
Open Excel.

 

[BillDowns]

From MS Web Help:
Note XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

Sorry, Gruffty. Looks like an upgrade is needed.

 

[BillDowns]

HMMM, follow up - Looks like Access 2000 could do it - do you have that?

 

[TheEngineer]

Well, we could create a magic macro for that..

 

[jfetters]

No, if you are going to to any serious XML conversions in Excel, you really should be using 2003.

As a side note, I think we should submit a schema for all Traveller data. Makes it great when porting data between systems.

 

[robject]

When it comes to schema, it's generally every man for himself. Have you seen the various efforts to present a schema for Traveller in the past? Trainwreck after trainwreck.

 

[jfetters]

Really?! I'm surprised, as it shouldn't be that difficult, although the devil is in the details, as always. One person's attribute is another's element, yada yada...

It just seems strange as Traveller data seems to be some of the more structured out there, at least in its basic form.

Are the previous attempts documented anywhere? I am curious to look through the wreckage.

 

[robject]

They ought to be. There was at least one in the TML in the past 8 years or so, and a couple more here, and one developing currently on Traveller5.com.

The various suggestions probably aren't all that different. I suspect that noone actually uses the XML data directly; therefore the schema almost doesn't matter (since it is hierarchical data, one can more or less freely convert from one XML schema to another, or convert whatever one's using to any XML schema. The thing is, there's noone to share the XML data with).