Importing XML file with invalid character

**agentblue** · 04-04-2013, 04:19 PM

Hello,
I have a very large XML files produced daily and they sometimes have 10 or so instances of an invalid character, it's like a musical symbol, i can't find the character code for it.
I am writing a macro to import these xml files, delete most of the unwated columns, filter by certain criteria, perform calculations on the filtered rows and then average the results of these calculations in order to obtain statistics from the massive datasets.
I have had (Limited) success with most of the steps but I have still not come up with a way of sanitizing the XML sheets before importing them into excel. For the xml files that do not contain the invalid character the file imports without a hitch. As I mentioned I can't find the character code for the offending symbol so I can't do a find and replace on it, besides this would make the operation a 2 step process rather than an elegant one button macro

Can anyone lend any suggestion on how to get around the problem of being unable to import the XML files that do contain the invalid character? My programming experience is very limited and I make most of my macros by recording basic examples and seeing how they work or tweaking other peoples brilliant solutions to suit my own needs.
Thanks in advance for all your help.

**Leith Ross** · 04-04-2013, 11:09 PM

Hello agentblue,

Welcome to the Forum!

Post one of the offending XML files as a text file. This will provide a file to be used in developing and testing solutions.

To Post Your Workbook

At the bottom right of the Reply window, Click the button Go Advanced
At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
Click the Select Files button at the bottom middle of the dialog.
In the new window Find the file you want to upload, select it, and Click Open.
You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Picture

**agentblue** · 04-05-2013, 08:33 AM

Hi,
Thanks for your reply.
I can't upload an XML file so I copied a sample as a .txt file.
The original XML is WAY larger than this sample that I have included. It does contain the invalid characters but for data protection issues I can't include the full file. If there are any formatting issues other than the invalid characters, they are of my own making when copying the sample. The original xml will import just fine if I manually delete the invalid characters.
P.S. I'm using the following code to import the XML file.

Sub ImportXml()
'
' ImportXml Macro
'
'prompt user to open file
Myfile = Application.GetOpenFilename("Text Files,*.xml") _


' Import xml
    ActiveWorkbook.XmlImport URL:=Myfile, _
    ImportMap:= _
    Nothing, Overwrite:=True, Destination:=Range("$A$1")
    
End Sub

attachment

Attachment 225871