Parsing ASCII Files Elegantly Using SQL By Joe Lumbley, Informix Pro
A common task for database administrators is converting external data—such as data from external programs, spreadsheets, legacy systems, or captured data—into a format that can be loaded into a database.
The typical way to handle external data is to create a data file from the outside source and then massage the file with various methods until it meets the requirements to load into the database. This most often results in an ASCII file delimited with UNIX pipe symbols (|). This data massaging is usually accomplished by a parser program in C, awk, shell, perl, or another language. After the file is created, it is loaded into the database using either the SQL "LOAD FROM" command or the dbload program.
The problem with this traditional approach is that it requires a completely different skill set from other database administration tasks. Not all DBAs are programmers and the more different tools you use in a particular system, the more complicated subsequent maintenance becomes. The database should do the job instead!
As a DBA, you need to load external data into a database. But you're not a programmer, so you want to do this without doing any extra programming.
Create an ODBC Text data source for the input data and use a third-party tool to load the data into your Informix database. This entails several steps:
Obtain a third-party tool.
Create the ODBC data source.
Query the ODBC data source to get the data you need.
