XML

XML

Didn't find your answer?

Exact software will only import xml files....as we are currently setting up the program, we want to move lots of standing data into the new program, without manually retyping it.

I have created the appropriate schema in Excel, and entered all of the transactions onto the sheet, but when I save as xml, Microsoft goes and changes all of the tags and enters lots of its own code!, which then means that the program rejects the file.

Any ideas? A quick response would be appreicated! I have downloaded XML spy, and tried to cut and paste, but as some of the field are subfields of others, this cut/paste doesnt' seem to work too well.

Please help o wonderful Aweb members!

Mike Jones

Replies (8)

Please login or register to join the discussion.

avatar
By AnonymousUser
07th Nov 2003 13:14

perl script to extract
I could probably write a perl script to extract the required data and output it in whatever format... I suspect that isn't what you wanted to hear though :-)

http://www.OWAL.co.uk/

Thanks (0)
avatar
By AnonymousUser
21st Jan 2004 12:53

Correct way to handle XML file modifications like this...
The way to handle XML files generally is through a language called 'XSL'. This is better than cutting and pasting as above because the XSL solution understands XML file formats, and can automate the cut+paste for you.

However it's not for everyone (can be a bit hardcore :-)

I've done a good bit of development in xsl and changing one xml file into another is often trivial.

So, if the original poster would like to email me a complete but minimal example of his original file and desired output, I can work out a transformation from one format to another. I'll then put the code which does this change online one one of my sites.

The plan here will be : I create a web page which you can visit to upload your Excel XML file, and you get a file download straight back with the changes made. Changing the XML file from one format to another will take under a 1/10th of a second ;-)


If anybody's interested in this, or the original poster is still about, mail me a couple of examples along with the desired output, so I can check the code works properly.

Cheers - Neil Smith.


PS - John - to use Angle Brackets on accountingweb, use &lt; and &gt; to make the forum display < and >

Thanks (0)
avatar
By asdesign
09th Nov 2003 13:56

Further to previous
I would be interested to find out if the claims of friends who work for Sun are correct in that they say that star office and its freebie pal openoffice will provide the file that you require, with no MS fluff added in.

Yours

Viv Burrows

[email protected]
please use the following as Intel are using threats to make me change.
[email protected]

Thanks (0)
avatar
By 3569787
03rd May 2016 19:46

2 suggestions

Thanks (0)
avatar
By AnonymousUser
06th Nov 2003 12:33

Might be easy
How about copying to Word then converting the resulting table to text, then copying the text to Notepad and saving as "file.xml"?

Thanks (0)
avatar
By suzina
06th Nov 2003 16:32

Solution? untried!
The new version of Office (2003) Professional says on the back of the box that it now contains facilities / a package to exchange data with other systems using XML technology... I haven't tried it & you may want to do some homework on the net before buying but it could save you a lot of time!

Good Luck

Thanks (0)
John Stokdyk, AccountingWEB head of insight
By John Stokdyk
06th Nov 2003 18:57

I know how you feel
Mike - I put this one in the IT Zone newswire because I know how you feel. The same thing happens to me when I try and paste HTML versions of charts or even Word files into AccountingWEB.

I'm on a little less solid ground with Excel and XML, but I think there is a way forward with Bob Brook's option a). But it will take some finniky work and probably a lot of trial and error.

First, most the code you get at the top of an Excel XML file is extraneous Microsoft rubbish. Delete everything below the XML header - the top bit which says ?xml version="1.0"?: you can probably cut it to "xml" and it'll work - down to the tag "Worksheet ss:Name="Sheet1". If this doesn't work there may be some useful tags buried in there - see if the Exact documentation (or helpdesk) provide a sample file format for XML imports.

Next, using Find/Replace in Notepad, (or paste it into Word to edit, but then paste the resulting text into Notepad and save as .txt - don't ask, it's just part of the magic) pick out strings such as:
"Column ss:Index="5" ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="51"/"
and replace them with "" (ie nothing, so they disappear.

Eradicate as much as you can this way until you get down to the values that say things like:
"Cell ss:Formula="=11650-(R[2]C+R[3]C+R[4]C+R[5]C+R[1]C)"-Data
ss:Type="Number">3107/Data>/Cell>.

From there, you're on your own! It'll take a lot of time - and probably a lot of manual cutting if any of the values change in your find/replace strings. Depending on the file size, you might well end up taking as much time as typing it, but working out how to Edit the XML will be dead useful if you import data in the future, or need it for any other purpose.

If you do a lot of imports from the same old ledgers (eg to pull in archived data), you could even do the editing in Word and record it as a Macro to automate the process next time. It's very satisfying watching the file jump around until it's ready for use!

Hope you can make sense of this - and please note, I cannot actually put the "less than" XML tag characters in this message, as the AccountingWEB system gets confused trying to interpret them. Isn't modern technology great!

Good luck
John Stokdyk
Editor
AccountingWEB.co.uk

Thanks (0)
avatar
By asdesign
06th Nov 2003 11:21

open office
Open office is a free replacement for the MS Office offering
The excel part is called calc and doesn't add any additional formatting when exporting to xml and it is free. It also has an export to pdf built in as well.

http://www.openoffice.org

Viv Burrows

[email protected]
For timesheets and HR
http://www.easy-hr.co.uk

Thanks (0)