import file contents into a dynamic temp-table

S

Serge

New Member
Hi,

I'm trying to do following:

I have a semi-colon .csv file e.g:

InvoiceNumber;InvoiceDate;CustomerNumber;CustomerName;
1;12/12/07;1245;"Customer1";
2;12/12/07;1458;"Customer2";

Like you see, the column-labels are also in the flat file.

Now I want to create a dynamic temp-table with the column-labels as fields (so row 1 should create the temp-table fields) and the rest has to be filled into the dynamic temp-table.

Once this temp-table is filled I must be able to display all records and put them into a DB Table.

I've got already following code, but it isn't doing anything:

DEF VAR ttTable AS HANDLE NO-UNDO.
DEF VAR bttTable AS HANDLE NO-UNDO.
DEF VAR qttTable AS HANDLE NO-UNDO.
DEF VAR iCounter AS INT NO-UNDO INIT 0.
DEF VAR i AS INT NO-UNDO.
DEF VAR cTextLine AS CHAR NO-UNDO.

INPUT FROM VALUE(cInputFile).
REPEAT:
/* If row = 0 then create dynamic temp-table and fields */
IF iCounter = 0 THEN
DO:
IMPORT DELIMITER ";" cTextLine.
CREATE TEMP-TABLE ttTable.
DO i = 1 TO NUM-ENTRIES(cTextLine):
ttTable:ADD-NEW-FIELD(TRIM(LC(cTextLine)), "char").
END.
END.
/* If row > 0 then fill up dynamic temp-table */
ELSE
DO:
IMPORT DELIMITER ";" cTextLine.
bttTable = ttTable:DEFAULT-BUFFER-HANDLE.
bttTable:BUFFER-CREATE.
DO i = 1 TO NUM-ENTRIES(cTextLine):
bttTable:BUFFER-FIELD(i):BUFFER-VALUE = cTextLine.
END.
END.
iCounter = iCounter + 1.
END.
INPUT CLOSE.
/* Here I want to display the temp-table */
qttTable:SET-BUFFERS(bttTable).
qttTable:QUERY-PREPARE("for each bttTable").
qttTable:QUERY-OPEN().
REPEAT:
IF qttTable:QUERY-OFF-END THEN LEAVE.
DO i = 1 TO bttTable:NUM-FIELDS:
DISPLAY bttTable:BUFFER-FIELD(i).
END.
END.

Please help!!?

Thanks in advance.
 
C

Casper

ProgressTalk.com Moderator
Staff member
There is lots of things you didn't do right:
  1. If you use delimiter then you have to import as many fields as there are in a line. So you only imported the first field of every line.
  2. You didn't do a temp-table prepare.
  3. You dind't create the query
  4. you can't do a for each bttTable because bTTTable isn't a known buffer name.
  5. You displays the handles and not the values.
I tried to make a working example hope it makes sense to you:

Code: 
DEFINE VARIABLE cReadField AS CHARACTER EXTENT 3 NO-UNDO.
DEFINE VARIABLE ttTable AS HANDLE      NO-UNDO.
DEFINE VARIABLE bttTable AS HANDLE      NO-UNDO.
DEFINE VARIABLE qttTable AS HANDLE      NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE      NO-UNDO.
DEFINE VARIABLE iNumFields AS INTEGER     NO-UNDO.
DEFINE VARIABLE iTmp AS INTEGER     NO-UNDO.
DEFINE VARIABLE iCounter AS INTEGER     NO-UNDO.
DEFINE VARIABLE cInputFile AS CHARACTER   NO-UNDO.
 
ASSIGN cInputFile = 'c:\temp\t.csv'
       iNumFields = 3. /* number of fields */
INPUT FROM VALUE(cInputFile).
REPEAT:
/* If row = 0 then create dynamic temp-table and fields */
    IF iCounter = 0 THEN
    DO:
        IMPORT DELIMITER ";" cReadField[1] cReadField[2] cReadField[3]. /* put here as much entries is needed to cover all the fields */
        CREATE TEMP-TABLE ttTable.
        DO iTmp = 1 TO iNumFields:
            ttTable:ADD-NEW-FIELD(TRIM(LC(cReadField[iTmp])), "char").  /* put here as much entries is needed to cover all the fields */
        END.
        ttTable:TEMP-TABLE-PREPARE("ResultSet").
        bttTable = ttTable:DEFAULT-BUFFER-HANDLE.
    END.
    /* If row > 0 then fill up dynamic temp-table */
    ELSE DO:
        IMPORT DELIMITER ";" cReadField[1] cReadField[2] cReadField[3].
        bttTable:BUFFER-CREATE().
        DO iTmp = 1 TO iNumFields:
            bttTable:BUFFER-FIELD(iTmp):BUFFER-VALUE = cReadField[iTmp].
        END.
    END.
    iCounter = iCounter + 1.
