Export data to Ms Excel

It’s often necessary to convert data stored in your database to another form, such as an Excel workbook. For example, you may wish to take advantage of Excel’s unique analytical capabilities or share data with someone unfamiliar with databases. Whatever the reason, the conversion process is pretty straightforward and quick.
To export data from your database to Excel, you must create a script file (*.cd).
In the file, you must have the query that will call the data from the tables you want and the commands to export the data to Excel.
Let’s take a few moments to walk through the process step-by-step.

1. Open the application.
2. Right-click and select “Developer.”
3. Select “Scripts” and then select “Create.”
4. In the window, you must write your code. The example below shows the code for exporting three columns, customer name, address, and phone, from the table customer.

proc exp_customer()

       start_sql “CUSTOMER” “KOSMOS”
         SELECT
         CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME,
         CUSTOMER.ADDRESS, CUSTOMER.PHONE
         FROM
         CUSTOMER
         ORDER BY
         CUSTOMER.LAST_NAME, CUSTOMER.FIRST_NAME
       end_sql

       call RunEmbSQL(“CUSTOMER”)

       q = QueryByName(“CUSTOMER”)
       rcount = TRecordCount(q)

       call DisconnectExcel() this command closes ms excel

       call OpenExcelDoc(“C:\CUSTOMER.XLS”) this command opens the excel file (we must create this file, the data always exports into an existing excel file)

       call SetExCell2(“A1”;” CUSTOMERS”;ftString). This command puts the value CUSTOMERS in A1 cell.

       line = 0

       call TFirst(q)
       for i = 1 to rcount

         name = strcat(CUSTOMER.LAST_NAME;” “;CUSTOMER.FIRST_NAME)

         line = line + 1

         call SetExCell1(line;2;name;ftString)
         call SetExCell1(line;3;DATA.ADDRESS;ftString)
         { this command puts the field address in C1 cell, the first parameter is the line,}
         { the second is the column, the third is the value you want to put in the cell, }
         { the fourth it the type of the value, ftString for strings and ftFloat for numbers }
         call SetExCell1(line;4;DATA.PHONE;ftString)

         call TNext(q)
       next

       call FreeEmbSQL(“CUSTOMER”)

       call ShowExcel() this command opens the excel and the file

end

5. Save changes and close the window.
6. Call the procedure.
(You can call the procedure from the menu or with a button.
The command you use is “call procedure name().”
For example “call exp_customer()“ )