ABL Progress OpenEdge: Updating records in csv file with delimiter

Question

I'm working on the very first steps to be able te refresh a database environment.

First I export a table from environment 1 to a csv file called Test1.

OUTPUT TO VALUE("X:\Test\Test1.csv").

FOR EACH Test1 NO-LOCK:
    EXPORT DELIMITER ',' Test1.      
END.

OUTPUT CLOSE

then I import this file to environment 2 where it is unkown if all records in test1 are existing in environment 2. also unkown if the order is the same (this is where the below is limited in functioning)

INPUT FROM "X:\Test\Test1.csv".

FOR EACH Test12:
    IMPORT DELIMITER ',' Test1.
END.

So I need to be able to see if the existing records in environment 2 are present in the exported file Test1 from environment 1 and apply changes/updates by adjust these records in environment 2.

Unfortunately I cannot go more specific with names and date since it is confidential

I tried with the shown scripts to edit the exportfile Test1 in notepadd++ by removing a record which still worked well. howver, when i also removed the blank rule i left behind by removing the record, so everything moved one row upwards, it mentions that the entryID (first column) already exists. this means it exists in the file on another row ( which was the row below before i removed a record.).

Answer 1

I think your fundamental issue is that you should not be using FOR EACH in your import block.

What you probably want to be doing is to use a REPEAT block to import the data.

Something like:

REPEAT:
  CREATE tableName.
  IMPORT DELIMITER "," tableName.
END.

Possibly into a temp-table record, and to compare the imported record to the existing table and then decide whether or not to update the existing record if there are differences.

DEFINE TEMP-TABLE tt_tableName LIKE tableName.
CREATE tt_tableName.

REPEAT:
  IMPORT DELIMITER "," tt_tableName.
  FIND tableName EXCLUSIVE-LOCK WHERE tableName.id = tt_tableName.id NO-ERROR.  /* The "id" field is just something that I made up - use whatever the real table has for a UNIQUE key in the WHERE clause */
  IF AVAILABLE( tableName ) THEN
    DO:
      /* do whatever is appropriate to merge the data */
    END.
END.

Note: this method will not detect records that are missing in "test" and which should be deleted from "test12" unless your "whatever is appropriate" merge logic perhaps uses "magic" values in a field to indicate a record should be deleted.

If you are simply replacing the exiting "test12" data with "test" then you should first DELETE the "test12" data. For small tables you could do that reasonably with:

FOR EACH tableName exclusive-lock:
  DELETE tableName.
END.

For large tables that could get very painful. For those you would better off putting the large table into a dedicated storage area and using "proutil -C truncate area".

(The code above is all pseudo code, it has not been tested. It is just to outline the general approach.)