How to read file in Informix-4gl 4

Hi,

There is no in-built fuctionality exists in 4GL to such activities. A workaround is to create a temporary table, load it with your data and select the data from it. A sample below tries to demonstrate this.

database testdb
main
define m_dat varchar(200)

create temp table t_data (dat varchar(255)) with no log
load from 'your.data' insert into t_data

declare cur1 cursor for select * from t_data

open cur1
while(1)
fetch cur1 into m_dat
if status=notfound then
exit while
end if
display m_dat
end while
end main

Regards,
Shriyan

Hi:

Shriyan is right about there being no built in method for reading files into 4GL. However, if you take a look at my "How can informix 4GL interact with Unix" FAQ over in the Informix Online forum:

faq179-2007

It includes a section on reading and writing ASCII files. It's all "C" functions, but you don't have to be a "C" programmer to use it.

Regards,

Ed

Thank you very much for Ed and Shriyan. Thanks for ur information and guidance. The method tat tought by Shriyan is working. It help me alots ~

Have a nice day!!

regards,
tzeyik

i had a similar problem and the method describe by vpshriyan works perfectly, but know i want to get that file i have loaded into the temporary table and insert it into a table in the database in a certain order, is that posible using informix 4gl?

Thanks for your time,

trdin

Hi:

Given Vpshriyan's temp table, t_data. If you have another database table created of the same type, say, new_data, you can do something like this:

insert into new_data
select * from t_data order by ....

To ensure that data is inserted into new_data in your required order, you'll have to have a clustered index on new_data.

Regards,


Ed

Ok, dind't quite worked for me, i think my explanation was short, here's what i need to do. I already loaded my file into the temp table and the data is stored in this pattern without spaces between lines:

Name
Description
Date
Name
Description
Date.....

and so on.

I need to know if i can read for example the first 3 lines or line by line and insert them into a table in the database in that order into fields for that, and so on with the rest of the file that is quite big. Is it posible in 4GL? Hope i explain myself?.

Thanks a lot!

trdin

Trdin:

Shriyan's 4GL code loads the temp table and simply displays the data to the screen. If you have another algorithm in mind, 4GL probably can handle it.

I'm not clear what you need, but you can read each row from the temp table, examine the data, and decide whether to insert into into another table.

Regards,

Ed

Trdin,

Like Ed, I'm also unable to decipher your requirement fully. However, if I understood bit of it, then I feel that you have column-wise data horizontally placed in your data file, and every 3 lines of information makes a Row for your base table. If so, here is a solution:

database testdb
main
define m_dat varchar(255), cntr smallint,
arr_cols array[3] of
record
mcol varchar(255)
end record

create temp table t_data (dat varchar(255)) with no log
load from 'your.data' insert into t_data

declare cur1 cursor for select * from t_data

let cntr=0
open cur1
while(1)
fetch cur1 into m_dat
if status=notfound then
exit while
end if
let cntr=cntr+1
let arr_cols[cntr].mcol=m_dat clipped

display m_dat

if cntr=3 then
-- send the full row into your database table
while (1)
insert into base_table values
(arr_cols[1].mcol,
arr_cols[2].mcol,
arr_cols[3].mcol)
if status=0 then
exit while
else
error &quot;<base_table> Insert &quot;, status sleep 1
end if
end while
for cntr=1 to 3
let arr_cols[cntr].mcol=&quot;&quot;
end for
let cntr=0
end if
end while
end main

Regards,
Shriyan

That's exactly what i needed!!! thanks a lot Shriyan, and to Ed too.

trdin

Ok, i have been reading my files without problems, but i just got this error when i tried to read some files: -846 Number of values in load file is not equal to number of columns. Reading the Informix Error Messages, i think is beacuse of empty lines in the files. How do i skip the empty lines without my program stopping and keep reading the file and inserting into the temp table?

Thanks guys

trdin.

trdin:

If you're using Shriyan's code similar to this:

while(1)
fetch cur1 into m_dat
if status=notfound then
exit while
end if
let cntr=cntr+1
let arr_cols[cntr].mcol=m_dat clipped

m_dat is probably null or equal spaces; do something like this:

while(1)
fetch cur1 into m_dat
if status=notfound then
exit while
end if
if m_dat is null or m_dat = &quot; &quot;
then
continue
end if

either that or filter your data before hand with unix tools:

sed /^$/d old.file > new.file

Regards,


Ed

let cntr=cntr+1
let arr_cols[cntr].mcol=m_dat clipped

Ed,
Since 3 rows of raw input data makes a single row for the base table, knocking off any blank data from the input file would result in havoc to already set logic of the conversion steps. Those blank lines need to be there as place holders.

trdin,
You need to pre-process the initial datafile. The following sed command will replace blank lines with pipe character, which would be ok for the SQL LOAD statement.

sed &quot;s/^$/|/&quot; input_file > output_file

Regards,
Shriyan
&quot;Vision is the art of seeing things invisible.&quot;

Ok, you guys were right i need to pre-process the file, so i ask this, i know is a longshot, is there any way i can execute the Unix sed command from my program in i4gl?. Before the load statement.

Thaks a lot,

trdin.

Try This,

main
define l_run char(100)
let l_run = 'sed &quot;s/^$/|/&quot; input_file > output_file'
run l_run
end main

Regards
Sojan

Hey Guys,

Ok, i want to print the displayed information that i read from the files...if you remember i display the info like this:

Name
Description
Date
Name
Description
Date.....

and so on.

I want to print that information displayed on screen, i have tried using the unix cat command to create a file that contains the info, but i have to manually select the info and paste it on the screen so it can be copied into the file. Is it posible to do it automatically, maybe defining where it starts and where it ends and do it from i4GL?

Thanks a lot guys!

trdin

Don't worry about it guys, i was able to figure it out using reports.

Thanx anyway.

trdin.