Export data to Ms Excel
It’s often necessary to convert data stored in your database to another form, such as an Excel workbook. You may wish to take advantage of some of Excel’s unique analytical capabilities or simply share data with someone unfamiliar with databases. Whatever the reason, the conversion process is fairly 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 that will 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 (this file must be created, the data is always exported 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()" )