Export data from Data Lake as CSV using Data Lake Flow Query Activity (AnySQL modeler)?

Is it possible to export data out of the data lake as a field delimited CSV using the "Query" activity (AnySQL modeler) in a Data Lake Flow?

e.g. Field1,Field2,Field3

It's possible to export as a JSON array or JSON new line delimited file, but is it possible to convert the output to a field delimited CSV using a file template? It appears the input document has to be a BOD in order to use the file template, and I haven't found a way to do that. Is scripting then the only option?

Find more posts tagged with

Infor Data Fabric

Accepted answers

unknown

Hey Andy,

We typically don't recommend using Compass with the ION API Connector largely because it can produce an arbitrarily large volume of data. AnySQL provides a managed approach towards chunking result set payloads down into more manageable pieces which helps balance throughput and delivery. Using a script to convert the payload is a more effective approach.

That said, it sounds like a great request enhancement and I'd recommend submitting an enhancement request via https://ers.infor.com/ers/ for ION or checking if one already exists. Providing a CSV result set could provide value and other folks can also vote on the request.

Can I ask what use case you're trying to solve for as well? There may be other approaches or roadmap items that could align with your use case depending on needs.

All comments

Brandon Watson

Hello Andy - AnySQL will only do JSON/NDJSON. You could use an API Connection Point to call the Compass API's and retrieve the CSV that. The main concern will be the 5mb ION BOD size limit, which i have run into before.

You could also use scripting and convert the JSON to CSV.

andy

Thanks for the insight. Have you successfully built a Document Flow that uses API connection points for the DATAFABRIC/compass/v2 endpoint?

Since there's no loop in the Document Flow, seems like you would have to call a Workflow that loops and checks the job status before returning to the Document Flow to grab the results and then write to a file.

Brandon Watson

Yes sir, I had to call a workflow to loop through until the status was "Finished".

unknown

Hey Andy,

Can I ask what use case you're trying to solve for as well? There may be other approaches or roadmap items that could align with your use case depending on needs.

andy

Thank you for the insight Mike. We are a single tenant client that is in the process of migrating to multi tenant. As such, we have a number of scheduled data exports from FSM that are sent to third parties that need to be converted to function in multi tenant. The single tenant versions are SQL exports driven by IPA process flows using Sys Commands, with the resulting output CSV file sent via FTP.

We are still performing exploratory efforts for a preferred method of data export without SQL and Sys Commands, and one proposed idea is to retrieve the already replicated data from the data lake in some manner.

I believe these single tenant exports were originally performed using SQL / IPA because the joins / filters necessitated could not be efficiently implemented with landmark transactions / exports, and replication sets were avoided due to some other limitation that I can't recall at the moment. I'm thinking it may be worth revisiting replication sets to see if a custom replication set alone could accommodate. Though, we would be essentially replicating some duplicate data already being replicated by the delivered rep sets, so that might be suboptimal as well.

andy

Thanks Brandon!

Rene Bottern

You have a couple of alternatives that I know of.

Use ETL Toolset to transfer specific Tables to a Local SQL instance. (Replication can be set to refresh as often as every 5/10 minutes)
Create a Custom Tool to replicate to local SQL instance utilizing API
This is more if you do not own the ETL Tool and don't want to pay for this, can be done in .Net for instance (I have done this before).
Create Search API's in CMS015
You can Filter on Related Tables as well using this, so you can extract from Multiple instances in 'one swoop'
Source can be either
- CMS010 - Max 300 characters output
- AHS100 - Max 800 characters output
Create Custom API(s) using XtendM3 (XM3)
You need a Certified party to create these though, Infor is not allowing Customers to create these!

I agree that the DataLake is too slow, in it's current state, for time-sensitive processes.

Hopefully this will help to get you pointed in the correct direction.

unknown

We are using DBeaver enterprise to download data from the data lake and import into local SQL. It is not perfect but it has kept us alive. Relaying solely on Birst or DataLake was not an option.

We are also considering the ETL Toolset but not sure where that is going. There is little documentation to implement and to even aquire.

Billy Willoughby

Greetings;

I have a Data Lake Flow that runs on a schedule and writes an Excel File to the EC server, and sends an email of the Excel document via IDM to the user.

I make the Data Lake Query which returns the ND JSON file, then parse it with Python to create the Excel file. It should be easy to convert to CSV.

You can grab the Python Script here:

https://gitlab.billy.today/FeuEtGlace/infor-python/-/blob/main/NJSONtoXLSXBinary.py

Email Output:

Hopefully that helps.

Billy

Maxime Richard

Hi Billy,

Nice script, its working like a charm. Very usefull ! Thank you.

Copyright © 2025 Infor. All rights reserved.