Loading Flat ASCII file
As a newcomer to the Informix database, I'm experiencing the general frustrations of reading technical manuals. I'm probably in need of "Informix for Dummies" but nonetheless: I've configured IDS, have set up a database, and defined my first table. I'd like to import a flat ascii text file but I am having problems getting it to import. The method that I am using is utilizing SQL with an "insert into" method. IDS gives it a try, but it appears that I'm having field delimiter problems. The ascii file is a fixed record length, fixed field file, that is there are no delimiters. What do I need to do to simply import a common file type such as this without having to take a two year course. Assistance is appreciated!
Here's an example that should get you going.
If your table was created like this:
create table table1 (
column1 char(4),column2 smallint
);
then you would load data into it with a statement like:
load from 'file1.unl' insert into table1;
file1.unl would be formatted like this:
abcd|12345|
ab|12|
abc|1|
Pipe-delimited, no padding. Two pipes together causes an attempt to load a
null into the corresponding column, and two pipes seperated by a space
character loads a space.
Once you get some stuff loaded, you can use the inverse command to see this
in reverse:
unload to 'file1.unl' select * from table1;
Hope that helps!
Scott Henderson
Actuary <actuary@visi.com> wrote in message
news:p6qX2.2650$WA4.500327@ptah.visi.com...
> As a newcomer to the Informix database, I'm experiencing the general
> frustrations of reading technical manuals. I'm probably in need of
> "Informix for Dummies" but nonetheless: I've configured IDS, have set up
a
> database, and defined my first table. I'd like to import a flat ascii
text
> file but I am having problems getting it to import.
>
> The method that I am using is utilizing SQL with an "insert into" method.
> IDS gives it a try, but it appears that I'm having field delimiter
problems.
> The ascii file is a fixed record length, fixed field file, that is there
are
> no delimiters.
>
> What do I need to do to simply import a common file type such as this
> without having to take a two year course.
>
> Assistance is appreciated!
>
>
On Mon, 3 May 1999 18:11:30 -0500, "Actuary" <actuary@visi.com> wrote:
> As a newcomer to the Informix database, I'm experiencing the general
> frustrations of reading technical manuals. I'm probably in need of
> "Informix for Dummies" but nonetheless: I've configured IDS, have set up a
> database, and defined my first table. I'd like to import a flat ascii text
> file but I am having problems getting it to import.
>
> The method that I am using is utilizing SQL with an "insert into" method.
> IDS gives it a try, but it appears that I'm having field delimiter problems.
> The ascii file is a fixed record length, fixed field file, that is there are
> no delimiters.
>
> What do I need to do to simply import a common file type such as this
> without having to take a two year course.
>
> Assistance is appreciated!
>
Say for a table:
create table test(
c1 char(20),c2 integer);
and you want to load the fixed field file:
aaaaa1
bbbbb2
ccccc3
start dbload:
dbload -d <database> -c <text file> -l <error file>
and the <text file> looks like:
FILE "<data file> "
(f01 1-5,
f02 6-6);
INSERT INTO test
(c1,
c2)
VALUES (f01,
f02);
The FILE statement defines the fixed length field positions and the
INSERT maps the fields to your table columns.
HTH
---------------------------------------------------------
Steve Roach: Remove NOSPAM from address to reply:
steve_roach@NOSPAMibm.net
steve_roach@NOSPAMhotmail.com