|
|
|
|
|
# 1. Create Dictonary with Fields needed for data import with their final names for data import
|
|
# 2. List of date fields - Date Formats
|
|
# 3. List of Float Fields - filling with Zeros
|
|
#
|
|
# 'DESTINATION' : 'Destination',
|
|
# 'BUYER NAME' : 'Buyer Name',
|
|
# 'BUYER COUNTRY' : 'Buyer Country',
|
|
# 'BUYER PAN' : 'Buyer Pan',
|
|
# 'BUYER IE CODE' : 'Buyer Ie Code',
|
|
# 'PLACE OF ORIGIN' : 'Place Of Origin',
|
|
# 'PREVIOUS PROCUREMENT [Y/N]' : 'Previous Procurement [Y/N]',
|
|
# 'PREVIOUS DTA SALES [Y/N]' : 'Previous Dta Sales [Y/N]',
|
|
# 'BUYER ADDRESS' : 'Buyer Address',
|
|
# 'BUYER ID TYPE' : 'Buyer Id Type',
|
|
# 'BUYER ID NUMBER' : 'Buyer Id Number',
|
|
# 'BDRFlag' : 'Bdrflag',
|
|
# 'ExtinguishmentRequestId' : 'Extinguishmentrequestid'
|
|
#
|
|
|
|
import sys
|
|
import os
|
|
import time
|
|
import pandas as pd
|
|
import numpy as np
|
|
import win32com.client
|
|
import pywintypes
|
|
|
|
|
|
if len(sys.argv) < 2:
|
|
print("Error: No file path provided.")
|
|
sys.exit(1)
|
|
|
|
# Get the file path from the system argument
|
|
file_path = sys.argv[1]
|
|
if not os.path.isfile(file_path):
|
|
print(f"Error: The file '{file_path}' does not exist.")
|
|
sys.exit(1)
|
|
|
|
print("Old File:")
|
|
print(file_path)
|
|
|
|
# Path to save the new .xlsx file
|
|
new_file_path = file_path+"x"
|
|
|
|
print("New File:")
|
|
print(new_file_path)
|
|
# Open the .xls file with Excel
|
|
|
|
# Start Excel
|
|
excel = win32com.client.Dispatch("Excel.Application")
|
|
time.sleep(5)
|
|
|
|
excel.Visible = False # Set to False to open in the background
|
|
time.sleep(5)
|
|
|
|
# Open the file
|
|
wb = excel.Workbooks.Open(file_path)
|
|
|
|
# Get the directory and file name (without extension)
|
|
directory, file_name = os.path.split(file_path)
|
|
file_name_without_ext = os.path.splitext(file_name)[0]
|
|
|
|
# Define the new file path with .xlsx extension
|
|
new_file_path = os.path.join(directory, f"{file_name_without_ext}.xlsx")
|
|
print(new_file_path)
|
|
# Save as a new Excel Workbook (xlsx format)
|
|
# Save as a new Excel Workbook (xlsx format)
|
|
retry_count = 5
|
|
for attempt in range(retry_count):
|
|
try:
|
|
time.sleep(5)
|
|
wb.SaveAs(new_file_path, FileFormat=51) # 51 = xlOpenXMLWorkbook (.xlsx)
|
|
break
|
|
except pywintypes.com_error as e:
|
|
print(f"Attempt {attempt + 1} failed: {e}")
|
|
if attempt < retry_count - 1:
|
|
time.sleep(5)
|
|
else:
|
|
print("Failed to save the workbook after multiple attempts.")
|
|
sys.exit(1)
|
|
|
|
|
|
# Close the workbook and quit Excel
|
|
time.sleep(5)
|
|
time.sleep(5)
|
|
|
|
wb.Close()
|
|
time.sleep(5)
|
|
|
|
time.sleep(5)
|
|
|
|
excel.Quit()
|
|
|
|
print(f"File saved as: {new_file_path}")
|
|
|
|
# Dictionary mapping old column names to new column names
|
|
column_rename_mapping = {
|
|
'REQUEST ID' : 'Request ID',
|
|
'SEZ ID' : 'SEZ ID',
|
|
'BOE TYPE':'Bill of Entry Type',
|
|
'AUTHORISED DEALER CODE': 'Authorised Dealer Code',
|
|
'AUTHORISED DEALER NAME' : 'Authorised Dealer Name',
|
|
'CLIENT ADDRESS' : 'Client Address(Joint Filing)',
|
|
'CLIENT COUNTRY' : 'Client Country(Joint Filing)',
|
|
'CLIENT GSTIN' : 'Client GSTIN(Joint Filing)',
|
|
'CLIENT IEC' : 'Client IEC(Joint Filing)',
|
|
'CLIENT TYPE' : 'Client Type',
|
|
'ENTITY NAME [IMPORTERS NAME]' : 'Entity Name [Importers Name]',
|
|
'ENTITY ID' : 'Entity ID',
|
|
'DESTINATION' : 'Destination',
|
|
'BUYER NAME' : 'Buyer Name',
|
|
'BUYER COUNTRY' : 'Buyer Country',
|
|
'BUYER PAN' : 'Buyer PAN',
|
|
'BUYER IE CODE' : 'Buyer IE Code',
|
|
'PLACE OF ORIGIN' : 'Place of Origin',
|
|
'PREVIOUS PROCUREMENT [Y/N]' : 'Previous Procurement [Y/N]',
|
|
'PREVIOUS DTA SALES [Y/N]' : 'Previous DTA Sales [Y/N]',
|
|
'BUYER ADDRESS' : 'Buyer Address',
|
|
'BUYER ID TYPE' : 'Buyer ID Type',
|
|
'BUYER ID NUMBER' : 'Buyer ID Number',
|
|
'BDRFlag' : 'BDR Flag (Items)',
|
|
'ExtinguishmentRequestId' : 'Extinguishment Request ID (Items)',
|
|
'HAWB / HBL DATE 1' : 'HAWB / HBL Date (Shipment Details)',
|
|
'HAWB / HBL DATE 2' : 'HAWB / HBL Date 2 (Shipment Details)',
|
|
'HAWB / HBL NO 1' : 'HAWB / HBL No. (Shipment Details)',
|
|
'HAWB / HBL NO 2' : 'HAWB / HBL No. 2 (Shipment Details)',
|
|
'ENTITY STATUS AS ON PREVIOUS DAY' : 'Entity Status as on Previous Day',
|
|
'MAWB / MBL DATE 1' : 'MAWB / MBL Date (Shipment Details)',
|
|
'MAWB / MBL DATE 2' : 'MAWB / MBL Date 2 (Shipment Details)',
|
|
'MAWB / MBL NO 1' : 'MAWB / MBL No. (Shipment Details)',
|
|
'MAWB / MBL NO 2' : 'MAWB / MBL No. 2 (Shipment Details)',
|
|
'IS OUTWARD REMITTANCE INVOLVED (Y/N)' : 'Outward Remittance Involved (Y/N)',
|
|
'PURPOSE OF TRANSACTION' : 'Purpose of Transaction',
|
|
'REQUEST ASSESSMENT DATE' : 'Request Assessment Date',
|
|
'REQUEST CANCELLATION DATE' : 'Request Cancellation Date',
|
|
'REQUEST OUT OF CHARGE DATE' : 'Request out of Charge Date',
|
|
'REQUEST STATUS' : 'Request Status',
|
|
'REQUEST SUBMISSION DATE' : 'Request Submission Date',
|
|
'SEZ NAME' : 'SEZ Name',
|
|
'THOKA / NOTING NUMBER' : 'Thoka / Bill of Entry Number',
|
|
'TOTAL NO OF PACKAGES 1' : 'Total Number of Packages (Shipment Details)',
|
|
'TOTAL NO OF PACKAGES 2' : 'Total Number of Packages 2 (Shipment Details)',
|
|
'UOM FOR GROSS WEIGHT 1' : 'UOM for Gross Weight (Shipment Details)',
|
|
'UOM FOR GROSS WEIGHT 2' : 'UOM for Gross Weight 2 (Shipment Details)',
|
|
'UOM FOR PACKAGES 1' : 'UOM for Packages (Shipment Details)',
|
|
'UOM FOR PACKAGES 2' : 'UOM for Packages 2 (Shipment Details)',
|
|
'GROSS WEIGHT 1' : 'Gross Weight (Shipment Details)',
|
|
'GROSS WEIGHT 2' : 'Gross Weight 2 (Shipment Details)',
|
|
'INVOICE NO' : 'Invoice No (Invoices)',
|
|
'INVOICE DATE' : 'Invoice Date (Invoices)',
|
|
'INVOICE TYPE' : 'Invoice Type (Invoices)',
|
|
'ITEM DESCRIPTION' : 'Item Description (Items)',
|
|
'AD UNDER SECTION 3(1) (ADE)' : 'AD under Section 3(1) (ADE) (Items)',
|
|
'ADDITIONAL DUTY OF EXCISE (GSI)' : 'Additional Duty of Excise (GSI) (Items)',
|
|
'ADDITIONAL DUTY OF EXCISE (NCCD OF EXCISE)' : 'Additional Duty of Excise (NCCD of Excise) (Items)',
|
|
'ASSESSABLE VALUE FOR THE ITEM IN INR' : 'Assessable Value for the Item In INR (Items)',
|
|
'BASIC CUSTOMS DUTY' : 'Basic Customs Duty (Items)',
|
|
'BCD TO BE CONSIDERED FOR CALCULATING IGST' : 'BCD to be Considered for Calculating IGST (Items)',
|
|
'CESS CUSTOMS' : 'Cess Customs (Items)',
|
|
'CESS EXCISE' : 'Cess Excise (Items)',
|
|
'CIF VALUE FOR THE ITEM IN INR' : 'CIF Value for the Item In INR (Items)',
|
|
'COMPENSATION CESS AMOUNT' : 'Compensation Cess Amount (Items)',
|
|
'Countervailing Duty (of Additional Customs Duty)' : 'Countervailing Duty (of Additional Customs Duty) (Items)',
|
|
'COUNTRY OF ORIGIN' : 'Country of Origin (Items)',
|
|
'CTH NO.' : 'CTH No. (Items)',
|
|
'CURRENCY' : 'Currency (Items)',
|
|
'DUTY FORGONE / PAID AMOUNT' : 'Duty Forgone / Paid Amount (Items)',
|
|
'DUTY FORGONE / PAID FLAG' : 'Duty Forgone / Paid Flag (Items)',
|
|
'EDUCATION CESS CUSTOMS' : 'Education Cess Customs (Items)',
|
|
'EDUCATION CESS EXCISE' : 'Education Cess Excise (Items)',
|
|
'EXCHANGE RATE' : 'Exchange Rate (Items)',
|
|
'EXIM SCHEME NAME / CODE' : 'Exim Scheme Name / Code (Items)',
|
|
'FREIGHT IN INR' : 'Freight In INR (Items)',
|
|
'HEALTH CESS CUSTOMS' : 'Health Cess Customs (Items)',
|
|
'HEALTH CESS EXCISE' : 'Health Cess Excise (Items)',
|
|
'IGST AMOUNT' : 'IGST Amount (Items)',
|
|
'ITEM TYPE' : 'Item Type (Items)',
|
|
'JOINT FILING / CLIENT DETAILS' : 'Joint Filing / Client Details',
|
|
'MODE OF TRANSPORT' : 'Mode of Transport (Items)',
|
|
'NCCD' : 'NCCD (Items)',
|
|
'OTHER ADDITIONAL CENTRAL EXCISE DUTY' : 'Other Additional Central Excise Duty (Items)',
|
|
'OTHER ADDITIONAL CUSTOMS DUTY' : 'Other Additional Customs Duty (Items)',
|
|
'PRODUCT VALUE (IN INVOICE CURRENCY)' : 'Product Value (In Invoice Currency) (Items)',
|
|
'PROVISIONAL REQUEST [Y/N]' : 'Provisional Request [Y/N] (Items)',
|
|
'QUANTITY' : 'Quantity (Items)',
|
|
'UNIT PRICE (IN INVOICE CURRENCY)' : 'Unit Price (In Invoice Currency) (Items)',
|
|
'INSURANCE IN INR' : 'Insurance In INR (Items)',
|
|
'OTHER CENTRAL EXCISE DUTIES' : 'Other Central Excise Duties (Items)',
|
|
'OTHER CUSTOMS DUTIES' : 'Other Customs Duties (Items)',
|
|
'ROAD AND INFRASTRUCTURE CESS ON CUSTOMS' : 'Road and Infrastructure Cess on Customs (Items)',
|
|
'SAD' : 'SAD (Items)',
|
|
'SAFEGUARD' : 'Safeguard (Items)',
|
|
'SAPTA BCD' : 'SAPTA BCD (Items)',
|
|
'SECTOR FINALIZED' : 'Sector Finalized (Items)',
|
|
'SHE CESS CUSTOMS' : 'SHE Cess Customs (Items)',
|
|
'SHE CESS EXCISE' : 'SHE Cess Excise (Items)',
|
|
'SOCIAL WELFARE SURCHARGE ON CUSTOMS' : 'Social Welfare Surcharge on Customs (Items)',
|
|
'SOCIAL WELFARE SURCHARGE ON GST' : 'Social Welfare Surcharge on GST (Items)',
|
|
'SP EXCISE DUTY (SCHEDULE II)' : 'SP Excise Duty (Schedule II) (Items)',
|
|
'SURCHARGE ON BCD' : 'Surcharge on BCD (Items)',
|
|
'ADDITIONAL DUTY OF EXCISE (TTA)' : 'Additional Duty of Excise (TTA) (Items)',
|
|
'ADDITIONAL DUTY OF EXCISE (TTW)' : 'Additional Duty of Excise (TTW) (Items)',
|
|
'AIDC on Customs' : 'AIDC on Customs (Items)',
|
|
'ANTI DUMPING' : 'Anti Dumping (Items)',
|
|
'AIDC on Excise' : 'AIDC on Excise (Items)'
|
|
}
|
|
|
|
columns_to_retrieve = list(column_rename_mapping.keys())
|
|
invoice_cols = [value for key, value in column_rename_mapping.items() if '(Invoices)' in value or value == 'Request ID']
|
|
item_col = [value for key, value in column_rename_mapping.items() if '(Items)' in value or value == 'Request ID']
|
|
shipment_cols = [value for key, value in column_rename_mapping.items() if '(Shipment Details)' in value or value == 'Request ID']
|
|
all_cols = list(column_rename_mapping.values())
|
|
header = set(all_cols) - set(invoice_cols) - set(item_col) - set(shipment_cols)
|
|
header_cols = list(header)
|
|
header_cols.append('Request ID')
|
|
item_col.append('Invoice No (Invoices)')
|
|
|
|
|
|
def parse_date(date_str):
|
|
if isinstance(date_str, pd.Timestamp):
|
|
# If date_str is already a datetime object, format it directly
|
|
return date_str.strftime('%Y-%m-%d')
|
|
|
|
# Convert to string if date_str is not already a string
|
|
date_str = str(date_str)
|
|
|
|
# Check the separator in the date string
|
|
if '-' in date_str:
|
|
# Format is yyyy-dd-mm or yyyy-mm-dd
|
|
try:
|
|
# Try parsing as yyyy-dd-mm
|
|
dt = pd.to_datetime(date_str, format='%Y-%d-%m %H:%M:%S')
|
|
return dt.strftime('%Y-%m-%d')
|
|
except ValueError:
|
|
try:
|
|
# Try parsing as yyyy-mm-dd if the previous format fails
|
|
dt = pd.to_datetime(date_str, format='%Y-%m-%d %H:%M:%S')
|
|
return dt.strftime('%Y-%m-%d')
|
|
except ValueError:
|
|
return None
|
|
elif '/' in date_str:
|
|
# Format is mm/dd/yyyy or mm/dd/yyyy hh:mm:ss AM/PM
|
|
try:
|
|
dt = pd.to_datetime(date_str, format='%m/%d/%Y %I:%M:%S %p')
|
|
return dt.strftime('%Y-%m-%d')
|
|
except ValueError:
|
|
try:
|
|
dt = pd.to_datetime(date_str, format='%m/%d/%Y')
|
|
return dt.strftime('%Y-%m-%d')
|
|
except ValueError:
|
|
return None
|
|
return None # Return None if format is unknown
|
|
|
|
|
|
|
|
columns_to_retrieve
|
|
|
|
|
|
#print("Columns Used:\n")
|
|
#print(columns_to_retrieve)
|
|
#pip install xlrd
|
|
|
|
print("Reading the new excel file...")
|
|
try:
|
|
try:
|
|
df_unfiltered = pd.read_excel(new_file_path, usecols=columns_to_retrieve, dtype='object', skiprows=2)
|
|
except FileNotFoundError:
|
|
print(f"Error: The file '{new_file_path}' does not exist.")
|
|
sys.exit(1)
|
|
print("File successfully read!")
|
|
except Exception as e:
|
|
print(f"Error reading the file: {e}")
|
|
sys.exit(1)
|
|
|
|
|
|
|
|
|
|
|
|
# 1. Total Rows
|
|
# 2. Total E+H Rows
|
|
# 3. Unique Header Data Rows
|
|
# 4. Unique Invoices
|
|
# 5. Unique Item Rows
|
|
# 6. Sum of Qty[df vs import df]
|
|
# 7. Sum of BCD [df vs import df]
|
|
# 8. Sum of Ass Value[df vs import df]
|
|
# 9. Sum of Total Duty[df vs import df]
|
|
# 10. No. of total rows [df vs import df] should be same.
|
|
|
|
|
|
df_unfiltered['REQUEST ID']
|
|
|
|
|
|
# Drop all rows where the column 'Client IEC(Joint Filing)' does not contain the value E+H IEC Code.
|
|
df = df_unfiltered[df_unfiltered['CLIENT IEC'] == 300014953]
|
|
|
|
|
|
|
|
df
|
|
|
|
|
|
print("Total Rows:")
|
|
print(len(df_unfiltered))
|
|
print("E+H(300014953) Records: ")
|
|
print(len(df))
|
|
# Rename columns using the dictionary
|
|
df = df.rename(columns=column_rename_mapping)
|
|
|
|
|
|
df['Authorised Dealer Code']
|
|
|
|
|
|
header = df[header_cols]
|
|
invoice = df[invoice_cols]
|
|
invoice.rename(columns={'Request ID': 'Request ID (Invoices)'}, inplace=True)
|
|
|
|
#df_invoices
|
|
item = df[item_col]
|
|
item.rename(columns={'Request ID': 'Request ID (Items)'}, inplace=True)
|
|
item.rename(columns={'Invoice No (Invoices)':'Invoice No (Items)'},inplace=True)
|
|
item = item.astype('object')
|
|
#df_items
|
|
shipment = df[shipment_cols]
|
|
shipment.rename(columns={'Request ID': 'Request ID (Shipment Details)'}, inplace=True)
|
|
|
|
|
|
|
|
shipment
|
|
|
|
|
|
header_cols
|
|
|
|
|
|
shipment
|
|
|
|
|
|
#shipment split and append for each request ID
|
|
#DROP DUPLICATE SHIPMENTS
|
|
uni_shipments = shipment.drop_duplicates()
|
|
|
|
|
|
|
|
uni_shipments
|
|
|
|
|
|
|
|
cols_first_shipment = [
|
|
'Request ID (Shipment Details)',
|
|
'HAWB / HBL Date (Shipment Details)',
|
|
'HAWB / HBL No. (Shipment Details)',
|
|
'MAWB / MBL Date (Shipment Details)',
|
|
'MAWB / MBL No. (Shipment Details)',
|
|
'Total Number of Packages (Shipment Details)',
|
|
'UOM for Gross Weight (Shipment Details)',
|
|
'UOM for Packages (Shipment Details)',
|
|
'Gross Weight (Shipment Details)']
|
|
|
|
cols_second_shipment = [
|
|
'Request ID (Shipment Details)',
|
|
'HAWB / HBL Date 2 (Shipment Details)',
|
|
'HAWB / HBL No. 2 (Shipment Details)',
|
|
'MAWB / MBL Date 2 (Shipment Details)',
|
|
'MAWB / MBL No. 2 (Shipment Details)',
|
|
'Total Number of Packages 2 (Shipment Details)',
|
|
'UOM for Gross Weight 2 (Shipment Details)',
|
|
'UOM for Packages 2 (Shipment Details)',
|
|
'Gross Weight 2 (Shipment Details)'
|
|
]
|
|
|
|
shipment_col_rename = {
|
|
'Request ID (Shipment Details)':'Request ID (Shipment Details)',
|
|
'HAWB / HBL Date 2 (Shipment Details)':'HAWB / HBL Date (Shipment Details)',
|
|
'HAWB / HBL No. 2 (Shipment Details)':'HAWB / HBL No. (Shipment Details)',
|
|
'MAWB / MBL Date 2 (Shipment Details)': 'MAWB / MBL Date (Shipment Details)',
|
|
'MAWB / MBL No. 2 (Shipment Details)':'MAWB / MBL No. (Shipment Details)',
|
|
'Total Number of Packages 2 (Shipment Details)':'Total Number of Packages (Shipment Details)',
|
|
'UOM for Gross Weight 2 (Shipment Details)' : 'UOM for Gross Weight (Shipment Details)',
|
|
'UOM for Packages 2 (Shipment Details)':'UOM for Packages (Shipment Details)',
|
|
'Gross Weight 2 (Shipment Details)':'Gross Weight (Shipment Details)'
|
|
|
|
}
|
|
|
|
|
|
first_shipment = uni_shipments[cols_first_shipment]
|
|
second_shipment = uni_shipments[cols_second_shipment]
|
|
second_shipment = second_shipment.rename(columns=shipment_col_rename)
|
|
|
|
shipment_consolidated = pd.concat([first_shipment,second_shipment], ignore_index=False,)
|
|
shipment_consolidated.drop_duplicates(inplace=True)
|
|
shipment_consolidated.dropna(subset=['Total Number of Packages (Shipment Details)'], inplace=True)
|
|
|
|
|
|
|
|
|
|
len(shipment_consolidated)
|
|
|
|
|
|
first_shipment
|
|
|
|
|
|
second_shipment
|
|
|
|
|
|
shipment_consolidated
|
|
|
|
|
|
header
|
|
|
|
|
|
uni_header = header.drop_duplicates()
|
|
|
|
|
|
|
|
uni_header
|
|
|
|
|
|
uni_header = uni_header.astype('object')
|
|
|
|
|
|
|
|
len(uni_header)
|
|
|
|
|
|
uni_inv = invoice.drop_duplicates()
|
|
uni_inv = uni_inv.astype('object')
|
|
shipment_consolidated = shipment_consolidated.drop_duplicates()
|
|
shipment_consolidated = shipment_consolidated.astype('object')
|
|
|
|
|
|
|
|
len(uni_header)
|
|
|
|
|
|
shipment_consolidated
|
|
|
|
|
|
#shipment_consolidated
|
|
|
|
|
|
#header[header["Request ID"]==172400992002].to_csv("header_drop_duplicates.csv")
|
|
#shipments_for_header
|
|
|
|
|
|
#shipments_for_header = shipment_consolidated[shipment_consolidated["Request ID (Shipment Details)"]==172400964514]
|
|
#shipments_for_header
|
|
|
|
|
|
import_df = pd.DataFrame()
|
|
for i in uni_header["Request ID"]:
|
|
print("Request ID:")
|
|
print(i)
|
|
this_header = uni_header[uni_header["Request ID"]==i].reset_index(drop=True)
|
|
inv_for_header = uni_inv[uni_inv["Request ID (Invoices)"]==i].reset_index(drop=True)
|
|
item_for_header = item[item["Request ID (Items)"]==i].reset_index(drop=True)
|
|
shipments_for_header = shipment_consolidated[shipment_consolidated["Request ID (Shipment Details)"]==i].reset_index()
|
|
#print("Invoices:\n")
|
|
#print(inv_for_header)
|
|
#print("item:\n")
|
|
print(shipments_for_header)
|
|
df_concatenated = pd.concat([this_header,shipments_for_header,inv_for_header, item_for_header], axis=1).astype('object')
|
|
import_df = pd.concat([import_df,df_concatenated], ignore_index=True)
|
|
if(len(inv_for_header)>1 and len(item_for_header)>1):
|
|
print("Test Point")
|
|
continue
|
|
|
|
|
|
#
|
|
|
|
|
|
datatypes = import_df.dtypes
|
|
|
|
|
|
dtypes = import_df.dtypes
|
|
dtypes
|
|
|
|
|
|
# Functions for Date format conversion and Zero Fillings
|
|
|
|
|
|
import_df
|
|
|
|
|
|
#Function to convert date format
|
|
def convert_to_date_format(df, fields):
|
|
for field in fields:
|
|
try:
|
|
df[field] = pd.to_datetime(df[field], errors='raise').dt.strftime('%Y-%m-%d') #here's the date format
|
|
except Exception as e:
|
|
print(f"Error converting field {field}: {e}")
|
|
raise
|
|
|
|
# Function to fill NaN, null, blank, or empty entries with zero
|
|
def fill_empty_with_zero(df, fields):
|
|
for field in fields:
|
|
# Replace empty strings with zero
|
|
df[field] = df[field].replace('', 0)
|
|
# Replace None with zero
|
|
df[field] = df[field].replace(None, 0)
|
|
# Replace NaN with zero
|
|
df[field] = df[field].replace(np.nan, 0)
|
|
|
|
def replace_empty_with_zero(value):
|
|
if pd.isnull(value) or value == '':
|
|
return 0
|
|
return value
|
|
|
|
|
|
date_fields = [
|
|
"HAWB / HBL Date (Shipment Details)",
|
|
"MAWB / MBL Date (Shipment Details)",
|
|
"Request Assessment Date",
|
|
"Request Cancellation Date",
|
|
"Invoice Date (Invoices)",
|
|
"Request out of Charge Date",
|
|
"Request Submission Date",
|
|
]
|
|
|
|
import_df['Old Request out of Charge Date'] = import_df['Request out of Charge Date']
|
|
import_df['Request out of Charge Date'] = import_df['Request out of Charge Date'].apply(parse_date)
|
|
|
|
convert_to_date_format(import_df, date_fields)
|
|
|
|
fields_to_fill = [
|
|
'AD under Section 3(1) (ADE) (Items)',
|
|
'Additional Duty of Excise (GSI) (Items)',
|
|
'Additional Duty of Excise (NCCD of Excise) (Items)',
|
|
'Assessable Value for the Item In INR (Items)',
|
|
'Basic Customs Duty (Items)',
|
|
'BCD to be Considered for Calculating IGST (Items)',
|
|
'Cess Customs (Items)',
|
|
'Cess Excise (Items)',
|
|
'CIF Value for the Item In INR (Items)',
|
|
'Compensation Cess Amount (Items)',
|
|
'Countervailing Duty (of Additional Customs Duty) (Items)',
|
|
'Duty Forgone / Paid Amount (Items)',
|
|
'Duty Forgone / Paid Flag (Items)',
|
|
'Education Cess Customs (Items)',
|
|
'Education Cess Excise (Items)',
|
|
'Freight In INR (Items)',
|
|
'Health Cess Customs (Items)',
|
|
'Health Cess Excise (Items)',
|
|
'IGST Amount (Items)',
|
|
'NCCD (Items)',
|
|
'Other Additional Central Excise Duty (Items)',
|
|
'Other Additional Customs Duty (Items)',
|
|
'Insurance In INR (Items)',
|
|
'Other Central Excise Duties (Items)',
|
|
'Other Customs Duties (Items)',
|
|
'Road and Infrastructure Cess on Customs (Items)',
|
|
'SAD (Items)',
|
|
'Safeguard (Items)',
|
|
'SAPTA BCD (Items)',
|
|
'SHE Cess Customs (Items)',
|
|
'SHE Cess Excise (Items)',
|
|
'Social Welfare Surcharge on Customs (Items)',
|
|
'Social Welfare Surcharge on GST (Items)',
|
|
'SP Excise Duty (Schedule II) (Items)',
|
|
'Surcharge on BCD (Items)',
|
|
'Additional Duty of Excise (TTA) (Items)',
|
|
'Additional Duty of Excise (TTW) (Items)',
|
|
'AIDC on Customs (Items)',
|
|
'Anti Dumping (Items)',
|
|
'AIDC on Excise (Items)'
|
|
]
|
|
|
|
# Apply the custom function to the specified fields
|
|
for field in fields_to_fill:
|
|
import_df[field]=import_df[field].apply(replace_empty_with_zero)
|
|
|
|
|
|
|
|
|
|
test_dates = import_df[['Old Request out of Charge Date', 'Request out of Charge Date']]
|
|
|
|
|
|
test_dates.dropna().drop_duplicates()
|
|
|
|
|
|
|
|
import_df
|
|
|
|
|
|
#new_col_dtypes = import_df.dtypes.rename("dtype")
|
|
#old_col_dtypes = df_concatenated.dtypes.rename("dtype")
|
|
#compare = pd.merge(new_col_dtypes, old_col_dtypes, left_index=True, right_index=True, how='inner')
|
|
#excel_dtypes = df_unfiltered.dtypes
|
|
|
|
|
|
#excel_dtypes
|
|
|
|
|
|
# 1. Total Rows
|
|
# 2. Total E+H Rows
|
|
# 3. Unique Header Data Rows
|
|
# 4. Unique Invoices
|
|
# 5. Unique Item Rows
|
|
# 6. Sum of Qty[df vs import df]
|
|
# 7. Sum of BCD [df vs import df]
|
|
# 8. Sum of Ass Value[df vs import df]
|
|
# 9. Sum of Total Duty[df vs import df]
|
|
# 10. No. of total rows [df vs import df] should be same.
|
|
|
|
print("Excel Rows:")
|
|
print(len(df_unfiltered))
|
|
|
|
print("E+H Rows:")
|
|
print(len(df))
|
|
|
|
status = True
|
|
#Header QC
|
|
print("Unique Header Rows:")
|
|
count_header_uni = len(uni_header)
|
|
print(count_header_uni)
|
|
|
|
print("Unique Output Header Count:")
|
|
count_output_header = import_df["Request ID"].count()
|
|
print(count_output_header)
|
|
|
|
if(count_header_uni==count_output_header):
|
|
print("Header Count Checked")
|
|
else:
|
|
status = False
|
|
|
|
#Invoice QC
|
|
print("Unique Invoice Rows:")
|
|
count_invoice_uni = len(uni_inv)
|
|
print(count_invoice_uni)
|
|
|
|
print("Output Invoice No. Count:")
|
|
count_invoice_header = import_df["Invoice No (Invoices)"].count()
|
|
print(count_invoice_header)
|
|
|
|
if(count_invoice_uni==count_invoice_header):
|
|
print("Invoice Count Checked")
|
|
else:
|
|
status = False
|
|
|
|
|
|
#Item QC
|
|
print("Item Rows:")
|
|
count_item_uni = len(item)
|
|
print(count_item_uni)
|
|
|
|
print("Output Rows:")
|
|
count_output_rows = len(import_df)
|
|
print(count_output_rows)
|
|
|
|
if(count_item_uni==count_output_rows):
|
|
print("Total No. of Rows Checked")
|
|
else:
|
|
status = False
|
|
|
|
#Shipment QC
|
|
print("Total Shipment Rows:")
|
|
count_shipments = shipment_consolidated['Total Number of Packages (Shipment Details)'].count()
|
|
print(count_shipments)
|
|
|
|
print("Total Shipments in Output:")
|
|
count_output_shipments = import_df['Total Number of Packages (Shipment Details)'].count()
|
|
print(count_output_shipments)
|
|
|
|
|
|
if(count_shipments==count_output_shipments):
|
|
print("Total No. of Shipments Same")
|
|
else:
|
|
status = False
|
|
|
|
#Sum Checks
|
|
|
|
sum_qty_input = float(df["Quantity (Items)"].sum())
|
|
sum_qty_output = float(import_df["Quantity (Items)"].sum())
|
|
|
|
|
|
sum_ass_val_input = float(df["Assessable Value for the Item In INR (Items)"].sum())
|
|
sum_ass_val_output = float(import_df["Assessable Value for the Item In INR (Items)"].sum())
|
|
print(f'Ass Value : {sum_ass_val_input} vs {sum_ass_val_output}')
|
|
sum_bcd_input = float(df["Basic Customs Duty (Items)"].sum())
|
|
sum_bcd_output = float(import_df["Basic Customs Duty (Items)"].sum())
|
|
|
|
if (sum_qty_input==sum_qty_output):
|
|
print("INPUT-OUTPUT QUANTITY matched!")
|
|
if(sum_bcd_input==sum_bcd_output):
|
|
print("INPUT-OUTPUT BCD matched!")
|
|
if(sum_ass_val_input==sum_ass_val_output):
|
|
|
|
print(f"INPUT ass-OUTPUT ASS matched!")
|
|
else:
|
|
print("INPUT-OUTPUT Assessable Value(Items) does not match!")
|
|
print(sum_ass_val_input)
|
|
print(sum_ass_val_output)
|
|
status=False
|
|
else:
|
|
print(f"INPUT-OUTPUT BCD {sum_bcd_input}-{sum_bcd_output} does not match!")
|
|
status=False
|
|
else:
|
|
print("INPUT-OUTPUT Quantity (Items) does not match!")
|
|
status=False
|
|
|
|
|
|
|
|
import_df
|
|
|
|
|
|
# item subtext extraction - In Script
|
|
#
|
|
|
|
|
|
# Extract value between "^" characters
|
|
import_df['Item Subtext (Items)'] = import_df['Item Description (Items)'].str.extract(r'\^([^^]*)\^', expand=False)
|
|
|
|
# Check if extraction worked and fill with empty string if not
|
|
import_df['Item Subtext (Items)'].fillna('', inplace=True)
|
|
|
|
# Split the 'Item Subtext (DTA Sale Items)' column into two new columns safely
|
|
subtext_split = import_df['Item Subtext (Items)'].str.split('/', expand=True)
|
|
|
|
# Add the split columns to the DataFrame
|
|
import_df['Purchase Order Number (Items)'] = subtext_split[0]
|
|
import_df['Purchase Order Item No. (Items)'] = subtext_split[1]
|
|
|
|
|
|
|
|
test_po_det_extraction = import_df[['Item Subtext (Items)','Purchase Order Number (Items)', 'Purchase Order Item No. (Items)']]
|
|
|
|
|
|
test_po_det_extraction
|
|
|
|
|
|
#purchase_details_hawb_df = pd.read_excel(r"C:\Users\Admin\Downloads\Consolidated Trade Data Apr May Jun 2024(Only DTAS).xlsx",dtype='object')
|
|
#purchase_details_df = purchase_details_hawb_df[['REQUEST ID','ITEM DESCRIPTION','PO number','PO Position']]
|
|
#hawb_details_df = purchase_details_hawb_df[['REQUEST ID','HAWB from Import BE']]
|
|
|
|
|
|
|
|
#purchase_details_df
|
|
|
|
|
|
#hawb_details_df.drop_duplicates(inplace=True)
|
|
|
|
|
|
#hawb_details_df
|
|
|
|
|
|
#purchase_details_hawb_df
|
|
|
|
|
|
'''print(len(import_df))
|
|
merged_df = pd.merge(import_df, hawb_details_df, left_on='Request ID', right_on='REQUEST ID', how='left')
|
|
#merged_df.drop('Key2', axis=1, inplace=True)
|
|
print("After Join")
|
|
print(len(merged_df))'''
|
|
|
|
|
|
#change required if join is used
|
|
merged_df = import_df
|
|
merged_df['HAWB / HBL No. (Shipment Details)']=merged_df['MAWB / MBL No. (Shipment Details)']
|
|
|
|
|
|
#change if joins from consolidated files are required
|
|
print(len(import_df))
|
|
#final_df = pd.merge(merged_df, purchase_details_df, left_on=['Request ID (Items)','Item Description (Items)'], right_on=['REQUEST ID','ITEM DESCRIPTION'], how='left')
|
|
#merged_df.drop('Key2', axis=1, inplace=True)
|
|
#print("After Join")
|
|
print(len(merged_df))
|
|
final_df=merged_df
|
|
|
|
|
|
|
|
|
|
#drop Request ID Duplicate Columns from Invoice, Item and Shipment
|
|
#columns_to_drop = ["HAWB from Import BE", "Request ID (Invoices)","Request ID (Items)","Request ID (Shipment Details)","index",'PO number','PO Position','ITEM DESCRIPTION','REQUEST ID_x','REQUEST ID_y']
|
|
columns_to_drop = [ "Request ID (Invoices)","Request ID (Items)","Request ID (Shipment Details)","index"]
|
|
|
|
final_df.drop(columns=columns_to_drop,inplace=True)
|
|
|
|
print("DTA - Validations Starts")
|
|
print("Excel Rows:")
|
|
print(len(df_unfiltered))
|
|
|
|
print("E+H Rows:")
|
|
print(len(df))
|
|
|
|
import_df
|
|
status = True
|
|
#Header QC
|
|
print("Unique Header Rows:")
|
|
count_header_uni = len(uni_header)
|
|
print(count_header_uni)
|
|
|
|
print("Unique Output Header Count:")
|
|
count_output_header = final_df["Request ID"].count()
|
|
print(count_output_header)
|
|
|
|
if(count_header_uni==count_output_header):
|
|
print("Header Count Checked")
|
|
else:
|
|
status = False
|
|
print("Failing Validation - Header Count Not Matching")
|
|
|
|
#Invoice QC
|
|
print("Unique Invoice Rows:")
|
|
count_invoice_uni = len(uni_inv)
|
|
print(count_invoice_uni)
|
|
|
|
print("Output Invoice No. Count:")
|
|
count_invoice_header = final_df["Invoice No (Invoices)"].count()
|
|
print(count_invoice_header)
|
|
|
|
if(count_invoice_uni==count_invoice_header):
|
|
print("Invoice Count Checked")
|
|
else:
|
|
status = False
|
|
print("Failing Validation - Invoice Count Not Matching")
|
|
|
|
|
|
#Item QC
|
|
print("Input Item Rows:")
|
|
count_item_uni = len(item)
|
|
print(count_item_uni)
|
|
|
|
print("Output Item Rows:")
|
|
count_output_rows = len(final_df)
|
|
print(count_output_rows)
|
|
|
|
if(count_item_uni==count_output_rows):
|
|
print("Total No. of Rows Checked")
|
|
else:
|
|
status = False
|
|
print("Failing Validation - Item Count Not Matching")
|
|
|
|
#Shipment QC
|
|
print("Total Shipment Rows:")
|
|
count_shipments = shipment_consolidated['Total Number of Packages (Shipment Details)'].count()
|
|
print(count_shipments)
|
|
|
|
print("Total Shipments in Output:")
|
|
count_output_shipments = final_df['Total Number of Packages (Shipment Details)'].count()
|
|
print(count_output_shipments)
|
|
|
|
|
|
if(count_shipments==count_output_shipments):
|
|
print("Total No. of Shipments Same")
|
|
else:
|
|
status = False
|
|
print("Failing Validation - Header Count Not Matching")
|
|
|
|
#Sum Checks
|
|
|
|
sum_qty_input = float(df["Quantity (Items)"].sum())
|
|
sum_qty_output = float(final_df["Quantity (Items)"].sum())
|
|
|
|
if(sum_qty_input==sum_qty_output):
|
|
print("Input-Output Qty Matched")
|
|
else:
|
|
status = False
|
|
print("Failing Validation - Quantity Not Matching")
|
|
|
|
sum_ass_val_input = float(df["Assessable Value for the Item In INR (Items)"].sum())
|
|
sum_ass_val_output = float(final_df["Assessable Value for the Item In INR (Items)"].sum())
|
|
print(f'Ass Value : {sum_ass_val_input} vs {sum_ass_val_output}')
|
|
if(int(sum_ass_val_input)==int(sum_ass_val_output)):
|
|
print("Input-Output Qty Matched")
|
|
else:
|
|
status = False
|
|
print("Failing Validation - Ass. Value not Matching")
|
|
print(f'Ass Value : {sum_ass_val_input} vs {sum_ass_val_output}')
|
|
|
|
sum_bcd_input = float(df["Basic Customs Duty (Items)"].sum())
|
|
sum_bcd_output = float(final_df["Basic Customs Duty (Items)"].sum())
|
|
if(int(sum_bcd_input)==int(sum_bcd_output)):
|
|
print("Input-Output BCD Amount Matched")
|
|
else:
|
|
status = False
|
|
print("Failing Validation - Quantity Not Matching")
|
|
|
|
|
|
print(final_df['Request out of Charge Date'].dtype)
|
|
ooc_date = pd.to_datetime(final_df['Request out of Charge Date'], errors='coerce')
|
|
start_date = ooc_date.min().strftime('%Y-%m-%d')
|
|
end_date = ooc_date.max().strftime('%Y-%m-%d')
|
|
print(f"From:{start_date}")
|
|
print(f"End:{end_date}")
|
|
|
|
# Create the filename with prefix and date range
|
|
prefix = "FTWZ-DTA-Canofy-DataImport"
|
|
|
|
output_folder = os.path.dirname(file_path)
|
|
filename = os.path.join(output_folder, f"{prefix}_{start_date}_to_{end_date}.xlsx")
|
|
print(filename)
|
|
final_df.to_excel(filename,index=False)
|
|
if(status==True):
|
|
print("Validations are Positive...You can load the data.")
|
|
else:
|
|
print("Failed Validations, Please inform the team and check data for more details.")
|
|
|
|
print("Validations Ends")
|
|
|
|
|