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

[hanhanmy]

Hi,

Can anyone assist me how to import data from excel file to Progress database table?
Thanks

 

[RealHeavyDude]

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]

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.

 

[MaximMonin]

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.

 

[jdpjamesp]

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]

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]

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.

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]

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.

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]

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

CREATE customer.
BUFFER-COPY ttcustomer TO customer.

You can also assign the fields manually:

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

 

[hanhanmy]

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

CREATE customer.
BUFFER-COPY ttcustomer TO customer.

You can also assign the fields manually:

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

I tried assign it manually:

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:
**Customer already exists with Company "" Cust.ID"".(132)

 

[Stefan]

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.

 

[jdpjamesp]

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.

 

[hanhanmy]

Thanks all for the help.
Finally i able to import the file into progress.

 

[Rebin_Kurian]

Iam new to progress4GL. I think this code will work for CSV import while we set field1 as primary index in table1 table.

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.

 

[jdpjamesp]

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.