PrestaShop 1.7: How to Export Customer Phone Numbers with SQL

Mastering Customer Data: Exporting Phone Numbers from PrestaShop 1.7 with SQL

As an e-commerce expert at Migrate My Shop, we understand that comprehensive customer data is the lifeblood of any successful online store. While PrestaShop offers robust features, extracting specific data sets, like customer phone numbers, isn't always straightforward with the default export tools. This often leaves store owners, marketers, and migration specialists scrambling for solutions.

This guide, inspired by a common challenge faced by PrestaShop users (like the one in forum thread #1105161), will walk you through the process of accurately exporting customer names, emails, and crucially, their phone numbers from your PrestaShop 1.7.x database using a direct SQL query. Whether you're preparing for a migration, launching a targeted marketing campaign, or simply enhancing your customer service, having this data at your fingertips is invaluable.

Why Default Exports Fall Short for Phone Numbers

PrestaShop's built-in export functionalities are excellent for general data, but they often prioritize core customer information like names and email addresses. Phone numbers, typically stored within customer address records, can be overlooked or not easily consolidated in a single, clean export. This is where a direct SQL query becomes your most powerful ally, allowing you to precisely select and combine data from different tables.

The Challenge: A user on the PrestaShop forum, running PrestaShop 1.7.8.10, needed to export customer names, first names, and phone numbers. The default export only provided email addresses. This scenario is incredibly common, highlighting the need for a more granular approach.

The Solution: Leveraging PrestaShop's SQL Manager

PrestaShop provides an SQL Manager within its administration panel, a powerful tool that allows you to execute custom SQL queries directly against your database. This is the ideal place to run our specialized query.

Step 1: Locate Your PrestaShop Database Table Prefix

Before executing any SQL query, you need to know your database table prefix. By default, PrestaShop uses ps_, but many installations use a custom prefix for security or multi-store setups. Using the wrong prefix will result in errors like "Table 'ps_customer' doesn't exist."

• Via PrestaShop Admin Panel: Navigate to Advanced Parameters > Information. Look for the "Database Information" section; your table prefix will be listed there.
• Via FTP/File Manager: Connect to your server via FTP and open the file ./app/config/parameters.php. The prefix is typically defined within this file.
• Via phpMyAdmin: If you have direct database access, open phpMyAdmin. You'll see a list of tables, all starting with your specific prefix (e.g., yourprefix_customer, yourprefix_address).

Step 2: Crafting the Refined SQL Query

The following SQL query is designed to retrieve the customer ID, first name, last name, email, and both landline (phone) and mobile (phone_mobile) numbers. It intelligently joins the customer table with the address table, ensuring you get the most relevant contact details.

SELECT
    c.id_customer,
    c.firstname,
    c.lastname,
    c.email,
    a.phone,
    a.phone_mobile
FROM
    [YOUR_PREFIX]customer c
LEFT JOIN
    [YOUR_PREFIX]address a ON a.id_customer = c.id_customer
    AND a.deleted = 0
    AND a.active = 1
WHERE
    c.deleted = 0
    AND (a.phone IS NOT NULL AND a.phone != '' OR a.phone_mobile IS NOT NULL AND a.phone_mobile != '')
GROUP BY
    c.id_customer
ORDER BY
    c.id_customer ASC;

Explanation of the Query:

• SELECT ... FROM [YOUR_PREFIX]customer c: We select specific columns from the customer table, aliased as 'c'.
• LEFT JOIN [YOUR_PREFIX]address a ON a.id_customer = c.id_customer: This is crucial. It links each customer to their addresses. A LEFT JOIN ensures that all customers are included, even if they haven't provided an address or phone number. The address table is aliased as 'a'.
• AND a.deleted = 0 AND a.active = 1: Filters for active and non-deleted addresses.
• WHERE c.deleted = 0: Ensures we only retrieve active customers.
• AND (a.phone IS NOT NULL AND a.phone != '' OR a.phone_mobile IS NOT NULL AND a.phone_mobile != ''): This refined condition (a key insight from the forum thread) ensures that only customers who have provided *at least one* phone number (landline or mobile) are included in the results. This helps filter out irrelevant entries.
• GROUP BY c.id_customer: Prevents duplicate customer entries if a customer has multiple addresses, ensuring each customer appears only once.
• ORDER BY c.id_customer ASC: Sorts the results by customer ID for easy review.

Remember to replace [YOUR_PREFIX] with your actual database table prefix!

Step 3: Executing the Query in SQL Manager

1. Log in to your PrestaShop admin panel.
2. Go to Advanced Parameters > Database > SQL Manager (or similar path depending on your PrestaShop version).
3. Click on "Add new SQL query" or locate the area to input a new query.
4. Paste the modified SQL query into the provided text area.
5. You can optionally save the query for future use.
6. Click "Execute" or "Save and Execute."

Step 4: Exporting to CSV

After executing the query, PrestaShop will display the results directly in the SQL Manager. Look for an "Export" icon or button (often a small disk or arrow pointing down) to download the results as a CSV file. This file will contain your desired customer data.

Step 5: Handling CSV Formatting: The "Leading Zero" Problem

A common issue when opening CSV files containing phone numbers in spreadsheet software (like LibreOffice Calc or Microsoft Excel) is the automatic removal of leading zeros. For example, '0612345678' might become '612345678'. This is because these programs often interpret numbers as numerical values, stripping leading zeros.

The Fix: When importing the CSV file into your spreadsheet software, ensure you specify the phone number columns (phone and phone_mobile) as 'Text' rather than 'Standard' or 'Number'. Most spreadsheet programs offer an import wizard that allows you to define data types for each column during the import process. This simple step, as highlighted by the forum user, ensures your phone numbers retain their correct format.

Important Considerations & Best Practices

• Data Privacy (GDPR/CCPA): Always ensure you comply with relevant data protection regulations (like GDPR or CCPA) when extracting and using customer data. Only collect and process data for legitimate purposes and with appropriate consent.
• Backup Your Database: Before running any SQL queries, especially if you're unfamiliar with them, it's always a good practice to back up your PrestaShop database.
• Test on a Staging Environment: If possible, test complex queries on a staging or development environment first to ensure they produce the expected results without affecting your live store.
• Data Cleansing for Migrations: For those planning a PrestaShop migration, this process is crucial for data auditing and cleansing. Accurate and complete customer data is paramount for a smooth transition to a new platform.

Conclusion

Extracting specific customer data like phone numbers from PrestaShop 1.7 doesn't have to be a daunting task. By understanding your database structure and leveraging the power of SQL queries through PrestaShop's SQL Manager, you gain precise control over your data exports. This capability is not just a technical trick; it's a strategic advantage for marketing, customer service, and especially for ensuring data integrity during critical operations like e-commerce migrations.

At Migrate My Shop, we specialize in seamless PrestaShop migrations, and clean, comprehensive data is at the heart of our success. If you're planning a migration or need expert assistance with your PrestaShop store, don't hesitate to reach out to migratemyshop.com – your PrestaShop Migration Hub.