Muti-line records in textfile to temp table

[mja37]

this is the code I have that doesn't work...

create temp table t_data (dat varchar(255)) with no log

load from 'input.txt' insert into t_data

each rec. has 11 lines in the txt file that should appear in different columns, then a blank line, then another 11 line rec.

any help would be awesome.

 

[olded]

Hi:

You're problem is the column separater in Informix, DBDELIMITER. If it's not set, it's "|", pipe symbol, by default. Since the whole line is one column, you probably have to add a pipe symbol at the end of each line. (I've never had any luck changing DBDELIMITER to "\n".)

In my 4GL career, I got so tired of formatting data, that I wrote the "C" functions in:

faq179-2007

that allow me to talk to the OS. This includes being able to read and write text files from 4GL.

Concerning your question about two temp tables vs. one temp table and an array. System resources shouldn't make that much difference since its not that much data.

Regards,


Ed

 

[mja37]

Thanks Ed, I appreciate all the help. I am a web guy and this project has got me working OT.

 

[awopbamboo]

Hi,

I'm not sure your question makes sense, can you clarify something for me:

Yo have defined a table with one field, yet say that each line represents a seperate column. Column in what? your table only has one column. Do you mean that 11 lines make up one record?

So, your target table may be something like:

create temp table t_target (
line1 varchar(255),
line2 varchar(255),
line3 varchar(255),
line4 varchar(255),
line5 varchar(255),
line6 varchar(255),
line7 varchar(255),
line8 varchar(255),
line9 varchar(255),
line10 varchar(255),
line11 varchar(255)
) with no log

where each field is a seperate line in your file you are loading? After each 11th line, you create a new record?

if this is the case you could load the whole lot into your first table, then using a loop (stepping 11 each time) insert into your new table.

I've just written the following code that will do that, (its not brilliantly set out, but it does the job)

DEFINE
start_from, num_recs,loop, loop2 SMALLINT,
data ARRAY[11] of CHAR(255)


CREATE temp table t_data (dat varchar(255)) WITH no log

CREATE temp table t_target (
line1 varchar(255),
line2 varchar(255),
line3 varchar(255),
line4 varchar(255),
line5 varchar(255),
line6 varchar(255),
line7 varchar(255),
line8 varchar(255),
line9 varchar(255),
line10 varchar(255),
line11 varchar(255)
) WITH no log
LOAD FROM 'input.txt' INSERT INTO t_data

SELECT min(rowid) INTO start_from FROM t_data

SELECT COUNT(*)-1 INTO num_recs FROM t_data

FOR loop = 0 TO num_recs STEP 11

FOR loop2 = 1 TO 11
SELECT dat INTO data[loop2]
FROM t_data
WHERE rowid = loop+loop2-1+start_from

END FOR

INSERT INTO t_target values(data[1],
data[2],
data[3],
data[4],
data[5],
data[6],
data[7],
data[8],
data[9],
data[10],
data[11])
END FOR

..... etc now use t_target which will have all your individual records in....... etc



I know the above is untidy, (uses rowid etc), but it does the job there are much better of doing this, perhpas some people would mention some??

Ta

Jamie

 

[vpshriyan]

mja37:

The solution provided here looks relevant to your query.

How to read file in Informix-4gl
thread876-721667