Programmer

J

Jeannie

New Member
On a daily basis, we would like to import data from an Excel Spreadsheet .
We are on Progress 10.1b on a Unix machine and I am using a Progress Open Edge 10 1.b driver. I am able to connect and create queries in Excel, but I don't know where to begin in importing data back to the Progress db. The ideal is to click on an excel button (macro) and the data would magically be sent to the Unix box and updated into Progress, minimal user involvment. Doable?
 
Hi

In unix I used csv files. I read the line and separate the fields.

In windows you can use some like this:

DEFINE VAR t_ren AS INT NO-UNDO.
DEFINE VAR valor AS CHAR NO-UNDO.
DEFINE VAR chExcelApplication AS COM-HANDLE.
DEFINE VAR chWorkbook AS COM-HANDLE.
DEFINE VAR chWorksheet AS COM-HANDLE.
DEFINE VAR chChart AS COM-HANDLE.
CREATE "Excel.Application" chExcelApplication.
chExcelApplication:Visible = TRUE.
chWorkbook = chExcelApplication:Workbooks:Add().
NO-RETURN-VALUE chExcelApplication:Workbooks:OpenText('C:\data\CARGA.XLS',2,1,2,1,0,1,0,1,0,0, "-4142").
chWorkSheet = chExcelApplication:Sheets:Item(1).

REPEAT:
t_ren = t_ren + 1.
valor = chWorkSheet:Range('A' + STRING(t_ren)):Value.
IF valor = ? THEN LEAVE.
CREATE carga.
Id_tabla = valor.

valor = chWorkSheet:Range('B' + STRING(t_ren)):Value.
IF valor = ? THEN valor = ''.
dato_1 = valor.

valor = chWorkSheet:Range('C' + STRING(t_ren)):Value.
IF valor = ? THEN valor = '0'.
dato_2 = INT(valor).
END.
RELEASE OBJECT chExcelApplication.
RELEASE OBJECT chWorkbook.
RELEASE OBJECT chWorksheet.


In both cases
1) I use a temp table to load records (because the users usually merge cells or leave blank rows or use titles or the values do not agree with the format)
2) You must check the consistency and integrity of the data obtained with the fields in your table.


 
thank you for your response, I guess what I really need to know is....
Using FTP or ODBC how can I launch a Progress program from an Excel macro.
 
Your explanation of what you really want, IMHO, does not make any sense. There is no way to start a program with FTP or ODBC. You can use FTP to access FTP shares to up- and download files and you may use ODBC to access a database for data retrieval or manipulation.

I suppose you want to connect to the database via ODBC from Excel. That's definately possible. You need the ODBC driver for it which comes with any OpenEdge Runtime product and you need to set up the SQL access on the database (which is also included with any OpenEdge database product).


Regards, RealHeavyDude.
 
You must log in or register to reply here.
Back
Top