END.
INPUT CLOSE.
/* Here I want to display the temp-table */
CREATE QUERY qttTable.
qttTable:SET-BUFFERS(bttTable).
qttTable:QUERY-PREPARE("for each " + ttTable:NAME).
qttTable:QUERY-OPEN().
hBuffer = qttTable:GET-BUFFER-HANDLE(1).
qttTable:GET-FIRST().
DO WHILE NOT qttTable:QUERY-OFF-END:
    DO iTmp = 1 TO hBuffer:NUM-FIELDS:
        DISPLAY hBuffer:BUFFER-FIELD(iTmp):BUFFER-VALUE.
        PAUSE 1.
    END.
    qttTable:GET-NEXT().
END.
DELETE OBJECT qttTable.
DELETE OBJECT ttTable.

HTH,

Casper.
 
S

Serge

New Member
Thx for your quick help Casper, it works :rolleyes: but I've got still some issues:

1. How can I import a variable number of fields from a text file because the number of columns is not known?

2. And how can I check if the buffer-field name exists??

Please see highlighted text in my source below for more details ...


INPUT FROM VALUE(cInputFile). /* Start reading the input file */
REPEAT:
/* Create a dynamic temp-table based on the first row of the flat file as fields */
IF iCounter = 0 THEN
DO:
IMPORT DELIMITER ";" cReadField[1] cReadField[2] cReadField[3] cReadField[4]
cReadField[5] cReadField[6] cReadField[7] cReadField[8]
cReadField[9] cReadField[10] cReadField[11] cReadField[12]
cReadField[13] cReadField[14] cReadField[15] cReadField[16].
CREATE TEMP-TABLE ttTable.
/* DO i = 1 TO NUM-ENTRIES(cTextLine): */
DO iTmp = 1 TO iNumFields:
ttTable:ADD-NEW-FIELD(TRIM(LC(cReadField[iTmp])), "char").
END.
ttTable:TEMP-TABLE-PREPARE("ResultSet").
bttTable = ttTable:DEFAULT-BUFFER-HANDLE.
END.
ELSE
/* Insert rows from flat file into the dynamic temp-table */
DO:
IMPORT DELIMITER ";" cReadField[1] cReadField[2] cReadField[3] cReadField[4]
cReadField[5] cReadField[6] cReadField[7] cReadField[8]
cReadField[9] cReadField[10] cReadField[11] cReadField[12]
cReadField[13] cReadField[14] cReadField[15] cReadField[16].
bttTable:BUFFER-CREATE().
DO iTmp = 1 TO iNumFields:
bttTable:BUFFER-FIELD(iTmp):BUFFER-VALUE = cReadField[iTmp].
END.
END.
iCounter = iCounter + 1.
END.
INPUT CLOSE. /* End Reading the input file */
CREATE QUERY qttTable.
qttTable:SET-BUFFERS(bttTable).
qttTable:QUERY-PREPARE("for each " + ttTable:NAME).
qttTable:QUERY-OPEN().
hBuffer = qttTable:GET-BUFFER-HANDLE(1).
qttTable:GET-FIRST().
DO WHILE NOT qttTable:QUERY-OFF-END:
/* DO iTmp = 1 TO hBuffer:NUM-FIELDS: */
CREATE invoic.
ASSIGN
invnbr = hBuffer:BUFFER-FIELD("invoicenumber"):BUFFER-VALUE
invdat = hBuffer:BUFFER-FIELD("invoicedate"):BUFFER-VALUE
custnr = hBuffer:BUFFER-FIELD("customernumber"):BUFFER-VALUE
custnm = hBuffer:BUFFER-FIELD("customername"):BUFFER-VALUE
cntcod = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("country"):BUFFER-VALUE) THEN hBuffer:BUFFER-FIELD("country"):BUFFER-VALUE) ELSE ""
artnbr = hBuffer:BUFFER-FIELD("articlenumber"):BUFFER-VALUE
colco1 = hBuffer:BUFFER-FIELD("colorcode1"):BUFFER-VALUE
colsp1 = hBuffer:BUFFER-FIELD("colorspecification1"):BUFFER-VALUE
colco2 = hBuffer:BUFFER-FIELD("colorcode2"):BUFFER-VALUE
colsp2 = hBuffer:BUFFER-FIELD("colorspecification2"):BUFFER-VALUE
LENGTH = DEC(hBuffer:BUFFER-FIELD("length"):BUFFER-VALUE) / 100
nbrinv = INT(hBuffer:BUFFER-FIELD("numberinvoiced"):BUFFER-VALUE)
gprice = DEC(hBuffer:BUFFER-FIELD("grosssellingprice"):BUFFER-VALUE)
nprice = DEC(hBuffer:BUFFER-FIELD("nettsellingprice"):BUFFER-VALUE)
cprice = DEC(hBuffer:BUFFER-FIELD("costprice"):BUFFER-VALUE)
trtinv = hBuffer:BUFFER-FIELD("transitinvoice"):BUFFER-VALUE.
/* END. */
qttTable:GET-NEXT().
END.
DELETE OBJECT qttTable.
DELETE OBJECT ttTable.
 
