Keywords
Do you have have "siloed" data and VBA formulae and macros stuck in old spreadsheet files from Excel or LibreOffice Calc? Webel IT Australia has the ultimate data and equation migration solution using custom Wolfram Mathematica libraries that have been employed on substantial engineering spreadsheets for previous clients.
Spreadsheet data is not structured data. Just having, for example, labels on rows that indicate where data for a "component" starts - separated from data for other components by blank lines - is NOT structured data.
Whilst there are many products for reading spreadsheet data, they can only go so far. They mostly only enable you to read the data, but it's still unstructured, just rows and columns without true relationships to components, devices, clearly defined data groups, or a data schema.
The Webel system leverages the amazing Wolfram Mathematica - the most powerful mathematics and data processing tool in history - to take it much further. And once the data is encapsulated in Mathematica as structured data using the Webel system, it can be exported to a truly massive range of databases and cloud repositories (including the convenient Wolfram Cloud) other data formats and serialisations including XML Schema and JSON.
But it doesn't just stop there. The Webel spreadsheet migration service optionally includes reverse engineering of underlying spreadsheet VBA formulae and macros to symbolic algebra equations (actual Mathematics) in the Wolfram Language which can then be combined with incredibly powerful numerical analysis, plotting, visualisation, 3D modelling, and all of the amazing advanced computing goodies Mathematica offers.
Additionally, solutions can be captured as Graphical User Interfaces (GUIs) for deployment to full Mathematica or to the cost-effective Wolfram Player Pro for end users.
How does it work? The main steps
- Spreadsheet data is read from XLSM spreadsheet file bundles, which is a very common file format for Excel and LibreOffice Calc. Under the hood the XLSM bundle uses a number of files in the Open XML format to represent all sheets in a spreadsheet and all other aspects of the spreadsheet.
- Unlike some spreadsheet reading systems, the Webel system captures not just the data, but all of the information in the spreadsheet, including also styles and formulae. At this stage the data is still just unstructured data accompanied by some style information and metadata. The Webel XL Layer data format can by displayed in Mathematica in spreadsheet similar format, optionally including representation of the imported cell styles, and even showing all of the original spreadsheet cell information as tooltips.
- The imported spreadsheet information is then translated to a special intermediate unstructured data US Layer still with access to all of the additional styling and underlying spreadsheet metadata and VBA formula used, but not yet with any structured interpretation of the data. This intermediate format has support for flexible identification of groups of data that are candidates for identification of component data, device data, and data groups, and can display the data in rich ways far beyond a mere spreadsheet, including interactive tree views of identified data groups or components.
- The application Client Data (CD) extraction layer. This typically involves development of a custom library specific to the format of a client's particular data and domain. This is where component data, device data, or other data groups become available as structured data using domain specific terminology for each identified grouping. This layer leverages the powers of the Wolfram Language for parsing and identifying patterns in unstructured data!
- The Webel extracted data EX Layer. The data is now decoupled from the spreadsheet and is encapsulated in a very concise and efficient form, but still keeps a reference to its source (each data point knows which spreadsheet cell it came from). This data does not have (and does not need) the spreadsheet formulae, but can still be traced back to the formula of its source cell. This an efficient format for direct further use in Mathematica. EX Layer format data can also use units-aware Quantity algebra and numerics! And the Webel system can detect units indicator text labels in the original spreadsheet.
- The extracted data can then be persisted to the Wolfram Cloud or to other cloud databases, or exported to formats using XML Schema or JSON.
- All of the above steps are easily automated into a single workflow.
The Webel system uses special caching mechanisms to enable efficient processing of very large spreadsheets and datasets, and has been tested on complex engineering spreadsheet tasks.
(The Webel system is also able to write data and formulae back to spreadsheets, but this facility is not offered as part of the service, which service is intended foremost to release you from old spreadsheets for good.)Q: What about the spreadsheet formulae and VBA code?
A: The Webel system has some capacity to identify spreadsheet formula, but no available spreadsheet reading systems are able to automatically reverse engineer all VBA formulae reliably into other code completely robustly, and it is NOT a good idea to do it! The Wolfram Language (which is a symbolic algebra language as well as a numerical and coding language) is massively more powerful and elegant than VBA. To translate VBA to any more modern and powerful coding language directly is to miss most of the advantages of the new target language. To try to directly translate VBA to Mathematica would be utterly pointless.
The Webel service optionally includes migrating all underlying mathematics to Wolfram Language symbolic algebra representations, which can then be used for powerful numerics, analysis, and plotting. Webel employs Systems Modeling Language v1 (SysML®) to represent the calculations as graphical equation networks using SysML Parametrics and to represent consistent data transformation workflows using SysML Activity Diagrams, using concise notations developed by Webel specifically for this task. In fact the entire Mathematica-based Webel system for migrating spreadsheet data is modelled in Systems Modeling Language v1 (SysML®).
Q: What can I do with my data once it's migrated into structured form?
A: What do you want to do with it? It's now encapsulated in highly structured form within Wolfram Mathematica, the most powerful data analysis and computational system in history! With extraordinary mathematical and statistical analysis power, stunning plotting, graphing, and visualisation capabilities, rich 3D, and support for persistence to Wolfram Cloud or a wide range of databases, and support for exporting to a huge range of data formats and schema. Your data is now free of spreadsheets (and you will not miss them). It's the beginning of a fantastic journey into the world of modern computing.
Wolfram Mathematica is also brilliant for developing Graphical User Interfaces (GUIs) capturing your data analysis workflow, and Webel IT Australia are specialists in rapdi GUI development for deployment to Wolfram Player Pro, which can run on most operating systems at a fraction of the cost of the full Mathematica suite.
Q: Can I just have the data migrated to another system (without committing to using Mathematica or Wolfram Player Pro from then on)?
A: Yes. The spreadsheet data can be migrated for end-use in another system if you wish. Typically this involves writing the migrated data to a database in a known data schema or serialising the data according to a known data schema as XML or JSON.There is a detailed technical slide trail section showing some of how the system works: