NFT - no-frills-transformation

"No frills transformation" (NFT) is intended to be a lightweight transformation engine, having an extensible interface which makes it simple to

• Extend with Source Readers
• Extend with Target Writers
• Extend with additional Operators (if you can't do with the Custom Operators)

Out of the box, NFT will read from

• CSV files in any encoding
• Salesforce SOQL queries (NOT YET PORTED TO NET8.0)
• SQLite Databases (NOT YET PORTED TO NET8.0)
• MySql Databases (NOT YET PORTED TO NET8.0)
• Oracle Databases (NOT YET PORTED TO NET8.0)
• SQL Server Databases
• PostgreSQL Databases (NEW!)
• From SAP RFCs if they have a TABLE as output value (limited support currently) (NOT YET PORTED TO NET8.0)
• Acumatica Snapshots/Database templates
• JSON (also flattening) (NEW!!)

and write to

• CSV files in any encoding (including with or without UTF-8 BOMs)
• Salesforce Objects (including Upserts and using External IDs) (NOT YET PORTED TO NET8.0)
• Oracle Databases (NOT YET PORTED TO NET8.0)
• Rudimentary XML files
• SQL Server Databases (or: Insert Writer) (NEW!)
• PostgreSQL Databases (or: Insert Writer) (NEW!)
• Acumatica Snapshots/Database templates
• JSON (also including sub-documents) (NEW!)

A special "transformation" filter is supported, which currently implementations for

• SAP RFC Transformations: Read the parameters from a source and pass them to the RFC and retrieve the results from that to the output (NOT YET PORTED TO NET8.0)
• Frequency Transform: Creates basic frequency statistics from a source
• Salesforce SOQL Transform: For a set of input parameters read from a source, issue SOQL queries and output the result to a target. (NOT YET PORTED TO NET8.0)

There may be more to come; and if you have special needs, feel free to reach out and we'll look together what we can do about it.

In an ETL scenario, NFT is not specifically designed to do the "E" nor the "L" part, mostly just "T" tasks. But that quickly and efficiently, supporting the basic transformation stuff you might need (and with extensibility support if you need something out of the order). For convenience, the "E" is supported better than "L", with e.g. a Salesforce Reader for SOQL queries.

Among supported transformations are:

• Copy (nop transformation, copy source to target) using the source field operator, e.g. $FirstName
• Lookup (in other sources), using the lookup operator; see also lookup definitions in the config file
• Filtering (on source data)

Feel free to contribute and create pull requests. I'll check them out and merge them if they make sense.

Head over to the WIKI for a comprehensive documentation:

• https://github.com/Haufe-Lexware/haufe.no-frills-transformation/wiki

Binary Downloads

Currently, there are no binary downloads - compiling with dotnet is simple enough right now

Check out the releases section for binary packages of NFT.

See also the installation guide.

Acknowledgements

NFT (the CSV plugin to be more precise) is using parts of the brilliant CSV reader library written by Sebastien Lorion. You can find the original project page at CodeProject:

http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader

NFT has pulled in the CSV parts in the source code directly. They can be found here:

https://github.com/Haufe-Lexware/haufe.no-frills-transformation/tree/master/src/3rdParty/LumenWorks.Framework.IO

Configuration

The basic idea of NFT is that you configure the transformations using an XML file. The XML file contains the following main parts:

• A data source
• Source filters
• A target
• Lookup map definitions
• Target field definitions

Sample XML config file

A typical simple XML configuration may look like this:

<?xml version="1.0" encoding="utf-8"?>
<Transformation>
  <Logger type="file" level="info">C:\temp\log.txt</Logger>
  <Source config="delim=','">file://C:\temp\users.csv</Source>
  <Target config="delim=','">file://C:\temp\Accounts.csv</Target>
  <LookupMaps>
    <LookupMap keyField="id" name="Status">
      <Source config="delim=','">file://C:\temp\status_mapping.csv</Source>
    </LookupMap>
  </LookupMaps>

  <FilterMode>AND</FilterMode>
  <SourceFilters>
    <!-- In shorter, this is just Contains($SOBID, "A") -->
    <SourceFilter>Equals(If(Contains($SOBID, "A"), "hooray", "boo"), "HooRay")</SourceFilter>
  </SourceFilters>

  <Fields>
    <!-- The target row number -->
    <Field name="RowNo" maxSize="10">TargetRowNum()</Field>
    <!-- The source row number -->
    <Field name="SourceRowNo" maxSize="10">SourceRowNum()</Field>
    <!-- Plain copy of field content -->
    <Field name="SapUserId__c" maxSize="16">LowerCase($SOBID)</Field>
    <!-- Concatenation of two source fields -->
    <Field name="Whatever" maxSize="50">$OTYPE + $OBJID</Field>
    <!-- status mapping from lookup "Status" (see definition above) -->
    <Field name="Status" maxSize="40">Status($BOGUSTYPE, $salesforce_status)</Field>
  </Fields>

  <OperatorConfigs>
    <OperatorConfig name="equals">ignorecase</OperatorConfig>"
  </OperatorConfigs>
</Transformation>

So, what does this do?

• Defines log output to be written to a file (log.txt)
• Reads data from C:\temp\users.csv
• Writes data to C:\temp\Accounts.csv
• Loads a lookup map into the operator Status from C:\temp\status_mapping.csv
• Sets the filter mode to AND (all filter criteria must be met)
• Specifies a filter by using an expression which evaluates to a boolean
• Specifies a mapping with five output fields, each with a name, max size and an expression
• Passes on a configuration string to the Equals operator to make it behave as EqualsIgnoreCase

Please confer with the Wiki for a more detailed specification of the options:

• https://github.com/Haufe-Lexware/haufe.no-frills-transformation/wiki

All links on this page lead into the wiki.

See also:

• Getting Started
• Config File Documentation

Running the application

Just call the main DLL using dotnet with the full path to the XML configuration file.

C:\Temp> dotnet NoFrillsTransformation.dll sample_config.xml
Operation completed successfully.
C:\Temp> 

If the operation completes without error, the executable with exit with the exit code 0 (Success). Otherwise, an error message will be output to stderr and the exit code unequal 0 (for now, 1).

Running on Mac OS X and Linux

Same as for Windows, simply use the dotnet command line. Make sure you download the correct version of the published DLLs, as there are changes in some of the platform dependant libraries (such as SQL Server).

Alternatively, just clone the repository and run it using dotnet run:

dotnet restore
dotnet build
dotnet run <transformation.xml>

Packing as a tool

The configuration of NoFrillsTransformation.csproj contains the information on how to package the tool as a dotnet tool, which is probably the best way to distribute nft. It can create an nft command line tool which can be pushed e.g. to a private NuGet repository, and subsequently pulled from there.

Sample scripts (PowerShell), expecting the version as $version:

# Usage: ./release.ps1 <version>
param(
    [Parameter(Mandatory = $true)]
    [string]$version
)

Push-Location src

# Replace the Version in the Directory.Build.props file
$propsPath = Join-Path $PSScriptRoot 'Directory.Build.props'
$propsContent = Get-Content $propsPath -Raw
$propsContent = $propsContent -replace '<Version>.*?</Version>', "<Version>$version</Version>"
Set-Content $propsPath $propsContent

# Clean and build
& dotnet clean
& dotnet build

# Remove old nupkg directory
Remove-Item ../nupkg -Recurse -Force -ErrorAction SilentlyContinue

# Pack and push into ../nupkg
& dotnet pack -c Release -p:PackageVersion=$version
& dotnet nuget push -s lexbizz -k <some PAT> ../nupkg/*.nupkg

Pop-Location

This assumes you have a suitable nuget.config in the src folder.