Prestashop CSV to Organized Mailchimp Excel List with Python

As an online store owner, email marketing is crucial for staying connected with customers and boosting sales. I use Prestashop as my e-commerce platform and Mailchimp to manage my newsletters. However, keeping my subscriber list of the Prestashop CSV in sync between the two platforms has been a surprisingly tricky task.

The Challenge: Prestashop, Mailchimp, and the Missing Module

Ideally, customers subscribing to my newsletter on Prestashop should automatically flow into Mailchimp. Unfortunately, there’s no built-in integration, and I didn’t want to invest in a separate Prestashop module to achieve this. Manually exporting and importing contact lists became my only option, but it was far from perfect.

Prestashop’s newsletter export produces a simple CSV file with fields like this:

id;shop_name;gender;lastname;firstname;email;subscribed;subscribed_on;iso_language

Each subscriber’s information is crammed into a single cell, like this:

1;Canarias Agusto;Mr;Sajnani;Kunal;email@email.com;1;2024-03-24 19:13:29;en

Naively, I thought importing this Prestashop CSV into Mailchimp would be a breeze. Alas, Mailchimp couldn’t recognize the individual fields, meaning no new subscribers were added. Worse yet, my large contact list had become riddled with spam emails after I implemented anti-bot measures on my site.

Data Sanitization: A Tedious Task

Cleaning up 10,000+ rows of awkwardly formatted data was daunting. The limitations of the Prestashop CSV files made it impossible to easily sort, filter, or modify entries. It was time to think differently.

A Python Solution: CSV Transformation

I remembered my past experiences with Python. While no coding expert, I know just enough to get dangerous. My goal was simple: convert that messy CSV into a clean and structured Excel file where I could manipulate data as needed. Here’s the Python script that saved the day:

Python

import pandas as pd
import os

# Load the CSV file (adjust path as needed)
df = pd.read_csv('C:\\Users\\kunal\\Desktop\\Test\\test.csv', sep=';', names=['id', 'shop_name', 'gender', 'lastname', 'firstname', 'email', 'subscribed', 'subscribed_on', 'iso_language'])

# Write the data to an Excel file
output_file = 'output.xlsx'
df.to_excel(output_file, index=False)

# Print some useful output
print(f"Data successfully written to {output_file}")

current_directory = os.getcwd()
output_file_path = os.path.join(current_directory, output_file)
print("Output Excel file saved at:", output_file_path)

How It Works

Let’s break down what this code does:

1. Import Libraries: We use pandas (a powerful data analysis tool) and os (for getting file paths).
2. Load the Messy CSV: pd.read_csv reads the file, using the semicolon (;) as the separator and assigning column names.
3. Excel Magic: df.to_excel does the heavy lifting – it transforms the data into a proper Excel file.
4. Success Messages: The script prints confirmation and the location of your new Excel file.

The Result: Data I Can Work With!

Now, I have a proper Excel file with columns for ID, name, email, etc. Sorting, filtering, removing spam entries, and preparing the data for Mailchimp became a whole lot easier.

Beyond the Basics

This script is a lifesaver, but it could be even better. In the future, I’d like to explore adding functions to directly filter out spam-like emails and potentially automate the entire process to run on a schedule.

Let me know in the comments if you’ve faced similar hurdles or found clever ways to streamline data workflows!

Note: This article was written with the assistance of AI.