R

RKR

Member
If you know the delimiter then you can also read 1 line at a time into a character variable.
Code: 
def var cLine as character no-undo.
def var iEntries as integer no-undo.
 
import unformatted cLine.
iEntries = num-entries(cLine,";").

then when you import the table you read the whole line and loop through it using do i = 1 to iEntries:
and read the individual items using entry(i,cLine,";").

Hope this will help you a little.

Ruud

Serge said:
Thx for your quick help Casper, it works :rolleyes: but I've got still some issues:

1. How can I import a variable number of fields from a text file because the number of columns is not known?

2. And how can I check if the buffer-field name exists??

Please see highlighted text in my source below for more details ...


INPUT FROM VALUE(cInputFile). /* Start reading the input file */
REPEAT:
/* Create a dynamic temp-table based on the first row of the flat file as fields */
IF iCounter = 0 THEN
DO:
IMPORT DELIMITER ";" cReadField[1] cReadField[2] cReadField[3] cReadField[4]
cReadField[5] cReadField[6] cReadField[7] cReadField[8]
cReadField[9] cReadField[10] cReadField[11] cReadField[12]
cReadField[13] cReadField[14] cReadField[15] cReadField[16].
CREATE TEMP-TABLE ttTable.
/* DO i = 1 TO NUM-ENTRIES(cTextLine): */
DO iTmp = 1 TO iNumFields:
ttTable:ADD-NEW-FIELD(TRIM(LC(cReadField[iTmp])), "char").
END.
ttTable:TEMP-TABLE-PREPARE("ResultSet").
bttTable = ttTable:DEFAULT-BUFFER-HANDLE.
END.
ELSE
/* Insert rows from flat file into the dynamic temp-table */
DO:
IMPORT DELIMITER ";" cReadField[1] cReadField[2] cReadField[3] cReadField[4]
cReadField[5] cReadField[6] cReadField[7] cReadField[8]
cReadField[9] cReadField[10] cReadField[11] cReadField[12]
cReadField[13] cReadField[14] cReadField[15] cReadField[16].
bttTable:BUFFER-CREATE().
DO iTmp = 1 TO iNumFields:
bttTable:BUFFER-FIELD(iTmp):BUFFER-VALUE = cReadField[iTmp].
END.
END.
iCounter = iCounter + 1.
END.
INPUT CLOSE. /* End Reading the input file */
CREATE QUERY qttTable.
qttTable:SET-BUFFERS(bttTable).
qttTable:QUERY-PREPARE("for each " + ttTable:NAME).
qttTable:QUERY-OPEN().
hBuffer = qttTable:GET-BUFFER-HANDLE(1).
qttTable:GET-FIRST().
DO WHILE NOT qttTable:QUERY-OFF-END:
/* DO iTmp = 1 TO hBuffer:NUM-FIELDS: */
CREATE invoic.
ASSIGN
invnbr = hBuffer:BUFFER-FIELD("invoicenumber"):BUFFER-VALUE
invdat = hBuffer:BUFFER-FIELD("invoicedate"):BUFFER-VALUE
custnr = hBuffer:BUFFER-FIELD("customernumber"):BUFFER-VALUE
custnm = hBuffer:BUFFER-FIELD("customername"):BUFFER-VALUE
cntcod = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("country"):BUFFER-VALUE) THEN hBuffer:BUFFER-FIELD("country"):BUFFER-VALUE) ELSE ""
artnbr = hBuffer:BUFFER-FIELD("articlenumber"):BUFFER-VALUE
colco1 = hBuffer:BUFFER-FIELD("colorcode1"):BUFFER-VALUE
colsp1 = hBuffer:BUFFER-FIELD("colorspecification1"):BUFFER-VALUE
colco2 = hBuffer:BUFFER-FIELD("colorcode2"):BUFFER-VALUE
colsp2 = hBuffer:BUFFER-FIELD("colorspecification2"):BUFFER-VALUE
LENGTH = DEC(hBuffer:BUFFER-FIELD("length"):BUFFER-VALUE) / 100
nbrinv = INT(hBuffer:BUFFER-FIELD("numberinvoiced"):BUFFER-VALUE)
gprice = DEC(hBuffer:BUFFER-FIELD("grosssellingprice"):BUFFER-VALUE)
nprice = DEC(hBuffer:BUFFER-FIELD("nettsellingprice"):BUFFER-VALUE)
cprice = DEC(hBuffer:BUFFER-FIELD("costprice"):BUFFER-VALUE)
trtinv = hBuffer:BUFFER-FIELD("transitinvoice"):BUFFER-VALUE.
/* END. */
qttTable:GET-NEXT().
END.
DELETE OBJECT qttTable.
DELETE OBJECT ttTable.
Click to expand...
 
