Need to import a csv or txt file into a existing database table.

[spoye]

I am having trouble importing a file into a existing table. The table is iix and i am taking a item-code value substinging it and replacing the 1st number with a 1. I then export the stream to a csv file,which i thought i could import back into the table..I do not want to overwrite the old values just add the same line with the new Item-code number....I have never coded in progress. Could someone please help.

Below is a the code i have written to do it.


Define Variable item-codes LIKE iix.item-code.
Define Variable Item-desc-1s LIKE iix.Item-desc-1.
Define Variable Item-desc-2s LIKE iix.Item-desc-2.
Define Variable item-code-alts LIKE iix.Item-code-alt.
Define Variable class-codes LIKE iix.class-code.
Define Variable group-codes LIKE iix.Group-code.
Define Variable user-def-1s LIKE iix.User-def-1.
Define Variable user-def-4s LIKE iix.user-def-4.
Define Variable customers LIKE iix.customer.
Define Variable vendors LIKE iix.vendor.
Define Variable unit-tracks LIKE iix.unit-track.
Define Variable date-createds LIKE iix.date-created.
Define Variable date-modifieds LIKE iix.date-modified.
Define Variable lot-auto-rcvds LIKE iix.lot-auto-rcvd.
Define Variable user-def-2s LIKE iix.user-def-2.
Define Variable user-def-3s LIKE iix.User-def-3.
Define Variable modified-bys LIKE iix.Modified-by.
Define Variable item-units1s Like iix.item-unit[1].
DEFINE VARIABLE item-units2s LIKE iix.item-unit[2].
DEFINE VARIABLE item-units3s LIKE iix.item-unit[3].
DEFINE VARIABLE item-units4s LIKE iix.item-unit[4].
Define Variable unit-ratios1s LIKE iix.unit-ratio[1].
Define Variable unit-ratios2s LIKE iix.unit-ratio[2].
Define Variable unit-ratios3s LIKE iix.unit-ratio[3].
Define Variable unit-ratios4s LIKE iix.unit-ratio[4].
Define Variable cost-ctrs LIKE iix.cost-ctr.
Define Variable acct-nums LIKE iix.acct-num.
Define Variable branch-cost-flgs LIKE iix.branch-cost-flg.
Define Variable active-flgs LIKE iix.active-flg.
Define Variable vat-codes LIKE iix.vat-code.
Define Variable vat-flags LIKE iix.vat-flag.
Define Variable item-type-flags LIKE iix.item-type-flag.
Define Variable mmd-codes LIKE iix.mmd-code.
Define Variable customs-codes LIKE iix.customs-code.
Define Variable country-of-origins LIKE iix.country-of-origin.
Define Variable percent-contents LIKE iix.percent-content.
Define Variable end-use-codes LIKE iix.end-use-code.
Define Variable upc-codes LIKE iix.upc-code.
Define Variable notes-status-strs LIKE iix.notes-status-str.
Define Variable comments LIKE iix.comment.
Define Variable item-colors LIKE iix.item-color.

INPUT FROM v:\iixsptest.txt.
CREATE csv.
IMPORT DELIMITER "," Item-codes Item-desc-1s Item-desc-2s ITem-code-alts Class-codes Group-codes User-def-1s User-def-4s Customers Vendors Unit-tracks Date-createds Date-modifieds Lot-auto-rcvds User-def-2s User-def-3s Modified-bys Item-units1s Item-units2s Item-units3s Item-units4s Unit-ratios1s Unit-ratios2s Unit-ratios3s Unit-ratios4s Cost-ctrs Acct-nums Branch-cost-flgs Active-flgs vat-codes vat-flags Item-type-flags mmd-codes Customs-codes country-of-origins percent-contents end-use-codes upc-codes noTes-status-strs comments item-colors.
DISPLAY Item-codes Item-desc-1s Item-desc-2s ITem-code-alts Class-codes Group-codes User-def-1s User-def-4s Customers Vendors Unit-tracks Date-createds Date-modifieds Lot-auto-rcvds User-def-2s User-def-3s Modified-bys Item-units1s Item-units2s Item-units3s Item-units4s Unit-ratios1s Unit-ratios2s Unit-ratios3s Unit-ratios4s Cost-ctrs Acct-nums Branch-cost-flgs Active-flgs vat-codes vat-flags Item-type-flags mmd-codes Customs-codes country-of-origins percent-contents end-use-codes upc-codes noTes-status-strs comments item-colors.

INPUT CLOSE.

 

[spoye]

Seems that i have found the issue, when using substring it is truncating the item-code which is the reason i cannot import...at least this is what i believe, does anyone know how the overlay is used.

Thanks.

 

[MadMichael]

Exporting to a flat file and then importing is unnecessary. I would use the 'DEFINE BUFFER' and 'BUFFER-COPY' statements something like this:

