-
Notifications
You must be signed in to change notification settings - Fork 0
/
Read Me.txt
106 lines (82 loc) · 6.47 KB
/
Read Me.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
Data Lake Export
Data Lake Export is a small tool to allow the creation of files from the Infor© Data Lake SaaS product.
It uses the general SQL syntax supported by the Infor© Compass API to pull the information, and based on the command line options, generates a formatted Excel, PDF, or a CSV document. To connect to the Compass API requires an IONAPI© authorization file. This tool was specifically tested with Infor M3© in mind, but should work with any JSON data in the Data Lake.
Image of Workflow
Usage
Data Lake Export works by reading the query from a file, connecting to the Data Lake via ION API, and taking the result set and formatting it into the requested output document.
Quick Steps to Use:
Create an ION API Authorization file for Data Lake Export
Prototype your query in the Compass Query tool in ION Desk
Save the query in a file accessible by the application
Run the command specifying the desired output type and filename
An ION API authorization file to be created as a Backend Service for this application to use. Create the authorization file in the ION API application in IOS©, and save it to your local machine.
Write a sample query in Compass in ION Desk. For M3 data, a simple test would be to select something from the Item Master.
Query: Select top 100 CONO, ITNO, FUDS from MITMAS order by CONO, ITNO
After testing the query, save it on the computer where the Data Lake Export application can access it. With the query saved, and the authorization file saved, you’re ready to build a file.
Syntax Examples
In the Samples folder in the source code, you will find some sample SQL queries and batch files to generate various file types. The samples use Windows Environment variables for the connection information. You will need to set these variables for your environment, or edit the files as needed.
Required Parameters
SQL
Set to the filename containing the Compass / SQL query for the Data Lake.
Filename
Set to the file name you wish to create. This file will be deleted if it exists. The default file type is Excel© xlsx.
Connection
Set this to the filename of the IONAPI Authorization file used to connect to the Data Lake.
Compass
Set this to the URL for the Compass endpoint for the Infor IOS© environment you will be connecting to.
Title
Set this to the title of the document. Make sure the title is Excel compatible.
Optional Parameters
PDF
Creates a PDF File
Set to 0 to generate a PDF and set paper size based on data width
Set to 1 to force to fit a letter landscape. This will truncate data that’s too wide.
Set to 2 to force to fit a legal page, landscape. This will truncate data that’s too wide.
Set to 3 to force to fit a legal page, landscape, 8 point font. This will truncate data that’s too wide.
Set to 4 to force to fit a letter page, landscape, 8 point font. This will truncate data that’s too wide.
CSV
Create a Flat File
Set to 1 to set the Column delimiter to tab and the field delimiter to single quote
Set to 2 to set the Column delimiter to comma and the field delimiter to single quote
Set to 3 to set the Column delimiter to tab and the field delimiter to double quote
Set to 4 to set the Column delimiter to comma and the field delimiter to double quote
Set to 5 to set the Column delimiter to tab and the field delimiter to nothing
Set to 6 to set the Column delimiter to comma and the field delimiter to nothing
Set to 7 to set the Column delimiter to nothing and the field delimiter to nothing
Set to 8 to set the Column delimiter to pipe and the field delimiter to nothing
EOL=""
Override the default environment linefeed text
LF = Char(10)
CR = Char(13)
LF, CR, CRLF, and LFCR are valid options
Sample Syntax
Create a PDF
DataLakeExport PDF=0 SQL=“basic.sql” Filename=“Sample Export.pdf” Connection=InforDEV.ionapi Title=“Basic Test Query” Compass=https://mingle-ionapi.inforcloudsuite.com/YOURENVIRONMENT_TRN/IONSERVICES/datalakeapi
Create an Excel workbook
DataLakeExport SQL=“basic.sql” Filename=“Sample Export.xlsx” Connection=InforDEV.ionapi Title=“Basic Test Query” Compass=https://mingle-ionapi.inforcloudsuite.com/YOURENVIRONMENT_TRN/IONSERVICES/datalakeapi
Create a CSV flat file
DataLakeExport CSV=3 SQL=“basic.sql” Filename=“Sample Export.csv” Connection="%DL_ConnectionFile%" Title=“Basic Test Query” Compass=%DL_CompassURL%
Dependencies
EPPlus by EPPlus Software AB
This application uses the EPPlus library which is licensed via the PolyForm NonCommercial License. If you run the binary application, no license is required. EPPlus 5 uses a dual license model, with Polyform Noncommercial as the community license and a commercial license for commercial businesses. The commercial license can be purchased at epplussoftware.com.
See https://epplussoftware.com/en/Home/LgplToPolyform for more details.
Flurl by Todd Menier
Flurl is licensed under the MIT License.
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions… (see link for full text)
https://tldrlegal.com/license/mit-license
IdentityModel by Dominick Baier, Brock Allen
IdentityModel is licensed under the Apache 2.0 license.
You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions… (see link for full text)
https://tldrlegal.com/license/apache-license-2.0-(apache-2.0)#summary
PdfSharp.MigraDoc.Standard by MigraDoc.Rendering
PDFsharp and MigraDoc Foundation are published under the MIT License.
See the PDFsharp License.
See the MigraDoc Foundation License.
PDFsharp and MigraDoc Foundation are Open Source and free to use
Copy, modify and integrate the source code of PDFsharp and MigraDoc Foundation in your applications without restrictions at all
This also applies to commercial products (both Open Source and Closed Source)
PDFsharp and MigraDoc Foundation will remain Open Source and free to use even in the far future
Data Lake Export Warranty
Copyright © 2020 Billy Willoughby
This program comes with ABSOLUTELY NO WARRANTY;
This is free software, and you are welcome to redistribute it under certain conditions, see the License.txt file for further details.