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.

Source Data

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.

Using a SQL Server Table as the Source Data

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'

Configure the CSV Adapter

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

Configure the Source and Target Systems

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.

Exporting CSV Data

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.

Using the Path Argument

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

Exporting a Zip File

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
            

Scheduling File Exports

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.

Using an SQL Command

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

To learn more about scheduling, please see the online documentation.

Using Enzo Manager

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.

Conclusion

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.