Dynamic temp-table from a csv file?

[dayv2005]

I was wondering if anyone had any code samples of generating a dynamic temp-table from an imported csv file?

I was using a sample from the KB but i keep getting a buffer-field erorr and plus i don't know how to set it up when i am creating a dynamic temp table that is unlike any other table in the database.

This example uses the create-like and uses customer. Is there a way i can just create one that isn't like any other table.

DEFINE VARIABLE hDynamicTempTable  AS HANDLE NO-UNDO.
DEFINE VARIABLE hRecordBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hFieldBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE iConter AS INTEGER    NO-UNDO.
DEFINE VARIABLE cRecord AS CHARACTER  NO-UNDO.
DEFINE VARIABLE cDelimiter AS CHARACTER  NO-UNDO INITIAL ",".
DEFINE VARIABLE cTrimCharacter AS CHARACTER  NO-UNDO INITIAL " ".

ASSIGN
    cDelimiter     = CHR(44)   /* comma */
    cTrimCharacter = CHR(32).  /* Space */

CREATE TEMP-TABLE hDynamicTempTable.

hDynamicTempTable:CREATE-LIKE("customer").

hDynamicTempTable:TEMP-TABLE-PREPARE("hDynTTName").

hRecordBuffer = hDynamicTempTable:DEFAULT-BUFFER-HANDLE.

INPUT FROM Customer.csv.

OuterRepeat:
    REPEAT:
    IMPORT UNFORMATTED cRecord.
    DO TRANSACTION:
        hRecordBuffer:BUFFER-CREATE().
        REPEAT iConter = 1 TO NUM-ENTRIES(cRecord, cDelimiter) ON ERROR UNDO, LEAVE OuterRepeat:
            ASSIGN
                hFieldBuffer = hRecordBuffer:BUFFER-FIELD(iConter)
                hFieldBuffer:BUFFER-VALUE = TRIM(ENTRY(iConter, cRecord, cDelimiter), cTrimCharacter).
        END.
    END.
END.

 

[rusguy]

This code works with a csv file. But why do you need a temp-table to read a csv? Why not to read it into variables using a delimiter or something?

def var hTable as handle no-undo.
def var hBuffer as handle no-undo.
def var str as char no-undo.
def var i as integer no-undo.
def var hQuery as handle no-undo.
create temp-table hTable.
hTable:add-new-field("a", "character").
hTable:add-new-field("b", "character").
hTable:add-new-field("c", "character").
hTable:add-new-field("d", "character").
hTable:temp-table-prepare("newTable").
hBuffer = hTable:default-buffer-handle.
input from c:\temp\test.csv.
repeat:
  import unformatted str.
  hBuffer:buffer-create.
  do i = 1 to num-entries(str):
    hBuffer:buffer-field(i):buffer-value = entry(i, str).
  end.
end.
input close.
create query hQuery.
hQuery:set-buffers(hBuffer).
hQuery:query-prepare("for each " + hBuffer:name).
hQuery:query-open().
hQuery:get-first().
do while not hQuery:query-off-end with frame a:
  disp hBuffer:buffer-field(1):buffer-value.
  down.
  hQuery:get-next().
end.
hQuery:query-close().
delete object hQuery.
delete object hTable.

 

[dayv2005]

Thanks, i ended up hacking it out and the rough version of what i have now is this.

Still needs some tweaks and i will be using your code. Thanks

/* Define all the handles */
DEFINE VARIABLE bhtt         AS HANDLE   NO-UNDO.
DEFINE VARIABLE hFieldBuffer AS HANDLE   NO-UNDO.
DEFINE VARIABLE hfld1        AS HANDLE   NO-UNDO.
DEFINE VARIABLE hTempTable   AS HANDLE   NO-UNDO.
DEFINE VARIABLE qh           AS HANDLE   NO-UNDO.

DEFINE VARIABLE cDelChar  AS CHARACTER   NO-UNDO.
DEFINE VARIABLE crecord   AS CHARACTER   NO-UNDO.
DEFINE VARIABLE crecord2  AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cTrimChar AS CHARACTER   NO-UNDO.
DEFINE VARIABLE FILEloc   AS CHARACTER   NO-UNDO.
DEFINE VARIABLE lbltext   AS CHARACTER   NO-UNDO.

DEFINE VARIABLE iCounter AS INTEGER   NO-UNDO.

ASSIGN
    cDelChar     = CHR(44)
    cTrimChar = CHR(32)
    FILEloc = "n:\common\logs\rb_logs\rb_history_081309.csv".

/* Create a temp table from the temp table handle. */
CREATE TEMP-TABLE hTempTable.

/* Create all your fields here */
INPUT FROM value(fileloc).
REPEAT:
    IMPORT UNFORMATTED cRecord.
    DO TRANSACTION:        
        REPEAT iCounter = 1 TO NUM-ENTRIES(cRecord, cDelChar) ON ERROR UNDO:        

            lbltext = TRIM(ENTRY(iCounter, cRecord, cDelChar), cTrimChar).        
            
            hTempTable:ADD-NEW-FIELD(STRING(iCounter),'CHAR').
        END.
    END.
END.


/* prepare your temp table by naming it */
hTempTable:TEMP-TABLE-PREPARE('myDynTempTable').

/* capture a buffer handle to it. */
bhtt  = hTempTable:DEFAULT-BUFFER-HANDLE.

/* populate our temp table */
INPUT FROM VALUE(fileloc).
OuterRepeat:
REPEAT:
    IMPORT UNFORMATTED cRecord2.
    DO TRANSACTION:
        bhtt:BUFFER-CREATE().
        REPEAT iCounter = 1 TO NUM-ENTRIES(cRecord2, cDelChar) ON ERROR UNDO, LEAVE OuterRepeat:
            ASSIGN
                hFieldBuffer = bhtt:BUFFER-FIELD(iCounter)
                hFieldBuffer:BUFFER-VALUE = TRIM(ENTRY(iCounter, cRecord, cDelChar), cTrimChar).
        END.
    END.
END.


/* display our temp table */
CREATE QUERY qh.
qh:SET-BUFFERS(bhtt).
qh:QUERY-PREPARE("for each myDynTempTable").
qh:QUERY-OPEN.

REPEAT:
    qh:GET-NEXT.
    IF qh:QUERY-OFF-END THEN LEAVE.
    
    REPEAT iCounter = 1 TO bhtt:num-fields: /*qh num fields*/
        hfld1 = bhtt:BUFFER-FIELD(iCounter).
        DISP hfld1:BUFFER-VALUE FORMAT "X(70)".
    END.    
    
END.