S

Serge

New Member
Thx Ruud,

I've adapted your code and so this issue is done.:D

About the other issue how to check if a buffer-field exists, I've got following:

DO WHILE NOT qttTable:QUERY-OFF-END:
CREATE invoic.
ASSIGN
invnbr = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("invoicenumber")) THEN hBuffer:BUFFER-FIELD("invoicenumber"):BUFFER-VALUE ELSE ""
invdat = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("invoicedate")) THEN hBuffer:BUFFER-FIELD("invoicedate"):BUFFER-VALUE ELSE 12/12/9999
custnr = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("customernumber")) THEN hBuffer:BUFFER-FIELD("customernumber"):BUFFER-VALUE ELSE ""
custnm = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("customername")) THEN hBuffer:BUFFER-FIELD("customernumber"):BUFFER-VALUE ELSE ""
cntcod = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("country")) THEN hBuffer:BUFFER-FIELD("country"):BUFFER-VALUE ELSE IF VALID-HANDLE(hBuffer:BUFFER-FIELD("countrycode")) THEN hBuffer:BUFFER-FIELD("countrycode"):BUFFER-VALUE ELSE ""
artnbr = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("articlenumber")) THEN hBuffer:BUFFER-FIELD("articlenumber"):BUFFER-VALUE ELSE ""
colco1 = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("colorcode1")) THEN hBuffer:BUFFER-FIELD("colorcode1"):BUFFER-VALUE ELSE ""
colsp1 = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("colorspecification1")) THEN hBuffer:BUFFER-FIELD("colorspecification1"):BUFFER-VALUE ELSE ""
colco2 = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("colorcode2")) THEN hBuffer:BUFFER-FIELD("colorcode2"):BUFFER-VALUE ELSE ""
colsp2 = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("colorspecification2")) THEN hBuffer:BUFFER-FIELD("colorspecification2"):BUFFER-VALUE ELSE ""
LENGTH = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("length")) THEN DEC(hBuffer:BUFFER-FIELD("length"):BUFFER-VALUE) / 100 ELSE 0
nbrinv = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("numberinvoiced")) THEN INT(hBuffer:BUFFER-FIELD("numberinvoiced"):BUFFER-VALUE) ELSE ""
gprice = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("grosssellingprice")) THEN DEC(hBuffer:BUFFER-FIELD("grosssellingprice"):BUFFER-VALUE) ELSE 0
nprice = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("nettsellingprice")) THEN DEC(hBuffer:BUFFER-FIELD("nettsellingprice"):BUFFER-VALUE) ELSE 0
cprice = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("costprice")) THEN DEC(hBuffer:BUFFER-FIELD("costprice"):BUFFER-VALUE) ELSE 0
trtinv = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("transitinvoice")) THEN hBuffer:BUFFER-FIELD("transitinvoice"):BUFFER-VALUE ELSE "".
qttTable:GET-NEXT().
END.

This code is working and fills up my table with the correct values but it's exporting an error (BUFFER-FIELD country was not found in buffer ResultSet. (7351)) to my log file for every record if VALID-HANDLE = false.

