Creating flat files (including CSV files) can be challenging, specifically when considering automated data
exports and the large number of possible data sources (such as cloud platforms). While many
ETL tools can help (such as SSIS), the end solution is usually very rigid, and involves ODBC drivers
which can be costly and hard to test/build/deploy.
To demonstrate the simplicity of the ENZO solution, this blog post shows you how to export
data from virtually any system into a flat file
(such as a CSV file), and store it directly into a drive,
SharePoint Document, or cloud system.
In order to demonstrate how exporting data into a flat file works, we will use a SharePoint List. Keep in mind that any system that Enzo can communicate with, including any relational database (such as SQL Server, MySQL or Oracle), ODBC driver or even an HTTP server can be used as the source system.
In this example, we are connecting to Enzo directly, and fetching records from SharePoint Online. The SQL command returns all the available records in the US States SharePoint list. Note the syntax used to identify the list name; we added square brackets because of the space in the list name.
You can also use Enzo to execute a command against a SQL Server database as the source data. To do this,
you can either use the SQLServer or the DB adapter. The SQLServer adapter may perform better
in some instances as it is specifically built for the SQL Server database platform. However you would need
to use the DB adapter to select from other relational data sources, including ODBC drivers.
For example, to select records from the FLIGHT.Arrived table in SQL Server through Enzo, you can execute this command (using brackets is required because the Arrived table is located in a schema called FLIGHT):
SELECT * FROM SQLServer.[table@FLIGHT.Arrived]
Alternatively, you can also execute the Run command, which allows you to pass a complete native
SQL command, and also allows you to execute a Stored Procedure:
EXEC SQLServer.Run 'SELECT * FROM FLIGHT.Arrived'
Exporting flat file data requires the use of the CSV adapter. Although the CSV adapter doesn’t use a formal configuration setting like other adapters, it requires the definition of a CSV layout.
Using Enzo Manager, select the CSV adapter and create a new CSV Configuration.
In this example we have created a definition named generic as a tab-delimited file, with columns headers, and no quoted text.
It is not necessary to define the list of columns for the export operation to work; they can be inferred automatically based on the SQL call made against the source system
The CSV adapter will call the source system to retrieve the data, then call the target system to
save it; this requires that both source and target adapters be configured for access through Enzo.
For example, if you are planning to store the output flat file on Dropbox, you must first
configure the Dropbox adapter. If the target destination is your local drive, there is no
need to configure anything.
Please see the online help for more information on how to configure adapters.
We are now ready to perform the export operation. Exporting data as a flat file is done by using the
WriteCSV handler on the CSV adapter.
EXEC CSV.WriteCSV 'generic', -- the CSV Configuration 'SELECT ID, Title, [State Code], _guid_, Modified, Created FROM SharePoint.[list@US States]', -- Source SQL Statement 'c:\tmp\usStatesOut.csv' -- Target file , 1 -- 1 to override target file if needed
Additional options allow you to override the CSV output settings (such as the delimiter), and to return the actual flat file data in the output column.
The path argument accepts a URI notation in this format:
system[@container]://path/.../filename.extension Or \\server\path\...\filename.extension Or C:\tmp\...\filename.extension
In the above, system can be any of the following: sharepoint, ftp, dropbox, azurestorage, s3,
onedrive365, onedrive, googledrive, egnyte (coming soon), or HTTP. Except for HTTP, system is the name of the adapter that will
be used to read the file; as a result it is necessary to have the adapter installed through the Market Place.
Most adapters must first be configured so that Enzo can call the remote system with the appropriate credentials.
The container argument is needed for sharepoint, azurestorage and s3; it is not accepted by other systems. For SharePoint, this represents the SharePoint Document Library to use; for Azure Storage this is the name of the Blob Container (the $root container will be assumed if not provided), and for S3 the Bucket name. The remainder of the path argument is an absolute path from the root folder.
For example, to export the same data to a Dropbox file in a tmp folder, you would use this SQL command:
EXEC CSV.WriteCSV 'generic', -- the CSV Configuration 'SELECT ID, Title, [State Code], _guid_, Modified, Created FROM SharePoint.[list@US States]', -- Source SQL Statement 'dropbox://tmp/output.txt' -- Target file , 1 -- 1 to override target file if needed
You may need to export the CSV file as a compressed file. Enzo supports the Zip compression algorythm so that
you can compress the output CSV file with an optional password for protection. To export a Zip file,
simply name the file with a .zip extension. And if you have specified a default password in
the CSV Configuration, it will be used to set the password on the Zip file. For example,
the following SQL command creates and automatically compresses the CSV file as a Zip document and
stores it on an FTP server (the name of the CSV file inside the document will be usStatesOut.csv):
EXEC CSV.WriteCSV__async '0 0/15 * * * ?', -- a CRON expression 'generic', -- the CSV Configuration 'SELECT * FROM SharePoint.[list@US States]', -- Source SQL Statement 'ftp://usStatesOut.zip' -- Target Zip file
You can also schedule a job that exports data as a CSV file using Enzo. You can either run this command manually using a modified SQL command, or use Enzo Manager.
This is accomplished by running the __asyncAt extension of the WriteCSV operation (with two underscores). Virtually all handlers accept this extension, so this capability is not limited to exporting CSV files. Scheduling a job requires an additional parameter, provided as the first argument, that specifies how often the call should be made as a CRON expression. For example, the following runs a background job every 15 minutes:
EXEC CSV.WriteCSV__async '0 0/15 * * * ?', -- a CRON expression 'generic', -- the CSV Configuration 'SELECT ID, Title, [State Code], _guid_, Modified, Created FROM SharePoint.[list@US States]', -- Source SQL Statement 'dropbox://tmp/output.txt' -- Target file , 1 -- 1 to override target file if needed
HINT: Adding a ! in front of the CRON expression schedules the operation and starts running the job immediately.
To learn more about scheduling, please see the online documentation.
You can also schedule an EXEC operation with Enzo Manager.
To do so, select the BSC database on the left treeview in Enzo Manager, select the CSV adapter in the list of adapters, and click on the Add Job button. Paste the EXEC command you would like to schedule, and select the CRON schedule. Click OK to save the job.
The CorrelationID and Snapshot Name fields are optional, and represent more advanced options that are not needed for exporting CSV files.
In this blog post we discussed how to export CSV data into a target system easily using the CSV adapter,
and the WriteCSV operation. The data was read from a SharePoint Online list and exported to a flat file
using a single SQL command.
To try Enzo at no charge, download the latest edition of Enzo Server on our website.
© 2023 - Enzo Unified