Import CSV Data Directing to Db2 for i Files

Chances are you're familiar with CSV files. They're a common format for exchanging data that is transferred between applications and different systems. CSV data is not easy to work with in RPG IV on IBM i and when it comes to storing that data in a database file, there's often a multi-step process involved in importing the CSV file. This can be time-consuming and error-prone, especially if you're working with large datasets. A good indication of the level of experience of the Programmer who created your CSV import processing routines is directly related to the number of steps involved in that import process, or its lack of flexibilty.

But what if you could skip that step entirely? What if you could read the CSV file directly, and produce a result similar to reading any other database file without having to create temporary work files? That's exactly what you can do with the CSV functions included in SQL Tools for IBM i.

SQL Tools provides SQL functions and features for working with delimited IFS files (aka CSV files). One of those features is the CSV table function that allows you to read CSV files using SQL, and insert that data directly into your existing Db2 for i database files.

Why should you consider using SQL Tools and its CSV SQL functions to read CSV files directly into your database tables? Here are a few advantages:

1. Time savings: Importing a CSV file into a database can be a time-consuming process, especially if you're working with large datasets. Reading the CSV file directly into your database table using SQL Tools eliminates that step, saving you time and effort.
2. Accuracy: Building custom code that Imports CSV data into a database can be error-prone, especially if you have to map the fields in the CSV file to the columns in your database file. Using SQL Tools CSV functions significantly reduces the chance for error, while allowing you to easily adjust and customize that mapping based on your requirements, using simple SQL syntax.
3. Flexibility: Reading CSV files directly into your database table using SQL Tools gives you more flexibility in how you work with your data. For example, you can easily clone the SQL statement for other CSV to database file import routines, with fewer errors. Typically an entry-level developer can quickly clone and adjust the import statements.
4. Automation: If you're working with CSV data that is regularly updated, using SQL Tools CSV functions to read CSV files directly into your database table can be automated using a script tool (such as IBM ACS RUNSQL Scripts, or in a batch job using SQL iQuery or even the RUNSQLSTM CL commands. This can help streamline your workflow and reduce the risk of errors.

How do you use SQL Tools and the CSV SQL functions to read a CSV file directly into your Db2 for i database table? Here's a quick overview:

First, let's establish the Db2 for i database file. The SALESPRICE file defined below will eventually receive the CSV data; it contains 3 columns or fields as shown:

CREATE TABLE SALESPRICE 
    ITEMID INT NOT NULL DEFAULT 0,
    DESCRIPTION VARCHAR(50),
    SALEPRICE   DEC(7,2)
);        

And now the CSV file that is uploaded to the IFS using whatever method you choose:

ITEM, Description, Price
32, "Plastic Cases", 2.37
34, "Cardboard Covers",1.98
36, Dishsoap, 2.98
38, "Apple Sauce", 3.29        

To insert these rows directly into the database file named SALESPRICE you would use the SQL INSERT statement or potentially the SQL MERGE statement. But let's look at how to simply read that CSV data using SQL. A SELECT statement similar to the following could be used.

SELECT csv_int(data,'item') as ITEM,
       cast( csv_char(data,'description') as varchar(50)) as Desc,
       cast( csv_dec(data, 'itemprice') as Dec(7,2)) as Price
FROM table(sqlTools.csv_reader('/home/cozzi/SalePrices.CSV')) SP;         

With this SELECT statement, the CSV data is returned just like a regular IBM i database file. With it, you could easily create an SQL VIEW that is then used similar to a legacy Logical File and then read that View's data using traditional RPG IV record-level I/O (i.e., READ, CHAIN, etc.) Better yet, it can also be retrieved using SQL SELECT/FETCH processing, which is the preferred method. That way the only thing you actually need to do is update the CSV file on the IFS and the View immediately represents the new data to the RPG IV program. Personally, I would run an SQL iQuery script with an SQL MERGE statement to import the data into the SALESPRICE, but that's just me.

The above example uses 3 of the SQL Tools CSV functions. Each function has 2 parameters:

1. The delimited data whose content is to be returned. This is always specified as the column named DATA which is returned from the CSV_READER table function.
2. The CSV column name to be extracted. This name can be either the column header as specified in the CSV file, or it can be a numeric value indicating the relative column number. In this example, that would be 1, 2 or 3. Upper/lower case for the column header name is ignored as are any embedded blanks, making specifying the name less error-prone.

There are several SQL CSV functions provided, but the 3 most often used are those used in the prior example. Each accepts the delimited data value (the DATA field/column from the CSR_READER table function) and a second parameter that identifies the column whose data is exacted. That parameter may be either the relative column number (1 to 3 in this example) or the column header name.

• CSV_CHAR returns the value as a VARCHAR value.
• CSV_INT returns its value as an integer.
• CSV_DEC returns the value as a packed decimal or DEC value.

The output from the SELECT statement illustrated above would be similar to the following:

ITEM     DESC                 PRICE
  32     Plastic Cases         2.37
  34     Cardboard Covers      1.98
  36     Dishsoap              2.98
  38     Apple Sauce           3.29        

Once you used the SQL Tools CSV functions a couple of times, you will be surprised how quickly you can write a CSV "reader" for other files for your Company.

To read more about the powerful and efficient SQL Tools CSV functions, visit: https://www.SQLiQuery.com/SQLTools

That's all there is to it!