How to import data in Excel file into Progress database table?

RealHeavyDude

Well-Known Member
You don't tell us much.

Do you want to store an Excel document as a BLOB in the database or do you want to parse the contents of it and store it in the database in some tables/fields?

What is your Progress/OpenEdge version?


RealHeavyDude.
 

hanhanmy

New Member
Sorry for not explain it clearly. Actually i want to parse the contents of excel and store it in the database in some tables/ fields. For example : I want to copy list of customer from excel and put into the Customer table in Progress.

The Progress & Open Edge i used as below:
Progress : Progress Version 10.1C
OpenEdge : Open Edge 10.1C

I very new to Progres. :(


You don't tell us much.

Do you want to store an Excel document as a BLOB in the database or do you want to parse the contents of it and store it in the database in some tables/fields?

What is your Progress/OpenEdge version?


RealHeavyDude.
 
You can try use odbc tools
create odbc source as excel file
create odbc target as progress DB.
use ETL tools to transfer data from one source to target.

To parse xls in progress you have to use some external library.
 

Cringer

ProgressTalk.com Moderator
Staff member
Alternatively if you save the spreadsheet as a csv with the columns in the same order as the columns in a temp-table you define you can just import the customers into a temp-table, validate them and then write them to the db.
 

hanhanmy

New Member
Alternatively if you save the spreadsheet as a csv with the columns in the same order as the columns in a temp-table you define you can just import the customers into a temp-table, validate them and then write them to the db.


Cringer, do you have sample progress script read the csv and import the data into temp-table into progress?
 

Stefan

Well-Known Member
This very basic example will import a csv file with two character columns into temp-table tt. Hopefully it's enough to get you started.

Code:
DEFINE TEMP-TABLE tt
   FIELD columnA AS CHARACTER
   FIELD columnB as CHARACTER
.
 
INPUT FROM "c:/blaat.csv":U.
REPEAT:
   CREATE tt.
   IMPORT DELIMITER ",":U tt.
END.
INPUT CLOSE.
 

hanhanmy

New Member
This very basic example will import a csv file with two character columns into temp-table tt. Hopefully it's enough to get you started.

Code:
DEFINE TEMP-TABLE tt
   FIELD columnA AS CHARACTER
   FIELD columnB as CHARACTER
.
 
INPUT FROM "c:/blaat.csv":U.
REPEAT:
   CREATE tt.
   IMPORT DELIMITER ",":U tt.
END.
INPUT CLOSE.


Hi, thanks for the help.
I try to import customer list from customer.csv to customer table.
I now already get the data in the csv, but how i'm going to copy it into my Customer table? Is it using BUFFER-COPY ttCustomer TO Customer. ??

DEFINE TEMP-TABLE ttCustomer
FIELD cCompany AS CHARACTER
FIELD cCustID as CHARACTER
FIELD cCustNum as INTEGER
FIELD cName as CHARACTER
.
INPUT FROM "C:\test\customerlist.csv":U.
REPEAT:
CREATE ttCustomer.
IMPORT DELIMITER ",":U ttCustomer.
message ttCustomer.cCompany + ", " + ttCustomer.cCustID + ", " + ttCustomer.cName view-as alert-box.
END.
INPUT CLOSE.
 

Stefan

Well-Known Member
Yes you can use buffer-copy if the field names are the same. First create the record:

Code:
CREATE customer.
BUFFER-COPY ttcustomer TO customer.

You can also assign the fields manually:

Code:
CREATE customer.
ASSIGN
   customer.company = ttcustomer.ccompany
   customer.custid = ttcustomer.ccustid
etc.
 

hanhanmy

New Member
Yes you can use buffer-copy if the field names are the same. First create the record:

Code:
CREATE customer.
BUFFER-COPY ttcustomer TO customer.

You can also assign the fields manually:

Code:
CREATE customer.
ASSIGN
   customer.company = ttcustomer.ccompany
   customer.custid = ttcustomer.ccustid
etc.


I tried assign it manually:
Code:
DEFINE TEMP-TABLE ttCustomer
FIELD cCompany AS CHARACTER
FIELD cCustID as CHARACTER
FIELD cCustNum as INTEGER
FIELD cName as CHARACTER
.
INPUT FROM "C:\test\customerlist.csv":U.
REPEAT:
CREATE ttCustomer.
IMPORT DELIMITER ",":U ttCustomer.
CREATE customer.
ASSIGN
   customer.company = ttCustomer.cCompany
   customer.custid = ttCustomer.cCustid.
/*message ttCustomer.cCompany + ", " + ttCustomer.cCustID + ", " + ttCustomer.cName view-as alert-box.*/
END.
INPUT CLOSE.

But i got error message: :confused:
**Customer already exists with Company "" Cust.ID"".(132)
 

Stefan

Well-Known Member
Please at least attempt some basic analysis. The message is informing you that you are trying to create a customer with an empty company and customer id.

1. What values are in the temp-table for company and customer id?
2. At what line of your CSV file are you?
3. What is at the line of your CSV file?
4. Do you maybe have extra empty lines at the end of your CSV file.

Totally unrelated to the error, but you should also DELETE the temp-table record arfter assigning its contents to the database record.
 

Cringer

ProgressTalk.com Moderator
Staff member
I always find with an import statement that you get one line at the end imported with blank details. So check that the details you are using aren't blank before you create a customer record in the db.
 

Rebin_Kurian

New Member
Iam new to progress4GL. I think this code will work for CSV import while we set field1 as primary index in table1 table.
Code:
DEFINE VARIABLE myRow AS CHAR EXTENT 5 NO-UNDO.
INPUT FROM "d:\datafile.csv".
REPEAT:
    IMPORT DELIMITER ','  myrow.
    FIND FIRST table1 WHERE table1.field1 = integer(myrow[1]) NO-ERROR.
    IF AVAIL table1 THEN
    DO:
     MESSAGE "field1 alerady exist" VIEW-AS ALERT-BOX.
    END.
    ELSE
    DO:    
    CREATE table1.
    ASSIGN
        Table1.Field1 = integer(myrow[1] )
        Table1.Field2  = integer(myrow[2] )
        Table1.Field3  = integer(myrow[3] )
        Table1.Field4  = integer(myrow[4] )
        Table1.Field5  = integer(myrow[5]) .
        MESSAGE "sucess" VIEW-AS ALERT-BOX.
        END.                    
END.

Please reply me if their is any problem in the code.
Thanks.
 
Last edited by a moderator:

Cringer

ProgressTalk.com Moderator
Staff member
Do you actually have a question, or did you just fancy adding this to an ancient post? If you have a question then please create a new thread.
 
Top