Excel and TempTable

how to create a dynamic temp table.

Hi Sanjeev,

I found that one, but how do i create it from excel headers?

Greatz..

So this is how I do it.

I read the excel headers using the com object stuff - I'm in Windows so I can. I then go and look at the format of the next cell down and parse the format so I can work out a data type for it. I then create a column in my dynamic table that's named after the column header (with non-alphanumeric characters removed), with a datatype we just calculated. I then do this for the rest of the columns in the sheet until I come to a column with no header.

Define Variable FileNaam As Character No-undo.
Define Variable TabelNaam As Character No-undo.

FileNaam = "c:\tmp\bianca.csv".
TabelNaam = "ttbianca".

Define Variable ttHandle As Handle.
Define Variable bHandle As Handle.
Define Variable qHandle As Handle.
Define Variable fHandle As Handle.
Define Variable bTableHandle As Handle No-undo.

Define Variable ExcelHeaders As Character No-undo.
Define Variable ExcelData As Character No-undo.
Define Variable pColName As Character No-undo.
Define Variable pDbColName As Character No-undo.

Define Variable i As Integer No-undo.

Create Temp-table ttHandle.

Input From Value(FileNaam).

Import Unformatted ExcelHeaders.

Do i=1 To Num-entries(ExcelHeaders,";"):
    pColName = Entry(i, ExcelHeaders,";").
    If pColName Eq "" Then Next.
    /*Display "1 " pcolname Format "x(25)".*/
    pDbColName = TabelNaam + "." + pColName.
    /*Display "2 " pdbcolname Format "x(25)".  */
    ttHandle:Add-new-field(pColName,"Character").
End.

ttHandle:Temp-table-prepare("ttFile").

bHandle = ttHandle:Default-buffer-handle.

Repeat:
    Import UNFORMATTED ExcelData.
    bHandle:Buffer-create.

    Do i=1 To Num-entries(ExcelHeaders,";"):
        pColName = Entry(i, ExcelHeaders,";").
        If pColName Eq "" Then Next.
        fHandle = bHandle:Buffer-field(pColName).
        fHandle:Buffer-value = Entry(i,         ExcelData,";").
       /* Display pColname exceldata.*/
    End.
End.

Input Close.

Create Query qHandle.
qHandle:Set-buffers(bHandle).
qHandle:Query-prepare("FOR EACH ttFile").
qHandle:Query-open().

Create Buffer bTableHandle For Table TabelNaam.

Repeat Transaction:
    qHandle:Get-next().
    If qHandle:Query-off-end Then Leave.
    
    bTableHandle:Buffer-create().
    bTableHandle:Buffer-copy(bHandle).
    bTableHandle:Buffer-release().
End.

qHandle:Query-close().
bHandle:Buffer-release().
                                     
Delete Object bTableHandle.
Delete Object ttHandle.
Delete Object qHandle.


Can anyone tell me why this code wont do anything?

Is your data really separated by ';'?

You don't need to do this:

pDbColName = TabelNaam + "." + pColName.

The table name is defined when you temp-table-prepare. Just use pColName and see if that gets you closer. 

yup

unfortunately i get still this error

Could not create buffer object for table <table name>. (7334)

A buffer object could not be created for the table.  Perhaps the table referenced is no longer in scope or belongs to a database that has become disconnected.