I'm trying to export data into xml (to open in excel), but it runs into problems with a couple (at least) film titles; it's always possible of course that it's a bug in excel .

Opening either as "an XML table" or "use the XML source task pane" throws up errors; but only gives the type of error and a location; if you "open as a read only workbook", as well as the description and location of the error, it gives you the actual line that's the problem (so you can see which film it is).

So, one film that creates an error is "The Assemblage of the Crystal Sphere: A D&D Story", with the error

File cannot be opened because:
A semi colon character was expected
Line 165, Position 59
<Title>The Assemblage of the Crystal Sphere: A D&D Story</Title>

and another one, selecting a different set of films is "14 Amazons <Shi si nu ying hao>", which gives

File cannot be opened because:
Missing equals sign between attribute and attribute value.
Line 42005, Position 35
<Title>14 Amazons <Shi si nu ying hao></Title>

These appear to be more or less the only films that create errors in my collection; at least, filtering the list by year 2009-2015, 1973-2007, and 1880-1971 all export fine, when exporting title, popularity, rating, and genres anyway. There might be other films in 2008 and 1972 with problems of course.

I'm guessing it's something to do with the special characters in the title, but I don't really know a lot about xml.

Experimentally I tried exporting plot as well; and now another one at least throws up an error, "The Hired Hand" - this one seems to be about length, perhaps, as the plot text is truncated?

File cannot be opened because:
Whitespace is not allowed at this location.
Line 443, Position 825
<Plot>Peter Fonda's superb yet little-seen directorial debut subverts the machismo of the Western genre with a quiet, understated tale of relationships and loyalty in the Wild West. Fonda stars as Harry Collings, a drifter who has been roaming from town to town with his close friend and fellow wanderer Arch Harris (Warren Oates). Collings is haunted by memories of the wife (Verna Bloom) and daughter he abandoned and decides to make amends and return to them, offering penance by working on the family farmstead as a hired hand. But his newfound domesticity is threatened when Arch is ta

That's only about 60% of the plot, there's 40% more that doesn't show, in the error text at least.

"Plot" can also throw up one of the other errors as well, Altar (2014) gives :

File cannot be opened because:
A semi colon character was expected
Line 218, Position 141.
<plot>The Hamilton family move into a large country house on the Yorkshire Moors to supervise its restoration from a dilapidated B&B to the original Victorian grandeur. When Meg Hamilton, wife, mother and renovation expert first loses her London team after an accident, then a local Yorkshire team too superstitious to continue, she's forced to carry on alone. The discovery of a secret attic room, a Rosicrucian mosaic, a bricked up root cellar and many other unexplainable events gradually convince Meg, her husband Alec and children Penny and Harper, that they're not only restoring the house,

which is truncated as well, but here I guess the & in B&B is the problem? and if that was fixed it might give the whitespace error instead...

Not tremendously important probably, I don't know how much this feature is used (or like I said if it's not an excel problem instead), but thought I would bring it up.

Thanks,

Thank you for reporting this problem!

Apparently, XML needs 5 characters to be escaped (&, <, >, " and ').

http://stackoverflow.com/questions/1091945/what-characters-do-i-need-to-escape-in-xml-documents

Later tonight, or maybe tomorrow, I'll send you a version and you'll tell me if everything is fixed.

will do (tonight if it's not too late, otherwise tomorrow evening).

Please replace your current Coollector.exe with this one:

http://www.coollector.com/Coollector.exe

that seems to have fixed the initial problem - the whole list will import now, including plot - and there's no truncation.

However ( ), except for the ampersand, that list of characters still don't seem to display properly (although they import ok) :

her brother had made "the first feminist western".

has become

her brother had made &quot;the first feminist Western.&quot;


It's a Wonderful Life

is now

It&apos;s a Wonderful Life


programmer and "information activist" Aaron Swartz

becomes

programmer and &quot;information activist&quot; Aaron Swartz


14 Amazons <Shi si nu ying hao>

is

14 Amazons &lt;Shi si nu ying hao&gt;


B&B however shows perfectly as B&B...

Either I'm missing something, either Excel is dumb

Let's try a different approach (with CDATA)...

Please replace your current Coollector.exe with this one:

http://www.coollector.com/Coollector.exe

That goes back to not working at all, and the error message is (seemingly) a bit unhelpful

File cannot be opened because
A name was started with an invalid character.
Line 734, Position 17
<Title><</Title>.

(I don't think it's just excel though - opening the xml file from the previous version in firefox had the same issue, with all the escape characters showing as &lt; or &apos; and so on.)

looking at the raw xml (again from the previous version) in a text editor (not that it means a huge amount to me), e.g. the

14 Amazons <Shi si nu ying hao>

is translated to

<Title>14 Amazons &amp;lt;Shi si nu ying hao&amp;gt;</Title>

so it's almost like the the escape sequence should be &lt; but somehow when it's being translated, the & at the beginning of the escape sequence is being treated again as though it's part of the text itself, or being processed twice, so you get &amp; instead of just & before the lt;

but this doesn't happen when the symbol itself is an &, so

dilapidated B&B

is going to

dilapidated B&amp;B

and displaying correctly

just looking on that link you found, it says :

@Pacerier, I beg you not to write your own XML/HTML escaping code. Use a library function or you're bound to miss a special case. – Jason Mar 16 '12 at 9:23

Also for line breaks you need to use &#xA; &#xD; and &#x9; for tab, if you need these characters in an attribute. – radistao Nov 26 '12 at 22:33

If you're going to do a Find/Replace on these, just remember to do the &amp; replacement before the others. – Doug Jun 15 '13 at 21:29


do you think that last one might be the problem?, that would seem likely to produce this effect, if that's what you're doing...


in fact now I look that's exactly what the next comment says


@Doug I was just about to mention the exact same thing - or else all other replaced characters will be corrupted, and things like &quot; will be changed to &amp;quot; – Jerry Dodge Aug 5 '13 at 22:23

Don't go any further! A vicious bug has taken me by surprise and ruined what I was trying to do

Let's give it another try:

http://www.coollector.com/Coollector.exe

Same error...

(I still would like to try your first attempt, just with the &amp; being done first rather than last, or vice versa )

It's impossible. A different error, maybe, but the exact same error, it's impossible.

Because the file name hasn't changed, maybe your browser had cached the file.

I've renamed it into Coollector2.exe:

http://www.coollector.com/Coollector2.exe

Please try again. The XML file should contain things like

<Title><![CDATA[It's a Wonderful Life]]></Title>

<![CDATA[]]> is another way to escape special characters.

must have been a cache somehow (the previous file was 2493kb, so the same size, but this latest one is 2534kb). Although i have rebooted since and it's still the same size, so perhaps it was the same one

anyway, trying the new one now...

ok, that seems to have worked .

just playing around now, but all the characters seem to be working...

The only thing (possibly) missing is an "XML Schema", but I don't know if that really matters, Excel seems quite capable of making that up itself from the data (and in any case thinks it's unimportant enough you can check a box to 'don't show this message again' )

Thanks!

I had never heard of XML Schema. I've made a quick search, and could not even find an easy description.

I think I'll pass on this one...

I don't think it's terribly relevant here

thanks for your help...