NOTE! This site uses cookies and similar technologies.

If you not change browser settings, you agree to it. Learn more

I understand

Etl-tools.com announces partnership with Slemma

slemma

https://slemma.com/

About Slemma

Slemma is a simple business intelligence software that helps modern businesses leverage data into advantages online with less effort. Plug in directly to the most popular databases and cloud services; create, customize, and share dashboards and visualizations with the entire team.

Working with Slemma

loading data into slemma

All our ETL products support now loading data into slemma
Connecting to Slemma Wiki Page

Based on the customer feedback in the latest version of Advanced ETL Processor we have introduced support for JSON

What is JSON

JSON is short for JavaScript Object Notation. It is a way to store information in an organized, easy-to-access manner. In a nutshell, it gives us a human-readable collection of data that we can access in a really logical manner.

Benefits of using JSON

JSON is much more efficient than XML. It reqires less network traffic processing power and memory to work with  

More Information

  1. JSON only has three simple types – strings, numbers and Booleans – and two complex types – arrays and objects.
  2. A string is a sequence of zero or more characters wrapped in quotes with backslash escapement, the same notation used in most programming languages.
  3. A number can be represented as integer, real, or floating point. JSON does not support octal or hex. It does not have values for NaN or Infinity. Numbers are not quoted.
  4. A JSON object is an unordered collection of key/value pairs. The keys are strings and the values are any of the JSON types. A colon separates the keys from the values, and comma separates the pairs. The whole thing is wrapped in curly braces.
  5. The JSON array is an ordered collection of values separated by commas and enclosed in square brackets.
  6. The character encoding of JSON text is always Unicode. UTF-8 is the only encoding that makes sense on the wire, but UTF-16 and UTF-32 are also permitted.
  7. JSON has no version number. No revisions to the JSON grammar are anticipated.
  8. JSON has become the X in Ajax. It is now the preferred data format for Ajax applications. The most common way to use JSON is with XMLHttpRequest. Once a response text is obtained, it can quickly be converted into a JavaScript data structure and consumed by a program
  9. JSON's syntax is significantly simpler than XML, so parsing is more efficient.
  10. JSON doesn't have namespaces. Every object is a namespace: its set of keys is independent of all other objects, even exclusive of nesting. JSON uses context to avoid ambiguity, just as programming languages do.
  11. JSON has no validator. Being well-formed and valid is not the same as being correct and relevant. Ultimately, every application is responsible for validating its inputs.JSON Examples

JSON array

{
"Drinks": ["Water", "Beer", "Vine"]
}

JSON Objects

{ 
  "Patient": {
    "ID": 1,
    "Name": "Batman"
    "Age": 23,
    "friends": ["SpiderMan","IronMan"],
    "complaint": "Blured night vision"
   }
}

Advanced ETL Processor JSON Transformations

json transformation functions

Example

json transformation example

Visit JSON Wiki Page

Quite often people ask us how to stop all ETL packages execution.

“When I stat Advanced ETL Processor it immediately runs transformation package. How can I avoid it?”

Most of the time, this is because Professional version is being used to execute the packages.

Better option is to use Enterprise version agent and run tasks in parallel.

To stop execution

  1. close Advanced ETL Processor/Visual Importer ETL
  2. click  Start => DB Software Laboratory => Advanced ETL Processor Enterprise=> Advanced ETL Processor Enterprise Options=>Execution
  3. and check ‘Do not execute any actions’

Options

This work for both Advanced ETL Processor and Visual Importer ETL

Related forum posts:

A Question from the Customer:

An external PLC generates txt files and those must be downloaded and converted into an SQL DB.
The PLC generates 4 files each day, at a size of 5MB pr. File = 20MB a day.
The file name is date and time named like this : 14022719.txt, syntax is YYMMDDHH
The files are generated at the same 4 hours each day, 01, 07, 13, 19
In the files, first data row is a time stamp YYMMDDHHMMSS
It is important that one record with a specific date/time is written only once in the SQL.
The files would always be the same size.

ETL Job:

Each day, The ETL converter should copy the file to a local directory via FTP; convert the file to SQL, then delete the file from the local dir. It could be 4 times a day, maybe one hour after the txt file is completed on the PLC.
We can not delete the file from the PLC, since others should also have access to the data.
The data on the PLC will be deleted after 10 days using FIFO

The problem:

How to I keep track of which files I have copied ?
Could create a TXT file on local drive and write the file names, compare with whats on the FTP drive ?
But how do we do this ? That is file comparison..can ETL do this ?
Is there any other way to do this ?
Keep in mind, it could be that the internet connection is off line for a day or 2, the server could be down,
The PLC could be down. In some situations you will need to copy 4 files, some times 6 files..depents..
Or you could have gaps in the time stamps of the file names from the PLC if it has been down for a while.
I know we would then also have gaps in the SQL, but that is OK.

The solution:

Bye the way….. Your software rocks big time. This is the easiest way I have ever found to deal with disparate databases!!!!!!!!!!!!!!!!!!!!

George Martin

Our customers

BP

BBC

HSBC

Orange

CAT

SEPA
emc
NHS
Australian Rugby Union
xerox
Databases we work with