This blog post is the first in a four-part series that explores the complex topic of managing CSV files, including importing flat files into databases, exporting flat files from any source system, and exploring/discovering flat file content using SQL commands.

In most cases, exploring, importing and exporting flat files, including CSV files is a convoluted process that involves a series of steps that includes multiple technologies depending on the task at hand, including Excel for discovery, PowerShell, command-line utilities and Integration platforms (such as SSIS) to perform scheduled operations. This article provides an overview of the ways these use cases can be solved using Enzo and the power of the SQL language, allowing users to explore, import and export flat files easily.

CSV Use cases

Let’s start by exploring the CSV consumption use cases at a high level and discuss common challenges and solutions that exist. Using flat files as a way to export data is useful when it needs to be copied, or imported into another system, and is commonly used as a way to exchange information between organizations. Flat files may be compressed and stored on remote cloud platforms (such as Dropbox, GoogleDrive, or even as an Azure Blob).

The common use cases related to managing flat files can be grouped as follows:

• Explore flat file content
• Export to a drive or cloud storage
• Import into a database
• Schedule export and import operations

Many cloud platforms (such as MailChimp for example) can also use flat files to import data; we are considering this use case as a manual import mechanism for the purpose of this discussion, and is not covered in this article.

Exploring Flat Files

Exploring flat files can be challenging for many reasons since it is necessary to have some preliminary information about the layout of the file (headers, data types, field length…). In addition, many files are stored on remote systems that are not easily accessible (FTP sites, cloud drives…) and can be compressed. Last but not least, it is usually necessary to import flat file data into a database table before it can be inspected using SQL commands to apply filters.

With Enzo, it is possible to perform rapid inspection of flat files using SQL commands without having to load the data into a database first. This can be performed on the fly, directly against the source file wherever it lives (such as an FTP site). For example, the following commands read all available records from a flat file stored on an FTP site, and from a SharePoint document.

The power of the above statements lies in the path argument, allowing the user to point to any supported storage, such as OneDrive, Dropbox, SharePoint lists, S3 buckets, Azure Blobs and more. Enzo also allows you to read compressed folders (Zipped documents) directly and cache a local copy for faster exploration.

For a more in-depth discussion on flat file exploration, see How to Read and Explore CSV Files from SQL Server

Exporting Flat Files

At times it may be necessary to export flat files in a specific format directly using an SQL command. For example, some organizations need to export data from a database platform (such as MySQL, or SQL Server), or even the output of a Stored Procedure. It may also be necessary to export data stored in a cloud platform (such as MailChimp, or a SharePoint list) as a CSV file so that the data can be shared with other systems.

Enzo simplifies the creation of flat files by allowing users to execute an SQL command that first extracts the source data and saves it in the desired target file (raw CSV file or compressed Zip file), including local drives, cloud drives and SharePoint Documents. For example, the following command exports all columns and rows of a SharePoint list into a local file the file layout specified in a configuration setting named generic.

For more information on how to export data from a source system into a flat file, see How to Export Data as a CSV using SQL

Importing Flat Files

Another important use case is the ability to import flat files into a relational database. There are many challenges related to data import, including the need to create the target table with the correct headers, and determining whether records need to be inserted or updated if the intent is to refresh data that may have changed.

Since Enzo exposes data as rows and columns, regardless of the source system, it is possible to import data into SQL Server easily. The capability exposed in the SQLServer adapter uses the BulkImport operation behind the scenes for optimal performance, and offers the option to automatically create the target table, and update records that already exist if a key has been defined (this is referred as an Upsert operation). For example the following command imports data from a flat file located on an FTP site, and insert it into a table called ARRIVED. Additional options allow creating the target table if it doesn’t exist, and whether an Upsert operation should be performed.

For more information on how to import data from a source system into SQL Server, see How to Import CSV Data into SQL Server

Scheduling Operations

Most organizations need to schedule import and export operations to operationalize their data movement in a way that is more systematic. This typically involves more complex tools, such as Integration platforms (including SQL Server Integration Services, or SSIS), and can become complex to implement and support.

Enzo provides an internal schedule mechanism that runs operations asynchronously and optionally on a schedule. And since Enzo understands SQL commands natively, it is possible to use Microsoft's SQL Server Agent to run the above commands on schedule.

The previous links related to import and export data cover the scheduling options provided by Enzo, removing the need to complex integration platforms for most scenarios.

Conclusion

This blog discusses the main use cases for reading, importing and exporting flat files using SQL commands, including the ability to discover flat file content, create target tables, access files on cloud drives and schedule operations. The capabilities offered by the Enzo platform allow organizations to simplify the management of flat files and removes the need for more complex integration tools for most use cases.

To try Enzo at no charge, download the latest edition of Enzo Server on our website.