0

There are some data stored in sharepoint List which uses XML as the source. I used javascript to make it look good on sharepoint BUT when exporting to Excel - obviously - it does not copy the format and is left with the xml source instead.

Example (this is just one section of a long list): 

<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version /><Items><Item><Method type="System.String">Cash</Method><Amount type="System.Double">100</Amount><Rate type="System.Double">0</Rate><planRates type="System.String">0</planRates><planRatesValue type="System.Decimal">100.00</planRatesValue><calculateTotalAmount type="System.Decimal">100.00</calculateTotalAmount><Returned type="System.Double">84.75</Returned><ReturnedRate type="System.Decimal">0.00</ReturnedRate><TotalRemaining type="System.Decimal">15.25</TotalRemaining><varCurrency type="System.String">GBP - Sterling</varCurrency><SelectCurrency type="System.String">1</SelectCurrency></Item><Item><Method type="System.String">Cash</Method><Amount type="System.Double">500</Amount><Rate type="System.Double">0.883</Rate><planRates type="System.String">0.9</planRates><planRatesValue type="System.Decimal">450.00</planRatesValue><calculateTotalAmount type="System.Decimal">441.50</calculateTotalAmount><Returned type="System.Double">423.45</Returned><ReturnedRate type="System.Decimal">373.91</ReturnedRate><TotalRemaining type="System.Decimal">67.59</TotalRemaining><varCurrency type="System.String">EUR - Euro</varCurrency><SelectCurrency type="System.String">2</SelectCurrency></Item></Items></RepeaterData>

Can the above be formatted back to cells in Excel ?
if so, is there a comfortable method in doing so?

Update: screenshot

Notice that the [Item] [Amount] etc appear twice cos there are 2 entries.

enter image description here

Improve this question

2 Answers 2

Reset to default
1

Edit 1: With the image provided, it's not possible to make it as you want. Please, read my last comment.

Well, you could create calculated columns in your list. These calculated columns would get data from each column, so, using this formula:

=MID([Method], FIND(">", [Method], 1)-1, FIND("<", [Method], 1)-1)

In this way, you could get the text between tags, because MID function combined with FIND could get the text between tags. In that example you would get the text from <Method> tag.

So, once you have the correct text en each calculated column, you can create a view in that list with these calculated columns, and when you export you will see the correct columns in the Excel.

Resources:

Improve this answer
8
  • the [Method works but not everything else ... also I noticed this won't show more than 1 entry. Meaning the [Amount] can appear twice due to showing different currency & amount etc.
    – Bucki
    Commented Jan 24, 2018 at 8:05
  • If it works for [Method], then it should work for other columns. Are you sure that you have one column for each tag (Method, Amount, Rate...) and one entry for each <Item>? Please explain more.
    – Sergio
    Commented Jan 24, 2018 at 16:17
  • i have attached a screenshot in my post. In that case there are 2 entries with different amounts, rates etc. As you can see, the METHOD column has shown only CASH although in that instance the Method appears twice for instance ... not sure if I am clear
    – Bucki
    Commented Jan 24, 2018 at 16:33
  • Thank you for screenshot. Of course what I said it's not going to work, because formulas in calculated columns are not recursive, I mean, it's not going to navigate through all that XML. You may try to insert each XML tag in a different column and one entry per Item in the list, then what I said it will work.
    – Sergio
    Commented Jan 24, 2018 at 16:43
  • @Bucki, please mark the answer as correct answer if it was useful. Thanks
    – Sergio
    Commented Jan 24, 2018 at 17:24
0

This post is a bit vague, but assuming all your data has the same tags and you successfully managed to export the entire xml record as a single text cell in excel, you can extract the data into a series of calculated columns using the Left, Mid, Right & Len functions within excel.

https://spreadsheeto.com/text-functions/

Note that this will not conserve your cell type formatting (string/ float/ double e.t.c) but if you're wily you can set that value once and let calculated columns take care of the rest.

Alternatively you could try copy directly from the webpage after it had been reformatted using js and paste it directly into excel. If the formatting creates issues you could try paste into a text editor first.

A more manual method would be to export the spreadsheet as a csv and use an xml tag stripping algorithm or simply use find & replace in a text editor like notepad++ to remove the tags yourself indivdually from each record.

Improve this answer
8
  • sorry about my question being vague but was not sure how to explain in other words. Basically, for each entry in SharePoint List there is an individual XML code. when exporting the list into excel it takes all cells but then end up with the XML in the particular cells.
    – Bucki
    Commented Jan 23, 2018 at 13:59
  • copy pasting from nice looking format, does not work :/ as this is what I tried first thing lol. The exporting works fine from SP but when it uses the XML it does make it difficult to read to excel :(
    – Bucki
    Commented Jan 23, 2018 at 14:09
  • If you're trying to import raw xml use an xml map or try importing xml according to Microsoft guidelines support.office.com/en-us/article/…
    – J. Doe
    Commented Jan 23, 2018 at 14:57
  • Doe, there is an XML source for each entry which starts with "<?xml version="1.0" encoding="UTF-8" standalone="yes"?>" just imagine the code in my question is repeated many times ... i tried to import it that way via MAPPing in Excel but it does not work.
    – Bucki
    Commented Jan 23, 2018 at 15:07
  • never mind ... it is way more complicated as I initially thought and besides that, I am not a coder nor a developer. I hoped for some kind of build in option within excel or something ..... thanks for your suggestions though.
    – Bucki
    Commented Jan 23, 2018 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.