[56143] in SAPr3-news
Re: Flatfile datenextraktion in SAP R/3
daemon@ATHENA.MIT.EDU (georg.heiss@gmx.de)
Wed Nov 30 01:43:40 2005
To: sapr3-news@mit.edu
Date: 29 Nov 2005 22:43:29 -0800
From: "georg.heiss@gmx.de" <georg.heiss@gmx.de>
Message-ID: <1133333008.969224.203820@g14g2000cwa.googlegroups.com>
Hi, hier als Beispiel ein ABAP-Code der aus einer Tabelle ein csv-File
erzeugt:
REPORT PA0001_PA0001_1.
*----------------------------------------------------------------
*-- ABAP Program Generated by Oracle Warehouse Builder
*-- (c) Copyright 2002 Oracle Corporation. All rights reserverd.
*-- Author : owb_mdr_sis
*-- OWB Generator Version : 10g
*-- Generated for SAP Version:4.X
*-- Created Date : Wed Nov 30 07:40:35 CET 2005
*-- Modified Date : Wed Nov 30 07:40:35 CET 2005
*-- Created By : owb_mdr_sis
*-- Modified By : owb_mdr_sis
*-- Comments :
*-- Table Used as Sources : PA0001
*-- File Used as Targets : MAP_SAP_PA001.dat
*----------------------------------------------------------------
TABLES PA0001.
DATA: DATAFILENAME(128),CTRLFILENAME(128),LOGFILENAME(128),
ctLookup type i,
cfloat(25), cint1(3), cint2(5), cint4(10),
MSGCODE(255),MSGALL(255),
rejectedRecs type i, extractedRecs type i, fetchedRecs type i,
rejectedRecs_c(20) type c, extractedRecs_c(20) type c,
fetchedRecs_c(20) type c,
msg_err_text(255),CT(25) type c, MSG_TEXT(255),temp type n,
cmt1(100) TYPE C VALUE 'Oracle Warhouse Builder Release 10g',
cmt2(100) TYPE C VALUE 'SAP Integrator Code Generator of OWB',
cmt3(100) TYPE C VALUE '(c) Copyright 2002 Oracle Corporation.',
cmt4(100) TYPE C VALUE 'All rights reserved'.
DATA: BEGIN OF SRC,
PA0001MANDT LIKE PA0001-MANDT,
PA0001PERNR LIKE PA0001-PERNR,
PA0001SUBTY LIKE PA0001-SUBTY,
PA0001OBJPS LIKE PA0001-OBJPS,
PA0001SPRPS LIKE PA0001-SPRPS,
PA0001ENDDA LIKE PA0001-ENDDA,
PA0001BEGDA LIKE PA0001-BEGDA,
PA0001SEQNR LIKE PA0001-SEQNR,
PA0001AEDTM LIKE PA0001-AEDTM,
PA0001UNAME LIKE PA0001-UNAME,
PA0001HISTO LIKE PA0001-HISTO,
PA0001ITXEX LIKE PA0001-ITXEX,
PA0001REFEX LIKE PA0001-REFEX,
PA0001ORDEX LIKE PA0001-ORDEX,
PA0001ITBLD LIKE PA0001-ITBLD,
PA0001PREAS LIKE PA0001-PREAS,
PA0001FLAG1 LIKE PA0001-FLAG1,
PA0001FLAG2 LIKE PA0001-FLAG2,
PA0001FLAG3 LIKE PA0001-FLAG3,
PA0001FLAG4 LIKE PA0001-FLAG4,
PA0001RESE1 LIKE PA0001-RESE1,
PA0001RESE2 LIKE PA0001-RESE2,
PA0001GRPVL LIKE PA0001-GRPVL,
PA0001BUKRS LIKE PA0001-BUKRS,
PA0001WERKS LIKE PA0001-WERKS,
PA0001PERSG LIKE PA0001-PERSG,
PA0001PERSK LIKE PA0001-PERSK,
PA0001VDSK1 LIKE PA0001-VDSK1,
PA0001GSBER LIKE PA0001-GSBER,
PA0001BTRTL LIKE PA0001-BTRTL,
PA0001JUPER LIKE PA0001-JUPER,
PA0001ABKRS LIKE PA0001-ABKRS,
PA0001ANSVH LIKE PA0001-ANSVH,
PA0001KOSTL LIKE PA0001-KOSTL,
PA0001ORGEH LIKE PA0001-ORGEH,
PA0001PLANS LIKE PA0001-PLANS,
PA0001STELL LIKE PA0001-STELL,
PA0001MSTBR LIKE PA0001-MSTBR,
PA0001SACHA LIKE PA0001-SACHA,
PA0001SACHP LIKE PA0001-SACHP,
PA0001SACHZ LIKE PA0001-SACHZ,
PA0001SNAME LIKE PA0001-SNAME,
PA0001ENAME LIKE PA0001-ENAME,
PA0001OTYPE LIKE PA0001-OTYPE,
PA0001SBMOD LIKE PA0001-SBMOD,
PA0001KOKRS LIKE PA0001-KOKRS,
PA0001FISTL LIKE PA0001-FISTL,
PA0001GEBER LIKE PA0001-GEBER,
PA0001FKBER LIKE PA0001-FKBER,
PA0001GRANT_NBR LIKE PA0001-GRANT_NBR,
END OF SRC.
LOGFILENAME = 'C:\temp\PA001.log'.
OPEN DATASET LOGFILENAME FOR OUTPUT IN TEXT MODE MESSAGE MSG_TEXT.
IF SY-SUBRC ne 0.
WRITE: 'File C:\temp\PA001.log
can not be opened. Reason :', MSG_TEXT.
EXIT.
ENDIF.
DATAFILENAME = 'C:\temp\PA001.dat'.
OPEN DATASET DATAFILENAME FOR OUTPUT IN TEXT MODE MESSAGE MSG_TEXT.
IF SY-SUBRC ne 0.
WRITE: 'File C:\temp\PA001.dat
can not be opened. Reason :', MSG_TEXT.
EXIT.
ENDIF.
TRANSFER 'LOAD DATA' TO DATAFILENAME.
TRANSFER 'INFILE * ' TO DATAFILENAME.
TRANSFER 'APPEND CONCATENATE 100' to DATAFILENAME.
TRANSFER ' INTO TABLE PA0001_1' TO DATAFILENAME.
TRANSFER 'FIELDS TERMINATED BY "~" TRAILING NULLCOLS' TO DATAFILENAME.
TRANSFER '(MANDT' TO DATAFILENAME.
TRANSFER ',PERNR' TO DATAFILENAME.
TRANSFER ',SUBTY' TO DATAFILENAME.
TRANSFER ',OBJPS' TO DATAFILENAME.
TRANSFER ',SPRPS' TO DATAFILENAME.
TRANSFER ',ENDDA DATE ''yyyymmdd'' NULLIF ENDDA=''00000000''' TO
DATAFILENAME.
TRANSFER ',BEGDA DATE ''yyyymmdd'' NULLIF BEGDA=''00000000''' TO
DATAFILENAME.
TRANSFER ',SEQNR' TO DATAFILENAME.
TRANSFER ',AEDTM DATE ''yyyymmdd'' NULLIF AEDTM=''00000000''' TO
DATAFILENAME.
TRANSFER ',UNAME' TO DATAFILENAME.
TRANSFER ',HISTO' TO DATAFILENAME.
TRANSFER ',ITXEX' TO DATAFILENAME.
TRANSFER ',REFEX' TO DATAFILENAME.
TRANSFER ',ORDEX' TO DATAFILENAME.
TRANSFER ',ITBLD' TO DATAFILENAME.
TRANSFER ',PREAS' TO DATAFILENAME.
TRANSFER ',FLAG1' TO DATAFILENAME.
TRANSFER ',FLAG2' TO DATAFILENAME.
TRANSFER ',FLAG3' TO DATAFILENAME.
TRANSFER ',FLAG4' TO DATAFILENAME.
TRANSFER ',RESE1' TO DATAFILENAME.
TRANSFER ',RESE2' TO DATAFILENAME.
TRANSFER ',GRPVL' TO DATAFILENAME.
TRANSFER ',BUKRS' TO DATAFILENAME.
TRANSFER ',WERKS' TO DATAFILENAME.
TRANSFER ',PERSG' TO DATAFILENAME.
TRANSFER ',PERSK' TO DATAFILENAME.
TRANSFER ',VDSK1' TO DATAFILENAME.
TRANSFER ',GSBER' TO DATAFILENAME.
TRANSFER ',BTRTL' TO DATAFILENAME.
TRANSFER ',JUPER' TO DATAFILENAME.
TRANSFER ',ABKRS' TO DATAFILENAME.
TRANSFER ',ANSVH' TO DATAFILENAME.
TRANSFER ',KOSTL' TO DATAFILENAME.
TRANSFER ',ORGEH' TO DATAFILENAME.
TRANSFER ',PLANS' TO DATAFILENAME.
TRANSFER ',STELL' TO DATAFILENAME.
TRANSFER ',MSTBR' TO DATAFILENAME.
TRANSFER ',SACHA' TO DATAFILENAME.
TRANSFER ',SACHP' TO DATAFILENAME.
TRANSFER ',SACHZ' TO DATAFILENAME.
TRANSFER ',SNAME' TO DATAFILENAME.
TRANSFER ',ENAME' TO DATAFILENAME.
TRANSFER ',OTYPE' TO DATAFILENAME.
TRANSFER ',SBMOD' TO DATAFILENAME.
TRANSFER ',KOKRS' TO DATAFILENAME.
TRANSFER ',FISTL' TO DATAFILENAME.
TRANSFER ',GEBER' TO DATAFILENAME.
TRANSFER ',FKBER' TO DATAFILENAME.
TRANSFER ',GRANT_NBR' TO DATAFILENAME.
TRANSFER ')' TO DATAFILENAME.
TRANSFER 'BEGINDATA ' TO DATAFILENAME.
CLOSE DATASET DATAFILENAME.
fetchedRecs = 0.
rejectedRecs = 0.
extractedRecs = 0.
OPEN DATASET LOGFILENAME FOR APPENDING IN TEXT MODE MESSAGE MSG_TEXT.
IF SY-SUBRC ne 0.
WRITE: 'File C:\temp\PA001.log
can not be opened for appending. Reason :', MSG_TEXT.
EXIT.
ENDIF.
OPEN DATASET DATAFILENAME FOR APPENDING IN TEXT MODE MESSAGE MSG_TEXT.
IF SY-SUBRC ne 0.
WRITE: 'File C:\temp\PA001.dat
can not be opened for appending. Reason :', MSG_TEXT.
EXIT.
ENDIF.
TRANSFER cmt1 TO LOGFILENAME.
TRANSFER cmt2 TO LOGFILENAME.
TRANSFER cmt3 TO LOGFILENAME.
TRANSFER cmt4 TO LOGFILENAME.
CONCATENATE 'Map Name: '
'MAP_SAP_PA001' into msg_text.
TRANSFER msg_text TO LOGFILENAME.
CONCATENATE 'Source Table Name: '
'PA0001 ' into msg_text.
TRANSFER msg_text TO LOGFILENAME.
CONCATENATE 'Target Data File Name:'
'PA001.dat' into msg_text.
TRANSFER msg_text TO LOGFILENAME.
CONCATENATE 'Target Control File Name:'
'PA001.dat' into msg_text.
TRANSFER msg_text TO LOGFILENAME.
TRANSFER msg_text TO LOGFILENAME.
TRANSFER 'ABAP data extraction program runtime info.' TO LOGFILENAME.
DATA: OWB_TST LIKE TZONREF-TSTAMPS.
DATA: OWB_Z(20).
GET TIME STAMP FIELD OWB_TST.
WRITE OWB_TST TIME ZONE 'no tz ' DD/MM/YYYY TO OWB_Z.
CONCATENATE 'Began on Date: ' OWB_Z into msg_text.
TRANSFER msg_text TO LOGFILENAME.
SELECT
PA0001~MANDT
PA0001~PERNR
PA0001~SUBTY
PA0001~OBJPS
PA0001~SPRPS
PA0001~ENDDA
PA0001~BEGDA
PA0001~SEQNR
PA0001~AEDTM
PA0001~UNAME
PA0001~HISTO
PA0001~ITXEX
PA0001~REFEX
PA0001~ORDEX
PA0001~ITBLD
PA0001~PREAS
PA0001~FLAG1
PA0001~FLAG2
PA0001~FLAG3
PA0001~FLAG4
PA0001~RESE1
PA0001~RESE2
PA0001~GRPVL
PA0001~BUKRS
PA0001~WERKS
PA0001~PERSG
PA0001~PERSK
PA0001~VDSK1
PA0001~GSBER
PA0001~BTRTL
PA0001~JUPER
PA0001~ABKRS
PA0001~ANSVH
PA0001~KOSTL
PA0001~ORGEH
PA0001~PLANS
PA0001~STELL
PA0001~MSTBR
PA0001~SACHA
PA0001~SACHP
PA0001~SACHZ
PA0001~SNAME
PA0001~ENAME
PA0001~OTYPE
PA0001~SBMOD
PA0001~KOKRS
PA0001~FISTL
PA0001~GEBER
PA0001~FKBER
PA0001~GRANT_NBR
Into
(SRC-PA0001MANDT
,SRC-PA0001PERNR
,SRC-PA0001SUBTY
,SRC-PA0001OBJPS
,SRC-PA0001SPRPS
,SRC-PA0001ENDDA
,SRC-PA0001BEGDA
,SRC-PA0001SEQNR
,SRC-PA0001AEDTM
,SRC-PA0001UNAME
,SRC-PA0001HISTO
,SRC-PA0001ITXEX
,SRC-PA0001REFEX
,SRC-PA0001ORDEX
,SRC-PA0001ITBLD
,SRC-PA0001PREAS
,SRC-PA0001FLAG1
,SRC-PA0001FLAG2
,SRC-PA0001FLAG3
,SRC-PA0001FLAG4
,SRC-PA0001RESE1
,SRC-PA0001RESE2
,SRC-PA0001GRPVL
,SRC-PA0001BUKRS
,SRC-PA0001WERKS
,SRC-PA0001PERSG
,SRC-PA0001PERSK
,SRC-PA0001VDSK1
,SRC-PA0001GSBER
,SRC-PA0001BTRTL
,SRC-PA0001JUPER
,SRC-PA0001ABKRS
,SRC-PA0001ANSVH
,SRC-PA0001KOSTL
,SRC-PA0001ORGEH
,SRC-PA0001PLANS
,SRC-PA0001STELL
,SRC-PA0001MSTBR
,SRC-PA0001SACHA
,SRC-PA0001SACHP
,SRC-PA0001SACHZ
,SRC-PA0001SNAME
,SRC-PA0001ENAME
,SRC-PA0001OTYPE
,SRC-PA0001SBMOD
,SRC-PA0001KOKRS
,SRC-PA0001FISTL
,SRC-PA0001GEBER
,SRC-PA0001FKBER
,SRC-PA0001GRANT_NBR)
FROM PA0001 AS PA0001 .
ADD 1 to extractedRecs.
TRANSFER SRC-PA0001MANDT TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001PERNR TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001SUBTY TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001OBJPS TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001SPRPS TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001ENDDA TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001BEGDA TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001SEQNR TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001AEDTM TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001UNAME TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001HISTO TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001ITXEX TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001REFEX TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001ORDEX TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001ITBLD TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001PREAS TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001FLAG1 TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001FLAG2 TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001FLAG3 TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001FLAG4 TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001RESE1 TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001RESE2 TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001GRPVL TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001BUKRS TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001WERKS TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001PERSG TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001PERSK TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001VDSK1 TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001GSBER TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001BTRTL TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001JUPER TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001ABKRS TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001ANSVH TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001KOSTL TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001ORGEH TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001PLANS TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001STELL TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001MSTBR TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001SACHA TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001SACHP TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001SACHZ TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001SNAME TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001ENAME TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001OTYPE TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001SBMOD TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001KOKRS TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001FISTL TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001GEBER TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001FKBER TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
TRANSFER SRC-PA0001GRANT_NBR TO DATAFILENAME.
TRANSFER '~' TO DATAFILENAME.
ENDSELECT.
fetchedRecs = extractedRecs + rejectedRecs .
move fetchedRecs to fetchedRecs_c.
move extractedRecs to extractedRecs_c.
move rejectedRecs to rejectedRecs_c.
CONCATENATE fetchedRecs_c ' Rows fetched.' into msg_text.
TRANSFER msg_text TO LOGFILENAME.
CONCATENATE extractedRecs_c ' Rows extracted.' into msg_text.
TRANSFER msg_text TO LOGFILENAME.
CONCATENATE rejectedRecs_c ' Rows rejected.' into msg_text.
TRANSFER msg_text TO LOGFILENAME.
GET TIME STAMP FIELD OWB_TST.
WRITE OWB_TST TIME ZONE 'no tz ' DD/MM/YYYY TO OWB_Z.
CONCATENATE 'Ended on Date: ' OWB_Z into msg_text.
TRANSFER msg_text TO LOGFILENAME.
CLOSE DATASET LOGFILENAME.
CLOSE DATASET DATAFILENAME.
WRITE 'OWB data extraction to staging files has completed.'.
WRITE: / fetchedrecs_c, ' Rows fetched.'.
WRITE: / extractedrecs_c, ' Rows extracted.'.
WRITE: / rejectedrecs_c, ' Rows rejected.'.
