Power shell - save sql query result to file

 1. Simple script to run query and save it to file

$fileName = 'FILE_PATH'

$SQLServer = "SERVER_NAME"  

$SQLDBName = "DATABASE_NAME"  

$userid ="USER_NAME"  

$password = "PASSWORD"   

$delimiter = ";"

$SqlQuery = "SELECT * FROM TABLE_NAME";

#SQL Query 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection  

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $userid; Password = $password;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand  

$SqlCmd.CommandText = $SqlQuery  

$SqlCmd.Connection = $SqlConnection  

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  

$SqlAdapter.SelectCommand = $SqlCmd   

#Dataset  and save to file

$DataSet = New-Object System.Data.DataSet  

$SqlAdapter.Fill($DataSet)  

$DataSet.Tables[0] | export-csv -Delimiter $delimiter -Path $fileName -NoTypeInformation

Powershell - upload file to ftp

 1. Simple script for uploading files to ftp

$username = "FTP_USER"

$password = "FTP_PASSWORD"

$localFile = "LOCAL_FILE_PATH"

$remoteFile = "ftp://SERVER_ADDRESS/" + "FILENAME"

# Create FTP Rquest

$request = [System.Net.FtpWebRequest]::Create("$remoteFile")

$request = [System.Net.FtpWebRequest]$request

$request.Method = [System.Net.WebRequestMethods+Ftp]::UploadFile

$request.Credentials = new-object System.Net.NetworkCredential($username, $password)

$request.UseBinary = $true

$request.UsePassive = $true

# Read the File

$fileContent = gc -en byte $localFile

$request.ContentLength = $fileContent.Length

$run = $request.GetRequestStream()

$run.Write($fileContent, 0, $fileContent.Length)

# Close and dispose connection

$run.Close()

$run.Dispose()

ABAP - Editing segment, field values for multiple idocs

1. Data declarations

* Document number

DATA: L_DOCNUM LIKE EDIDC-DOCNUM.

* Idoc data, status tables
DATA: itab_edidd LIKE TABLE OF EDIDD WITH HEADER LINE,
      itab_edidc LIKE TABLE OF EDIDC WITH HEADER LINE,
      itab_edids LIKE TABLE OF EDI_DS40 WITH HEADER LINE.

* Variables for field positioning 
DATA: P_SUM TYPE N LENGTH 4,
      I_POS type I,
      I_SUM type I,
      I_LEN type I.

2. Create select-option and parameters

tables EDIDC.

* Document number

select-options S_DOCNUM for EDIDC-DOCNUM
            OBLIGATORY NO INTERVALS .

* Name of the segment

parameters P_SNAME(100) type C
            OBLIGATORY .

* Value of the field
parameters P_SVALUE(999) type C
            OBLIGATORY .

* Position of the field in the segment
parameters P_POS(004) type N
            OBLIGATORY .

* Length of the field
parameters P_LEN(004) type N
            OBLIGATORY .

3. Program

data: s_sel like line of S_DOCNUM.

*   Loop over select options
loop at S_DOCNUM into s_sel.

  CLEAR L_DOCNUM.
  CLEAR itab_edidc.
  CLEAR itab_edidd.
  CLEAR itab_edidc.

*   Asssign doc nbr
  L_DOCNUM = s_sel-low.

  IF L_DOCNUM IS NOT  INITIAL.
  

  CALL FUNCTION 'EDI_DOCUMENT_OPEN_FOR_EDIT'
    EXPORTING
      DOCUMENT_NUMBER   = L_DOCNUM
*     ALREADY_OPEN      = 'N'
    IMPORTING
      IDOC_CONTROL      = itab_edidc
    TABLES
      IDOC_DATA         = itab_edidd
    EXCEPTIONS
     DOCUMENT_FOREIGN_LOCK               = 1
     DOCUMENT_NOT_EXIST                  = 2
     DOCUMENT_NOT_OPEN                   = 3
     STATUS_IS_UNABLE_FOR_CHANGING       = 4
     OTHERS                              = 5
            .
  IF SY-SUBRC <> 0.
      MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
          WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

    LOOP AT itab_edidd.
      IF itab_edidd-segnam = P_SNAME.
        CLEAR P_SUM.
          

*   Calculate position of field
        I_POS = P_POS.
        I_LEN = P_LEN.
        I_SUM = I_POS + I_LEN.
        P_SUM = I_SUM.

*   Changing field value
        CONCATENATE
        itab_edidd-SDATA(P_POS)
        P_SVALUE(P_LEN)
        itab_edidd-SDATA+P_SUM INTO itab_edidd-SDATA
        RESPECTING BLANKS.

*   Modify table with segment contents
        MODIFY itab_edidd.

        EXIT.
      ENDIF.
    ENDLOOP.

    CALL FUNCTION 'EDI_CHANGE_DATA_SEGMENTS'
      TABLES
        idoc_changed_data_range = itab_edidd
      EXCEPTIONS
        idoc_not_open           = 1
        data_record_not_exist   = 2
        OTHERS                  = 3.
    

    IF SY-SUBRC <> 0.
        MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
                WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.

   CALL FUNCTION 'EDI_CHANGE_CONTROL_RECORD'
      EXPORTING
        idoc_changed_control         = itab_edidc
      EXCEPTIONS
        idoc_not_open                = 1
        direction_change_not_allowed = 2
        OTHERS                       = 3.
   

    IF SY-SUBRC <> 0.
        MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
                WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.

*   Modify table with idoc status
      REFRESH itab_edids.
      itab_edids-docnum      = L_DOCNUM.
      itab_edids-status      = 51.
      itab_edids-repid       = sy-repid.
      itab_edids-mandt       = sy-mandt.
      itab_edids-tabnam      = 'EDI_DS'.
      itab_edids-stamqu      = 'SAP'.
      itab_edids-stamid      = 'B1'.
      itab_edids-stamno      = 999.
      itab_edids-stapa1      = ''.
      itab_edids-stapa2      = ''.
      itab_edids-logdat      = sy-datum.
      itab_edids-logtim      = sy-uzeit.
      APPEND itab_edids.

    CALL FUNCTION 'EDI_DOCUMENT_CLOSE_EDIT'
      EXPORTING
       DOCUMENT_NUMBER        = L_DOCNUM
       DO_COMMIT              = 'X'
       DO_UPDATE              = 'X'
       WRITE_ALL_STATUS       = 'X'
*    STATUS_75              = '30'
     TABLES
       STATUS_RECORDS         = itab_edids
    EXCEPTIONS
      IDOC_NOT_OPEN          = 1
      DB_ERROR               = 2
      OTHERS                 = 3.
    

    IF SY-SUBRC <> 0.
        MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
                WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.

  ENDIF.
  endloop.

4. How to use selection screen

Example of modification of storage location in MBGMCR03 idoc (segment E1BP2017_GM_ITEM_CREATE).

Document number: provide all the document number (same type)

Segment name: E1BP2017_GM_ITEM_CREATE

Position of field STGE_LOC: 22 (Calculate all the previous fields in the segment 18+4)

Length field STGE_LOC: 4

Field value: name of storage location