Free SQL Help for Devs, DBAs, and the Curious

One of the essential tasks for any database administrator is to import data from flat files into relational databases. SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL provide some powerful features to process flat files. One such feature is the OPENROWSET function. This function allows you to read data from a file and return it as a set of rows and columns. In this blog post, we look at how to use the OPENROWSET function for flat file processing.

In SQL Server, the OPENROWSET function is used to connect to remote data sources and retrieve data. To import data from a flat file, we use the BULK option of the OPENROWSET function. The following T-SQL code illustrates how to use OPENROWSET with a CSV file:

SELECT * FROM OPENROWSET(BULK 'C:\path\to\your\file.csv',
 SINGLE_BLOB) AS Data;

The above command reads the entire content of the file.csv file into a single row and column. If you want to read the data as multiple rows and columns, you should provide a format file. A format file describes the format of the data file so that SQL Server can interpret the data correctly.

While OPENROWSET is an excellent option for SQL Server and Azure SQL, it is not available in MySQL or DB2. However, in MySQL 5.7, you can use the LOAD DATA INFILE statement to read data from a file into a table. Here is an example:

LOAD DATA INFILE 'C:/path/to/your/file.csv'
 INTO TABLE your_table
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n';

This command reads the file.csv file and inserts each line as a row in the your_table table. The FIELDS TERMINATED BY and LINES TERMINATED BY clauses specify the column and line terminators, respectively.

DB2 11.1 offers the IMPORT command to load data from a file into a table. The following command imports data from a CSV file into a table:

IMPORT FROM 'C:/path/to/your/file.csv'
 OF DEL METHOD P (1, 2, 3) INSERT INTO your_table;

This command imports data from the file.csv file and inserts it into the your_table table. The METHOD P (1, 2, 3) clause specifies the column positions in the source file.

In conclusion, the OPENROWSET function in SQL Server and Azure SQL provides a powerful way to process flat files. Even though MySQL and DB2 do not offer the OPENROWSET function, they provide their own unique solutions to import data from flat files. Regardless of the database system you're working with, there's a way to efficiently process flat files to suit your needs.