Is there a way to suppress this error message?
 
R

RKR

Member
Serge said:
Thx Ruud,

I've adapted your code and so this issue is done.:D

About the other issue how to check if a buffer-field exists, I've got following:

DO WHILE NOT qttTable:QUERY-OFF-END:
CREATE invoic.
ASSIGN
invnbr = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("invoicenumber")) THEN hBuffer:BUFFER-FIELD("invoicenumber"):BUFFER-VALUE ELSE ""
invdat = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("invoicedate")) THEN hBuffer:BUFFER-FIELD("invoicedate"):BUFFER-VALUE ELSE 12/12/9999
custnr = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("customernumber")) THEN hBuffer:BUFFER-FIELD("customernumber"):BUFFER-VALUE ELSE ""
custnm = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("customername")) THEN hBuffer:BUFFER-FIELD("customernumber"):BUFFER-VALUE ELSE ""
cntcod = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("country")) THEN hBuffer:BUFFER-FIELD("country"):BUFFER-VALUE ELSE IF VALID-HANDLE(hBuffer:BUFFER-FIELD("countrycode")) THEN hBuffer:BUFFER-FIELD("countrycode"):BUFFER-VALUE ELSE ""
artnbr = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("articlenumber")) THEN hBuffer:BUFFER-FIELD("articlenumber"):BUFFER-VALUE ELSE ""
colco1 = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("colorcode1")) THEN hBuffer:BUFFER-FIELD("colorcode1"):BUFFER-VALUE ELSE ""
colsp1 = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("colorspecification1")) THEN hBuffer:BUFFER-FIELD("colorspecification1"):BUFFER-VALUE ELSE ""
colco2 = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("colorcode2")) THEN hBuffer:BUFFER-FIELD("colorcode2"):BUFFER-VALUE ELSE ""
colsp2 = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("colorspecification2")) THEN hBuffer:BUFFER-FIELD("colorspecification2"):BUFFER-VALUE ELSE ""
LENGTH = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("length")) THEN DEC(hBuffer:BUFFER-FIELD("length"):BUFFER-VALUE) / 100 ELSE 0
nbrinv = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("numberinvoiced")) THEN INT(hBuffer:BUFFER-FIELD("numberinvoiced"):BUFFER-VALUE) ELSE ""
gprice = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("grosssellingprice")) THEN DEC(hBuffer:BUFFER-FIELD("grosssellingprice"):BUFFER-VALUE) ELSE 0
nprice = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("nettsellingprice")) THEN DEC(hBuffer:BUFFER-FIELD("nettsellingprice"):BUFFER-VALUE) ELSE 0
cprice = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("costprice")) THEN DEC(hBuffer:BUFFER-FIELD("costprice"):BUFFER-VALUE) ELSE 0
trtinv = IF VALID-HANDLE(hBuffer:BUFFER-FIELD("transitinvoice")) THEN hBuffer:BUFFER-FIELD("transitinvoice"):BUFFER-VALUE ELSE "".
qttTable:GET-NEXT().
END.

This code is working and fills up my table with the correct values but it's exporting an error (BUFFER-FIELD country was not found in buffer ResultSet. (7351)) to my log file for every record if VALID-HANDLE = false.

Is there a way to suppress this error message?
Click to expand...

Hi Serge,

Try this:

Code: 
DEFINE VARIABLE hBufferField     AS HANDLE    N NO-UNDO.
 
hBufferField = hBuffer:BUFFER-FIELD("country") NO-ERROR.
IF NOT VALID-HANDLE(hBufferField)
THEN hBufferField = hBuffer:BUFFER-FIELD("countrycode") NO-ERROR.
 
cntCod = IF VALID-HANDLE(hBufferField) THEN hBufferField:BUFFER-VALUE ELSE "".

Greetings,
Ruud
 
You must log in or register to reply here.

Similar threads

P
Prodataset temp-tables empty after read-xml
Replies
5
Views
663
Cecil
Cecil
B
  • Locked
[Stackoverflow] [Progress OpenEdge ABL] OpenEdge ABL JSON to temp table: READ-JSON
Replies
0
Views
363
BobNoobGuy
B
L
How to solve looping problem
Replies
2
Views
473
lkeller
L
P
How to use font after creating it at runtime
Replies
5
Views
439
patelspam
P
M
Question how to display browser fields dynamically
Replies
2
Views
791
Mahmad rafi shaik
M
Top