Import Data into .txt file and some column need to be Unform

Posted by Admin on 24-Sep-2010 04:28

Hi,

We have progress programs which export data into .txt files.

Now, we encountered problems with this export as there are type of column which need to be unformatted. Theses .txt files are read by a SSIS (Integration services) as flat file.

We did the following code :

OUTPUT TO "\FILE\XTR\XTR_OPL_EPR_test_2409.txt" APPEND CONVERT TARGET "ISO8859-1".
FOR EACH EPR NO-LOCK:
DEFINE VARIABLE i    AS INTEGER  NO-UNDO.
EXPORT DELIMITER ";"
EPR.Profc
EPR.Dptc
EPR.Lieutrc
EPR.Ncpt.
PUT UNFORMATTED """". DO i = 1 TO 8 : PUT UNFORMATTED EPR.Ncdc[i] ";". END. PUT UNFORMATTED """;".
PUT UNFORMATTED """". DO i = 1 TO 8 : PUT UNFORMATTED EPR.Cdc-p[i] ";". END. PUT UNFORMATTED """;".
EPR.Nresp.
EPR.Tel-int.
PUT UNFORMATTED """". DO i = 1 TO 8 : PUT UNFORMATTED EPR.Ecpt[i] ";". END. PUT UNFORMATTED """;".
EPR.Reserv1.

We have to put a dot before and after the Unformatted to have the correct syntax to run it. But the result .txt file is not correct. The dot put the data of  column in a new line. we doesn't want this.

Is there anyway to avoid the dot ??

thanks

test.txt.zip

All Replies

Posted by davidkerkhofs3 on 24-Sep-2010 05:12

Hello,

the export statement actually generates the new line feed.

If you want your output on one line for each epr record do the following:

DEFINE VARIABLE i    AS INTEGER  NO-UNDO.

OUTPUT TO "\FILE\XTR\XTR_OPL_EPR_test_2409.txt" APPEND CONVERT TARGET "ISO8859-1".
FOR EACH EPR NO-LOCK:

  PUT UNFORMATTED
  EPR.Profc ";"
  EPR.Dptc ";"
  EPR.Lieutrc ";"
  EPR.Ncpt ";".
  PUT UNFORMATTED """".

  DO i = 1 TO 8 : PUT UNFORMATTED EPR.Ncdc[i] ";".

  END.

  PUT UNFORMATTED """;".
  PUT UNFORMATTED """".

  DO i = 1 TO 8 : PUT UNFORMATTED EPR.Cdc-p[i] ";".

  END.

  PUT UNFORMATTED """;".
  PUT UNFORMATTED EPR.Nresp ";"  EPR.Tel-int.
  PUT UNFORMATTED """".

  DO i = 1 TO 8 : PUT UNFORMATTED EPR.Ecpt[i] ";".

  END.

  PUT UNFORMATTED """;".
  PUT UNFORMATTED EPR.Reserv1.

  PUT SKIP. /* this will produce a new line feed for the next record  (or the end of file) */

END.

Posted by Admin on 24-Sep-2010 05:53

Hi,

Thanks for your answer.This works fine. But I have still a problem as  I  forgot to explain something..I have to add one Information in the text file at the start of each line. For Example "OPH"

The programm worked as the following :

OUTPUT TO "\\SFHVDWH03\FHV-DWH$\FILE\XTR\XTR_OPL_EPR_OPH.txt" APPEND CONVERT TARGET "ISO8859-1".
FOR EACH EPR NO-LOCK:
EXPORT DELIMITER ";"
"OPH"
EPR.Nsoc
EPR.Nemp
EPR.Annee
EPR.Per
EPR.Prof

Result

"OPH";"01";1"01;;;;;;;";"100;0;0;0;0;0;0;";";;;;;;;";

"OPH";"ES01";1"061;;;;;;;";"100;0;0;0;0;0;0;";"34001;;;;;;;";"

This is doesn't work anymore with the PUT UNFORMATTED at the beginning of the export.

Regards

Posted by davidkerkhofs3 on 24-Sep-2010 08:19

Add "OPH;" after the first PUT UNFORMATTED statement.

This thread is closed