0

I have a bunch of files which should be a table with one row and 30/40 columns with data, in this format:

Inline XRBL (iXRBL - XML in HTML) http://resources.companieshouse.gov.uk/toolsToHelp/pdf/inlineXBRLsample.pdf

And I need them putting into this format:

XRBL (XML) http://resources.companieshouse.gov.uk/toolsToHelp/pdf/XBRLsample.pdf

(The XML in this example link has an error in it and won't actually import into excel, but you get the general idea).

I have a few files in the XRBL XML format already and they open in Excel and the data goes into columns automatically - ideal. Opens up as a few rows with 30/40 columns with the values in them.

I am trying to get the iXRBL files into Excel in the same fashion, with no luck, it won't display.

I need a batch gui to run across all the iXRBL files (.html) to, at least, convert them into XRBL XML, which should allow me to open in Excel and have all the data go into the right columns. Ideally I would then need to combine it all into one XML file/XLS.

Other ideas I had was a windows GUI app (I'm no programmer but have a general idea) to "extract" multiple expressions from all the files in the directory and import automatically into excel; putting the filename EntityCurrentLegalName value in Column A colum and taking out all the defined expressions above and their value into the B, C, D, E, etc. columns.

Other ideas I had was, lets say there are 40 columns, was to duplicate the folder 40 times, and run a search and replace tool deleting everything in the files, except for the expression, for example:

Folder 1: "Filename (EntityCurrentLegalName) + value" (COLUMN A) Folder 2: "CalledUpShareCapital + value" (COLUMN B) Folder 3: "NetAssetsLiabilitiesIncludingPensionAssetLiability + value" (COLUMN C)

etc, etc. - I would need to use wildcard for the value as would be different in each file and search the whole expression, i.e deleting everything before and after that expression...then combining all the files together as text and just pasting each column directly into Excel, cleaning it all up - very round-about-way to do it.

(The XML in the example link has an error in it and won't actually import into excel, but you get the general idea).

Any ideas on a windows 8 GUI to achieve?

Posted Other forums

http://www.excelforum.com/excel-programming-vba-macros/1093170-ixbrl-to-xbrl-xml-inside-html-excel-cant-resolve.html#post41287811

  • I'm thinking of Microsoft's Power Query Add-In. Those samples are protected PDF files - hard to try any ideas as I cant even copy the content. Can you point us at any unprotected examples? – Mike Honey Jul 13 '15 at 23:54
  • I've worked out a workaround at the moment. I am having to do it in bulk replace using one regex at a time. Thanks. – Jade Connor Jul 14 '15 at 21:11
  • Power Query is probably an easier solution - it can loop over nodes in HTML/XML and the query design UI is about 100x easier than regex. – Mike Honey Jul 15 '15 at 3:59
  • 1
    I used power query and it imported the HTML but all the xml/tags are all over the place. Maybe I will just have to play around with power query... – Jade Connor Jul 16 '15 at 17:10
  • solved? appreciate if you can share the solution... it'll benefit others... and me! – p._phidot_ Oct 15 '18 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.