KB Article: Daily Duke Deactivation File Processing and Validation Procedure

1. File Delivery Details

• We receive the file mcdn_deact_2025XXXX.csv.z every day at 10:06 AM EST in the i2sms SFTP folder:
  /home/i2smsftp/DEACT
• Each day, we must download the previous day’s file to our local system for validating and processing.

---

2. File Extraction Instructions

You may extract the file using either of the following methods:

Windows

• Use WinZip or Express Zip to extract the .csv.z file.

Unix/Linux

1. Upload the .csv.z file to a Unix server.
2. Run the following command:
3. gzip -d mcdn_deact_2025XXXX.csv.z

After extraction, proceed to validate and filter the contents.

---

3. Data Validation and Filtering

Once the file is extracted:

1. Check the total number of deactivation records by filtering rows where:
   REASON_CODE = DEACTIVATE
2. We also receive a separate Duke Deactivation Phone Numbers Email, which contains a formatted file (compressed).
   • Extract the attached .ZIP folder from the email.
   • The extracted file name will follow the format:
     CELLDEACTIVATE2025XXXX.TXT (today’s date).

---

4. Cron Job Processing (Automated Daily Process)

A cron job runs every day around this time and performs the following:

1. Picks up the previous day’s file from i2sms SFTP.
2. Extracts the .z file.
3. Filters only the DEACTIVATE records.
4. Removes the leading ‘1’ from all MDNs.
5. Saves the results into a .TXT file.
6. Removes all carriage returns from each line.
7. Triggers the final TXT file for review via email at 5AM EST.

---

5. Validation Checklist (Must Complete Before Uploading)

Ensure the following:

✔ File Validations

• The file received via email at 5 AM EST is the processed file and must be uploaded to Duke SFTP.
• All MDNs with REASON_CODE = DEACTIVATE must match exactly with the previous day’s i2smsftp file.
• The total count of DEACTIVATE records must match between both files.
• All MDNs must correspond exactly.

✔ Formatting Validations

• The leading ‘1’ must be removed from all MDNs in the TXT file.
• There must be no carriage return characters in any line.
• The TXT file must be named using the format:
  CELLDEACTIVATEYYYYMMDD.TXT
  (Today’s date, even though the records are from yesterday's i2smsftp file.)
  Example: CELLDEACTIVATE20251127.TXT

---

6. Uploading the Final File to Duke SFTP

Once all checks are complete and validated:

• Upload the final TXT file to the Duke Energy SFTP site:
  https://sftp2.duke-energy.com/
• Upload location:
  Inbound Directory
  /Home/AWS_DEAC_PHN_PGuest/Inbound
• Upload Window:
  6 AM – 9 AM EST