Wednesday Mar 27, 2013

New CAM Editor v2.4 release with enhanced Collaboration tools

The focus for this release is improved collaboration support including better dictionary generation, models, reports, spreadsheets and enhancement of the rules entry tools and rules processing. New for this release is support for Italian language localization.

The new XPath conditional rule entry wizard makes XPath rules definition significantly easier for cross-field validations and more. We have also improved the rule handling in the CAMV engine to be more consistent.

For collaboration the locations of dictionaries collections can now be located at a URL, a file system or stored in the Oracle Enterprise Repository (OER). Coupled with this are now the consistent dictionary collections and database connections manager tools for configuration management. Also better generation of dictionaries from spreadsheets and a new spreadsheet to dictionary utility XSLT tool. Dictionary XML component generation has also been improved adding a new Components section to itemize components in dictionaries along with more and more consistent handling for dictionary content types, rules and annotations.

The template evaluation report and NIEM NDR (Naming and Design Rule) checking is improved including better representation terms.

The XSD schema importing and exporting now supports the use of Appinfo tags for application specific detailing of exchange data relationships.

For models we have enhanced the Mindmaps to include color coding of Added and Updated annotations plus SQL DBmappings and choice items.

For reports we have added a new Export to XML option for the popular Tabular Report view. This exported XML is compatible with importing into an Excel spreadsheet or can be custom rendered using a stylesheet or XSLT transformation.

Several enhancements have been made to the CAMV validation engine along with XSD schema generating and annotations handling. For Open-XDX SQL data integration we now have a nifty utility that can generate MySQL database tables from CSV text file data exports.

In summary the new CAM Editor V2.4 provides the following improved functionality:

  • All new XPath rules entry Wizard tool

  • Significantly enhanced Dictionary generation

  • Collaboration support including Oracle Enterprise Repository (OER) and URL locations

  • Better dictionary collection and SQL database connections management

  • Enhanced Mindmap model generating

  • XML export format for Tabular Report View

  • Italian language localization

  • CAMV rules engine improvements

  • New spreadsheet handling utilities

  • More consistent NIEM NDR evaluation

    To download the latest software please see the download site.

    Saturday Mar 02, 2013

    Migrate Excel text CSV / SQL Server CSV -> SQL TABLE CREATE / INSERT data tool

    Loading CSV text data into a MySQL database table is an art form. Obviously the text data can trip up for a variety of reasons from non-unique keys to missing data columns to invalid number or date formats. I recently needed to load over 20 such tables from a SQL Server CSV text file dump.  The same techniques would work for data from an Excel csv text file too.

    To automate the process as much as possible I wrote a quick XSLT utility (called converter-txt-2-sql.xsl) that reads in the CSV text file, examines the first line that contains the table column field names, then analyses the samples by scanning the entire input data lines, and generates a valid CREATE TABLE {name} ( {column(s)}); SQL statement.  Then in a second pass it builds the INSERT VALUES ({data}) statement for all the following data lines in the CSV.

    It does a pretty good job, about 98% of what you need.  You still need to do some manual editing of the CREATE TABLE SQL generated.  Essentially it can only guess at the lengths for each column - so you may want to manually adjust those, along with setting the key field column name (it assumes the first one for that), and then if your data is null or unique and so on.  But those are quick edits once it has all the basics there for you.

    It assumes that each line is one data record in the CSV text file input; so you cannot have multiple linefeeds inside your data lines, only one at the end of each line; a fair assumption most of the time.

    Having got it working I was able to load up the twenty tables in less than an hour.  There is still room to improve the XSLT logic to handle various edge conditions better, but for the time I invested in writing the XSLT its a fair level of maturity, awaiting the next project to see if it needs more refinement.  Plus it is a nifty example of using XSLT to read in a text source file and output text (in this case SQL statements).  Note: depending on your XSLT processor (I used Saxon) you may need to feed a dummy xml file in e.g. <dummy/> just to satisfy the processing engine.

    Anyway - you can find the XSLT here:  and download the converter-txt-2-sql.xsl and try it for your own text data loading needs. It is provided "as is" so use at your own discretion and you should probably have run XSLT before using a tool like OxygenXML or similar that supports the Saxon processor.

    I found it worked well to migrate over SQL Server tables quickly into MySQL just working from the raw csv text export files from SQL Server that had been sent to me.  It's not completely perfect but it should suffice for proof of concept purposes and quick demonstrations. And you have to know what you are doing, to be able to resolve syntax and data integrity errors.  However I was able to load over 50,000 data records well enough.

    Of course if you can get a live connection from MySQL to SQL Server then you can use the built-in migration tools MySQL has.  This little XSLT utility is useful when you do not have that option.  Or if people are using Excel spreadsheets with data tables and you want to convert those over to SQL tables.


    Not all XML is created equal. XML Orb looks at the challenges of creating information exchanges with XML and NIEM and how this can be made simpler, comprehensible, consistent and reliable.


    « July 2016