Loading data from a text file Informix

Question

I want to know if there is any other way to load data from a text file other than using external tables.

Text file looks something like

101 fname1 lname1 D01..
102 fname2 lname2 D02..

I want to load it into a table with columns emp_id, fname, lname, dept etc.

Thanks!

Hi, Just my opinion... I see the job of load data in two way, from client or from server. From server/database this is something where the DBA/Admin should get be involved because there you are "disturbing" the infrastructure of your system and for good performance and implementation is better use the database tools which your DBA/Admin should support you (for Informix, the options are: **HPL, dbload, dbaccess+load, external tables**). From client side, you can use any tool for that or just write your own tool and use it as your will — ceinmart, Dec 21 '13 at 11:09
possible duplicate of [How to open and read a file in Informix](http://stackoverflow.com/questions/20663446/how-to-open-and-read-a-file-in-informix) — ceinmart, Dec 24 '13 at 00:36

score 1 · Answer 1 · answered Feb 20 '18 at 15:15

There are three utilities in Informix to load data to the database from flat files:

Load SQL command. Very simple to use, but not very flexible. I would recommend this for small amounts of records (less than 10k)

Dbload, which is a command line utility, a bit more complex than the load sql command. This will allow you to have more control on how the records are loaded: commit intervals, starting point in the flat file, number of errors before exiting, etc. I'd recommend this utility for a small to medium sized data loads (>10k<100k).

HPL, or High Performance Loader, which is a rather complex utility that can load data at a very high rate of speed, but with a lot of overhead. Recommended for large to x-large data loads.

score 0 · Accepted Answer · edited May 23 '17 at 12:28

As ceinmart suggested in comments you can do it from server side or from client side. From server side you can use DB-Access and LOAD command. From client side you can use any tool you like. For such tasks I often use Jython that can use Python string and CSV libraries as well as JDBC database drivers. With Jython you can use csv module to read data from file and PreparedStatement to insert it into database. In my other answer: Substring in Informix you will see such PreparedStatement.

Loading data from a text file Informix

2 Answers2