Example - How to read xls from dx using odbc

[birchfld]

The below is an example of how you can use DB/C DX to read an Excel Spreadsheet ( .xls )
You can find the example .xls file attached

ColList    LIST
Col1        dim        80
Col2        form        9.9
Col3        dim        80
Col4        dim        80
    LISTEND
Message        dim        800
Dim70A        dim        70
Dim70B        dim        70
Dim70C        dim        70

connectstring    dim        1024

    pack    connectstring with "": 
                "DBQ=test1.xls;":
                "Driver={Microsoft Excel Driver (*.xls)};":
                "DriverId=790;":
                "FIL=excel 8.0;":
                "MaxBufferSize=2048;":
                "MaxScanRows=8;":
                "PageTimeout=5;":
                "ReadOnly=0;":
                "SafeTransactions=0;":
                "Threads=3;":
                "UID=admin;":
                "UserCommitSync=Yes;":
                ""
    sqlexec    ("CONNECT "+connectstring+"")                                                  
    sqlmsg    Message
     if    (size squeeze Message>0)
        UNPACK    Message TO Dim70A,Dim70B,Dim70C
        display    "[connect] ",Dim70A
        display    "[connect] ",Dim70B
        display    "[connect] ",Dim70C
        keyin    "-->",Dim70A
    else
        display    "[connect] ok"
    endif
    
    sqlexec  "SELECT column1,column2,column3,column4 USING INCURSOR FROM #"sheet1$#""
     sqlmsg    Message
     if    (size squeeze Message>0)
        UNPACK    Message TO Dim70A,Dim70B,Dim70C
        display    "[select] ",Dim70A
        display    "[select] ",Dim70B
        display    "[select] ",Dim70C
        keyin    "-->",Dim70A
    else
        display    "[select] ok"
    endif

    loop
         sqlexec    "FETCH NEXT USING INCURSOR" into ColList
     while    not over
        sqlmsg    Message
         if    (size squeeze Message>0)
            UNPACK    Message TO Dim70A,Dim70B,Dim70C
            display    "[fetch] ",Dim70A
            display    "[fetch] ",Dim70B
            display    "[fetch] ",Dim70C
            keyin    "-->",Dim70A
        else
            display    "[result] ",(CHOP Col1)," ",(Col2!9.9)," ",(CHOP Col3)," ",(CHOP Col4)
        endif
        
     repeat 

    sqlexec "DISCONNECT"
    sqlmsg    Message
     if    (size squeeze Message>0)
        display    "[disconn] ",Dim70A
        display    "[disconn] ",Dim70B
        display    "[disconn] ",Dim70C
    endif
    display    "[end]"
    keyin    "-->",Dim70A

    shutdown