Introduction:​

In this how-to article, we will explore how to efficiently process CSV data using SQLite. Subsequently, we'll demonstrate how to access SQLite databases using the USqlStatement class to execute arbitrary SQL commands.

Prerequisites:​

Before we dive into the process, make sure you have SQLite tool binaries for Windows in place. You can download it from the official website (https://www.sqlite.org/download.html). In this article, we utilize the command-line tool sqlite3.exe.

A note on command-line (CLI) tools for database administration: most database management systems provide command-line tools for performing administrative tasks. Importing large sets of (CSV) data is one example for a task which can be performed very efficiently by a command-line tool. Copying the data to and running the CLI tool directly on the server reduces network traffic and server load by performing a batch import of the data, which is one of the goals to be achieved for this use-case.
Click to expand...

​

Step 1: Importing CSV Data into SQLite​

To process CSV data using SQL, you need to first import the CSV data into an SQLite database. Follow these steps:

Create the import script and store it on drive. In SQLite, you can import a CSV file with tab-separated columns and CRLF-separated rows using the .import command:
Xbase++: 
   cSql := ""
   cSql += '.mode tabs' + CRLF
   cSql += '.import input.csv mytable' + CRLF
   cSql += '.quit' + CRLF

   MemoWrit( "importtable.sql", cSql )
Create a SQLite database and import the CSV data with the script just created by using the SQLite CLI:
Xbase++: 
   RunShell( '/C sqlite3.exe file.db ".read "importtable.sql""' )

Step 2: Accessing SQLite Database with the USqlStatement class​

Now that your CSV data is in an SQLite database, you can access it using the Xbase++ USqlStatement class. Follow these steps:

Attach the SQLite database to the USqlStatement class using the :attachDatabase() class method:
Xbase++: 
   USqlStatement():attachDatabase( "file.db" )
Create a USqlStatement object and prepare an arbitrary SQL statement
Xbase++: 
   oStmt := UsqlStatement():new()
   oStmt:fromChar( "select * from mytable limit 5;" )
   oStmt:build()
Execute the query. Here we collect the resultset in an array of objects and print their JSon representation:
Xbase++: 
   oStmt:query(USQL_RESULT_OBJECTS, @aData)

   FOR n := 1 TO Len( aData )
      ? Var2Json( aData[n] )
   NEXT

Conclusion:​

By importing CSV data into an SQLite database and accessing it using the Xbase++ USqlStatement class, you can efficiently process your data using SQL queries. This approach provides flexibility and the power of SQL to work with your CSV data seamlessly.