excel import to progress database help

[ebuabeng]

hi

I'm new to progress
and i would be very grateful if you can give me directions as to how i can
import data from excel to progress database using
either the data administration tool or using procedure editor.
thanks.

 

[cecsno]

Hi,

Can you explain a little more about what you want to import to, is it a production database do you know what fields you want to populate and the order they appear in the Excel file?

hi

I'm new to progress
and i would be very grateful if you can give me directions as to how i can
import data from excel to progress database using
either the data administration tool or using procedure editor.
thanks.

 

[ebuabeng]

Re: Help

Hi,

Can you explain a little more about what you want to import to, is it a production database do you know what fields you want to populate and the order they appear in the Excel file?

Hi again

thanks for your reply.
I'm running a Human Resource and payroll integratate system
and i want to import data from excel to a progress database table called Ded_formula with fields :
employee#,ded_codes,Fixed_amount,Start_date,End_date

the fields appear in the same order as above ,in the excel file


hope to hear from you soon.

i have attached the sample excel file.
bye

 

[Simon Sweetman]

If you get your data in a format like this the progress data admin should be able to load it (Admin->Load data and Definitions->Table contents (.d file).

"D0870" "MMA" 568754.5 01/01/04 12/31/9999
"D1323" "MMA" 451764.01 01/01/04 12/31/9999
"D0864" "MMA" 555983 01/01/04 12/31/9999
"d0490" "MMA" 517690.75 01/01/04 12/31/9999
"D1051" "MMA" 374623.26 01/01/04 12/31/9999
"D1345" "MMA" 568754.5 01/01/04 12/31/9999
"D1337" "MMA" 414517.5 01/01/04 12/31/9999
"D1332" "MMA" 406526.75 01/01/04 12/31/9999
"1358" "MMA" 454945.76 01/01/04 01/31/04
"1357" "MMA" 564504.75 01/01/04 01/31/04
"D0010" "MMA" 573026.5 12/01/03 12/31/9999

It may ask some questions about code page, but just use accept the default.

Note the load will not replace anything that is already there so you many need to delete all the records in the table first - from procedure editor type:

for each <tablename>:
	delete <tablename>.
end.

or example with data range specified (assuming you have a field called startdate in your table and want to delete everything before 01/01/03).

for each <tablename> where <tablename>.startdate < 01/01/03:
	delete <tablename>.
end.

 

[ebuabeng]

RE: help

Hi
thanks for your reply,
My worry now is that, the table ( Ded_Formula ) contains other fields as well by which i can't afford to delete the table first before loading it again.
the results in the excel file was as a result of a select query: with this where clause:
where ded_codes = "MMA".


Is there a way i can load the excel file directly without overwriting the data in other fields that i dont need?


Will be whating for your reply

thanks
bye

If you get your data in a format like this the progress data admin should be able to load it (Admin->Load data and Definitions->Table contents (.d file).

"D0870" "MMA" 568754.5 01/01/04 12/31/9999
"D1323" "MMA" 451764.01 01/01/04 12/31/9999
"D0864" "MMA" 555983 01/01/04 12/31/9999
"d0490" "MMA" 517690.75 01/01/04 12/31/9999
"D1051" "MMA" 374623.26 01/01/04 12/31/9999
"D1345" "MMA" 568754.5 01/01/04 12/31/9999
"D1337" "MMA" 414517.5 01/01/04 12/31/9999
"D1332" "MMA" 406526.75 01/01/04 12/31/9999
"1358" "MMA" 454945.76 01/01/04 01/31/04
"1357" "MMA" 564504.75 01/01/04 01/31/04
"D0010" "MMA" 573026.5 12/01/03 12/31/9999

It may ask some questions about code page, but just use accept the default.

Note the load will not replace anything that is already there so you many need to delete all the records in the table first - from procedure editor type:

for each <tablename>:
	delete <tablename>.
end.

or example with data range specified (assuming you have a field called startdate in your table and want to delete everything before 01/01/03).

for each <tablename> where <tablename>.startdate < 01/01/03:
	delete <tablename>.
end.

 

[cecsno]

Save you spreadsheet as a csv file. Paste this in to the Progress editor and run.


DEFINE VARIABLE v-emp-no AS CHARACTER NO-UNDO.
DEFINE VARIABLE v-ded-code AS CHARACTER NO-UNDO.
DEFINE VARIABLE v-amt AS DECIMAL NO-UNDO.
DEFINE VARIABLE v-start AS DATE NO-UNDO.
DEFINE VARIABLE v-end AS DATE NO-UNDO.

INPUT FROM Ded_Formula.csv.

REPEAT :
IMPORT DELIMITER ","
v-emp-no
v-ded-code
v-amt
v-start
v-end.

FIND ded_formula WHERE employee# = v-emp-no NO-ERROR.

IF AVAILABLE ded_formula
THEN
ASSIGN
ded_codes = v-ded-code
fixed_amount = v-amt
START_date = v-start
END_date = v_end.
END.

 

[ebuabeng]

Thanks so much

Hi Friend,

Thanks so much for your help.The code you gave me did work by assiging values to the specified fields there by updating which ever records are there .but i will still need some modifications to it and would be happy if you could still help me in this regard.
instead of updating the already existing records, i want to create or add new records for each month( with Start_Date = first day of the month and End_Date = last day of the month) and load the contents of the excel file into it.





This is the code i used:


DEFINE VARIABLE v-emp-no AS CHARACTER NO-UNDO.
DEFINE VARIABLE v-ded-code AS CHARACTER NO-UNDO.
DEFINE VARIABLE v-amt AS DECIMAL NO-UNDO.
DEFINE VARIABLE v-start AS DATE FORMAT '99/99/9999' NO-UNDO.
DEFINE VARIABLE v-end AS DATE FORMAT '99/99/9999' NO-UNDO.

INPUT FROM c:\Ded_Formula.csv.

/*DISABLE TRIGGERS FOR LOAD OF Ded_Formula.*/
REPEAT :


IMPORT DELIMITER ","
v-emp-no
v-ded-code
v-amt
v-start
v-end.


FIND ded_formula WHERE ( Ded_codes = v-ded-code And employee#=v-emp-no) NO-ERROR.

IF AVAILABLE ded_formula
THEN
ASSIGN

Fixed_amount = v-amt
Start_Date = v-start
End_Date = v-end.


END.




Hope to hear from you again
bye

and thanks

 

[ebuabeng]

Hi Again

hi friends,

could you please , help me as to how i can add a new record to the tabe:
Ded_formula.


detail description of the table is as follows:

table name: Ded_formula
field names :employee#,start_date,end_date,ded_codes

thanks

..
I will welcome the syntax as well


hi friend,

could you please , help me as to how i can add a new record to the tabe:
Ded_formula.

thanks

 

[ebuabeng]

help

hi friends,

could you please , help me as to how i can add a new record to the tabe:
Ded_formula.


detail description of the table is as follows:

table name: Ded_formula
field names :employee#,start_date,end_date,ded_codes

thanks

..
I will welcome the syntax as well


hi friend,

could you please , help me as to how i can add a new record to the tabe:
Ded_formula.

thanks

 

[obio]

Hi,

these new records you want to create / add - could you explain what information you want them to hold? Give an example of what the record might store

hi friends,

could you please , help me as to how i can add a new record to the tabe:
Ded_formula.


detail description of the table is as follows:

table name: Ded_formula
field names :employee#,start_date,end_date,ded_codes

thanks

..
I will welcome the syntax as well


hi friend,

could you please , help me as to how i can add a new record to the tabe:
Ded_formula.

thanks

 

[tdi]

hi there.

I'm wondering... the excel datasheet is mantained by another person in HR? if so, if you're the programmer, you could come up with some clever solution, so you don't get bothered every time they need to use the data from the worksheet. Once I had some very like need, and the solution that i got was to make a little system to handle the capture and reporting of the same data... i don't have to say that the solution raised my credential so high, that i was able to date the girl who used to do the typing....
Another solution i can imagine is, what if you use excel trough DDE from the app? check http://www.4gl.fr/English/us1.htm and look for "automation.i"
Good look and tell us your experience..