DEFINE BUFFER iix-out for iix.
FOR EACH iix:
 BUFFER-COPY iix to iix-out 
   ASSIGN iix-out.item-code = "1" + 
          SUBSTRING(iix.itemcode,2).
END. /* FOR EACH iix: */

Check PROGRESS help for details on the BUFFER-COPY statement.

 

[spoye]

That didn't work for me it added a 1 to the front of every number, I need to replace the leading 0 with a 1.

Thanks though.

 

[spoye]

Hi Mike, I tried the buffer but I don't see how it will insert a new line with the specific item-codes into the table? When i ran a sample it looked, good but i then ran it against iix table and it only added to the 1 to the front of the item-code. I have changed that but i am still wondering if it will take the entire line? Sorry i am very new to this.

Thanks
sp

 

[MadMichael]

Do you have access to PROGRESS reference materials, either on-line or in hardcopy?

You could if you want add an explicit 'CREATE iix-out.' statement before the BUFFER-COPY, but PROGRESS help says it is not strictly necessary, here is a section from the BUFFER-COPY help:

At run time, BUFFER-COPY:

-	Creates a target record if none already exists and executes any applicable CREATE triggers

-	Assigns all matching fields that do not appear in the EXCEPT or ASSIGN options

-	Performs each assign-expression in the ASSIGN option, one-by-one

I am not clear on your problems with the SUBSTRING function, my example should do exactly what you want, i.e. concatenate a '1' with the current item-code starting at position 2.

The DEFINE BUFFER statement allows you to reference the same table by different names. My sample code reads the iix table with the FOR EACH statement and creates new records in the iix table using the name iix-out.

...And I just noticed my sample code has a typo, I had 'itemcode' for the field name instead of 'item-code' in one instance.

 

[MadMichael]

It would help if you could post your code here for me to look at.

 

[spoye]

here is my code, as you can see i have no idea what i am doing..this is the export. I need to manulipate a couple more fields i used the, i need to switch the unit1 with unit2 and i have to divide the unitratio by 1..the export I used is below...I have checked the table and it does look like what you gave inserted the values, but i didn't "change" the data i needed to.


DEF VAR itmcode AS CHAR FORMAT "X(2)".
DEF VAR itemcheck AS CHAR FORMAT "X(26)".
DEF VAR iixcode AS CHAR FORMAT "X(2)".
DEF VAR iixcheck AS CHAR FORMAT "X(26)".
DEF VAR iibcode AS CHAR FORMAT "X(2)".
DEF VAR iibcheck AS CHAR FORMAT "X(26)".
DEF VAR bmqcode AS CHAR FORMAT "X(2)".
DEF VAR bmqcheck AS CHAR FORMAT "X(26)".
DEF VAR iixuntrat2 AS DECIMAL FORMAT "zz,zzz,zz9.9999".

DEFINE STREAM iixsp.
OUTPUT STREAM iixsp TO v:iixsp.csv.



for each iix WHERE iix.item-code > "" NO-LOCK:
ASSIGN
iixcode = substring(iix.item-code,1,2).
iixcheck = iix.item-code.
iixuntrat2 = (1 / iix.unit-ratio[2]).
IF iixcode = "07" AND iix.active-flg > "" AND iix.item-unit[2] > "" THEN
EXPORT STREAM iixsp DELIMITER ","

REPLACE(iixcheck,'07','17')
iix.Item-desc-1
iix.Item-desc-2
iix.Item-code-alt
iix.Class-code
iix.Group-code
iix.User-def-1
iix.User-def-4
iix.Customer
iix.Vendor
iix.Unit-track
iix.Date-created
iix.Date-modified
iix.Lot-auto-rcvd
iix.User-def-2
iix.User-def-3
iix.Modified-by
iix.Item-unit[2]
iix.Item-unit[1]
iix.Item-unit[3]
iix.Item-unit[4]
iix.Unit-ratio[1]
iixuntrat2
iix.Unit-ratio[3]
iix.Unit-ratio[4]
iix.Cost-ctr
iix.Acct-num
iix.Branch-cost-flg
iix.Active-flg
iix.vat-code
iix.vat-flag
iix.Item-type-flag
iix.mmd-code
iix.Customs-code
iix.country-of-origin
iix.percent-content
iix.end-use-code
iix.upc-code
iix.noTes-status-str
iix.comment
iix.item-color.
END.
output close.




HERE Is the import i used...Much to do, I liked your way much better.


