Migrate and process Excel data

Convert, Clean, Integrate, and Mask Spreadsheets

Overview
CLF/ELF
CSV
Excel
JSON
LDIF
LS-RS-VS
MF-ISAM
MFVL
Text
Variable blocked
Vision
VSAM
XML
Challenges

Excel spreadsheets are ubiquitous sources and destinations for structured data and processes, but the data within those spreadsheets rarely begins and ends there. Data residing in other formats such as external files, database tables, and URLs must be fed into spreadsheets for analysis and exchange purposes. Conversely, data stored in Excel needs to be integrated into those sources or utilized in applications that connect to those sources.

The problem is that Excel's import and export functions, while helpful, are functionally limited and require manual intervention and separate steps to handle data conversion tasks. Furthermore, this utility can only handle or produce delimited files, but not fixed-position files, databases, pipes, or message queues.

Aside from the challenge of data migration, Excel itself lacks other important data integration and governance features, and it cannot apply its own data manipulation capabilities to other sources.

Solutions

Able CoSort v10 offer the IRI Voracity Platform software and the standalone component products linked below Excel data conversion and processing capabilities in multiple products. Choose as needed:

Use the product IRI NextForm, to move data from XLS and XLSX files into DB tables, convert sheets or ranges into other file formats (such as CSV, LDIF, COBOL, JSON, XML, etc.), or from other formats into Excel. NextForm includes an XLS/X file parser to automatically create the field layouts (and you can go in both directions based on row or column specifications, so you can also transpose data layouts) that are used in your file conversion scripts.

NextForm also supports field-level data type conversion and record layout remapping. NextForm job definitions also work in SortCL-compatible products such as Voracity, when you upgrade later.

Use the SortCL-Program in the IRI Voracity-platform or in IRI CoSort- Package for converting, transforming, masking, generating reports from, and creating Excel spreadsheets and other structured data destinations.

Declare one or more Excel and non-Excel files for input and output as part of a SortCL job that includes data:

  • CSV files transform (e.g., sorting, joining, aggregating, cross-calculating, etc.)
  • CSV files to other file formats convert and create CSV files from these formats.
  • Report from CSV file sources

with a simple 4GL for layout and manipulation definitions or a powerful free GUI on an Eclipse basis.

SortCL provides data architects working with Excel and other sources work, all these functions are available, one or more at a time. In this article series Find more details!

Use IRI CellShield or IRI FieldShield, to encrypt, mask, or otherwise de-identify values in Excel files, or IRI DarkShield, when the data you need to find and mask in your spreadsheets is less structured (like embedded images or charts with sensitive data). Look at this article where they are compared!

Use IRI RowGen, when you need to populate your spreadsheet(s) with real but synthetic test data. See Example #1 in this article. RowGen is in IRI Voracity contain and use the same layout metadata as CoSort, NextForm and FieldShield, so you can switch seamlessly between generating test data and transforming real data, masking, reporting, and so on.

All of the above (and many more!) use cases are performed through the same simple 4GL job script metadata for file layout and field editing. Automatically create, graphically modify, and team-share these metadata assets in a free, familiar IDE on Eclipse™ called IRI Workbench.

      Product links