Excelfilter: XML Mapping gone wrong
December 2, 2010 10:51 AM Subscribe
Excel 2007 Filter: I need to convert a text file to XML. I have an existing XML file that has the correct schema, but no functional xsd file. How do I get the schema from the old file to map to the data in the new text file?
Okay, I've gotten this whole shebang to work in the past, but my note-taking skills were apparently on vacation that day, and my notes suck. Googling my error message is surprisingly ineffective, so I'm turning to you, AskMe.
I have to generate an XML file each month from new data. Separate XML files for each month. The new data is a tab delimited text file. I have the past couple months' XML files. In theory, I can load the text file into a worksheet, then import an XML map from last month's XML file and use that to map to my new data, but I get the following error when I try to do that: "The operation cannot be completed because the result would overlap an external data range or PivotTable".
I've googled the complete error message and only get a few results that are other people asking the same question, with no answers. I've gone through all the docs I can find about importing XML into Excel. I *know* this is doable, because I did it last month. I just don't know how I did it because my notes are pathetic, as I mentioned before. I usually take great notes, but my boss and I were trying to figure out how to make it work then, so I guess I didn't write down as much as I should have.
Anyway, anyone have any idea either a) why I'm getting the error message and how to avoid getting it, or b) some other way to accomplish using the schema from last month's XML file to create a schema for this month's data?
Okay, I've gotten this whole shebang to work in the past, but my note-taking skills were apparently on vacation that day, and my notes suck. Googling my error message is surprisingly ineffective, so I'm turning to you, AskMe.
I have to generate an XML file each month from new data. Separate XML files for each month. The new data is a tab delimited text file. I have the past couple months' XML files. In theory, I can load the text file into a worksheet, then import an XML map from last month's XML file and use that to map to my new data, but I get the following error when I try to do that: "The operation cannot be completed because the result would overlap an external data range or PivotTable".
I've googled the complete error message and only get a few results that are other people asking the same question, with no answers. I've gone through all the docs I can find about importing XML into Excel. I *know* this is doable, because I did it last month. I just don't know how I did it because my notes are pathetic, as I mentioned before. I usually take great notes, but my boss and I were trying to figure out how to make it work then, so I guess I didn't write down as much as I should have.
Anyway, anyone have any idea either a) why I'm getting the error message and how to avoid getting it, or b) some other way to accomplish using the schema from last month's XML file to create a schema for this month's data?
« Older Ordering dates when writing about geologic time? | Can my company withhold paying me because I'm late... Newer »
This thread is closed to new comments.
The error appears to be caused by attempting to map an XML element onto data from an external data source. Makes sense, right? So the solution is to highlight the entire worksheet, then copy and paste-special->values into a completely new workbook. Then just import your XML map as usual and map the elements to your new columns. The paste-special part is the important part. There's one answer from googling the error message that suggests copying the data into a fresh workbook, but without doing the paste-special->values, you're just copying the data connection along with the data, which doesn't fix the problem.
posted by ashirys at 12:23 PM on December 3, 2010