Define Variable iitem-codes LIKE iix.item-code.
Define Variable iItem-desc-1s LIKE iix.Item-desc-1.
Define Variable iItem-desc-2s LIKE iix.Item-desc-2.
Define Variable iitem-code-alts LIKE iix.Item-code-alt.
Define Variable iclass-codes LIKE iix.class-code.
Define Variable igroup-codes LIKE iix.Group-code.
Define Variable user-def-1s LIKE iix.User-def-1.
Define Variable user-def-4s LIKE iix.user-def-4.
Define Variable customers LIKE iix.customer.
Define Variable vendors LIKE iix.vendor.
Define Variable unit-tracks LIKE iix.unit-track.
Define Variable date-createds LIKE iix.date-created.
Define Variable date-modifieds LIKE iix.date-modified.
Define Variable lot-auto-rcvds LIKE iix.lot-auto-rcvd.
Define Variable user-def-2s LIKE iix.user-def-2.
Define Variable user-def-3s LIKE iix.User-def-3.
Define Variable modified-bys LIKE iix.Modified-by.
Define Variable item-units1s Like iix.item-unit[1].
DEFINE VARIABLE item-units2s LIKE iix.item-unit[2].
DEFINE VARIABLE item-units3s LIKE iix.item-unit[3].
DEFINE VARIABLE item-units4s LIKE iix.item-unit[4].
Define Variable unit-ratios1s LIKE iix.unit-ratio[1].
Define Variable unit-ratios2s LIKE iix.unit-ratio[2].
Define Variable unit-ratios3s LIKE iix.unit-ratio[3].
Define Variable unit-ratios4s LIKE iix.unit-ratio[4].
Define Variable cost-ctrs LIKE iix.cost-ctr.
Define Variable acct-nums LIKE iix.acct-num.
Define Variable branch-cost-flgs LIKE iix.branch-cost-flg.
Define Variable active-flgs LIKE iix.active-flg.
Define Variable vat-codes LIKE iix.vat-code.
Define Variable vat-flags LIKE iix.vat-flag.
Define Variable item-type-flags LIKE iix.item-type-flag.
Define Variable mmd-codes LIKE iix.mmd-code.
Define Variable customs-codes LIKE iix.customs-code.
Define Variable country-of-origins LIKE iix.country-of-origin.
Define Variable percent-contents LIKE iix.percent-content.
Define Variable end-use-codes LIKE iix.end-use-code.
Define Variable upc-codes LIKE iix.upc-code.
Define Variable notes-status-strs LIKE iix.notes-status-str.
Define Variable comments LIKE iix.comment.
Define Variable item-colors LIKE iix.item-color.

DEFINE STREAM iixsp.

INPUT FROM v:\iixsptest.txt.

REPEAT:


IMPORT DELIMITER "," iItem-codes iItem-desc-1s iItem-desc-2s iITem-code-alts iClass-codes iGroup-codes User-def-1s User-def-4s Customers Vendors Unit-tracks Date-createds Date-modifieds Lot-auto-rcvds User-def-2s User-def-3s Modified-bys Item-units1s Item-units2s Item-units3s Item-units4s Unit-ratios1s Unit-ratios2s Unit-ratios3s Unit-ratios4s Cost-ctrs Acct-nums Branch-cost-flgs Active-flgs vat-codes vat-flags Item-type-flags mmd-codes Customs-codes country-of-origins percent-contents end-use-codes upc-codes noTes-status-strs comments item-colors.
create iix.
ASSIGN
iix.item-code = iitem-codes
iix.Item-desc-1 = iItem-desc-1s
iix.Item-desc-2 = iItem-desc-2s
iix.Item-code-alt = iitem-code-alts
iix.class-code = iitem-code-alts
iix.Group-code = igroup-codes
iix.User-def-1 = user-def-1s
iix.user-def-4 = user-def-4s
iix.customer = customers
iix.vendor = vendors
iix.unit-track = unit-tracks
iix.date-created = date-createds
iix.date-modified = date-modifieds
iix.lot-auto-rcvd = lot-auto-rcvds
iix.user-def-2 = user-def-2s
iix.User-def-3 = user-def-3s
iix.Modified-by = modified-bys
iix.item-unit[1] = item-units1s
iix.item-unit[2] = item-units2s
iix.item-unit[3] = item-units3s
iix.item-unit[4] = item-units4s
iix.unit-ratio[1] = unit-ratios1s
iix.unit-ratio[2] = unit-ratios2s
iix.unit-ratio[3] = unit-ratios3s
iix.unit-ratio[4] = unit-ratios4s
iix.cost-ctr = cost-ctrs
iix.acct-num = acct-nums
iix.branch-cost-flg = branch-cost-flgs
iix.active-flg = active-flgs
iix.vat-code = vat-codes
iix.vat-flag = vat-flags
iix.item-type-flag = item-type-flags
iix.mmd-code = mmd-codes
iix.customs-code = customs-codes
iix.country-of-origin = country-of-origins
iix.percent-content = percent-contents
iix.end-use-code = end-use-codes
iix.upc-code = upc-codes
iix.notes-status-str = notes-status-strs
iix.comment = comments
iix.item-color = item-colors.

END.
INPUT CLOSE.