WordPress Report Processing Script

Purpose:

This script is designed to process .xls reports generated by a specific WordPress-based system. To work correctly, the input file structure must align precisely with the expected format. Using files with a different structure will likely result in errors.

Overview

This script streamlines the handling of customer order data exported from a WordPress system. In its raw form, the report often repeats customer information for every product in an order. For example, if a customer orders three products, their details appear on three separate rows. The script eliminates these redundancies, organizing the data into a more structured and actionable format for analysis and logistics.

Key Features

Duplicate Customer Data:

Combines repetitive customer information into a single, consolidated entry.

Data Cleaning:

Strips unnecessary HTML tags (e.g., , ) from product names, ensuring they are clean and readable.

Product Bundles:

Automatically dissects product bundles (e.g., "Pakiet Klasyczny") into their individual components, listing each item with its respective quantity.

Weight Calculation:

Converts product quantities into total weights in kilograms. For example, products like "150g," "500g," and "1kg" are aggregated to provide a total weight.

Logistics Reports:

Generates a delivery report tailored for courier services by filtering out unnecessary data and including only relevant customer and order details.

Excel Formatting:

Adjusts column widths automatically for better readability. Highlights important rows (e.g., containing "w kawałku"). Organizes the processed data into three distinct Excel sheets for clarity. Input Data Requirements The script works exclusively with .xls files generated by a specific WordPress system. For optimal performance, the input file must include the following columns:

Line number

Email (Billing)

First Name (Shipping)

Last Name (Shipping)

Address 1&2 (Shipping)

City (Shipping)

Postcode (Shipping)

Order Subtotal Amount

Order Shipping Amount

Item Name

Quantity (-Refund)

SKU

Item #

Output Data

Products (Produkty):

A consolidated list of all products, including unpacked bundles with summed quantities. Cleaned product names, free from HTML tags. Product Weights (Produkty Gramatury):

A summary of total product weights, aggregated in kilograms. Couriers (Kurierzy):

A logistics-friendly report including only essential delivery information such as customer addresses, contact details, and order summaries. This script simplifies the data processing workflow, turning complex and repetitive order reports into clear and actionable outputs for business needs.