Methods to Export Magento Orders with Products in CSV

Running a Magento store means dealing with lots of orders—and sometimes, you need to get all that order info into a sheet. 

In this blog post, we will show you how to export Magento orders with products in CSV using different methods. 

Methods to Export Orders with Products

There are mainly four methods in Magento to export orders with products: 

Use Custom Order Grid – Here, you will need to extend the existing grid in your Magento 2 UI. The best way to do so is using a Magento 2 order export extension to add custom columns and filters.

Custom Order Export Script – Create a PHP script to query order and related product data to export directly to a CSV file. This method is used for scheduled exports or dealing with an external system.

Magento 2 API – You can export products using the API, where you need to use Magento REST and GraphQL APIs. This process can become complex for advanced reporting.

Query Order DB – Directly access the Magento database and extract the order and product details. This method means expert supervision.  

Of these four methods, three require you to manually add code where the risk of damage is high. 

The recommended one is using a custom order grid. 

How to Export Orders with Products Using a Custom Grid?

Here, we will use the Meetanshi Magento 2 Custom Order Grid extension to add an order grid directly to your Magento 2 store and take mass action upon it instantly. 

Install the extension and follow these simple steps for exporting Magento orders with products CSV. 

Step 1: Head to Columns in The Order Grid  

First, you need to add a product column in the order grid. For that, navigate to Sales > Orders 

Now, click Columns in the upper right-hand side corner. 

Step 2: Select the Products from the Columns

Now, click Columns and select the product details you want to add to the order grid. The selected columns will automatically be added to the grid. 

Then click Export and choose CSV as the file format. 

Step 3: Open CSV file in Excel or Google Sheet 

Now, open the exported CSV file in a Google sheet or Excel. 

In just three steps, your Magento 2 export orders with products is done without any coding. 

Magento 2 Export Orders CSV Programmatically via PHP Script

Follow these three easy steps to export orders in CSV programmatically.

Step 1: Create an Order file With Products 

Go to your Magento 2 root directory and create a new file named export-orders-with-products.php

Step 2: Use this Order Code

Now, paste the following code into the newly created file.

<?php
use Magento\Framework\App\Bootstrap;

require __DIR__ . '/app/bootstrap.php';
$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();

// Set area code
$state = $objectManager->get('Magento\Framework\App\State');
$state->setAreaCode('adminhtml');

// Get order repository
$orderRepository = $objectManager->get('Magento\Sales\Api\OrderRepositoryInterface');
$searchCriteriaBuilder = $objectManager->get('Magento\Framework\Api\SearchCriteriaBuilder');

// Build search criteria (e.g., all orders)
$searchCriteria = $searchCriteriaBuilder->create();
$orders = $orderRepository->getList($searchCriteria)->getItems();

// Export to CSV
$file = fopen('orders_with_products.csv', 'w');
fputcsv($file, ['Order ID', 'Date', 'Customer', 'Status', 'SKU', 'Product Name', 'Quantity', 'Price']);

foreach ($orders as $order) {
    $customer = $order->getBillingAddress()->getFirstname() . ' ' . $order->getBillingAddress()->getLastname();
    foreach ($order->getItems() as $item) {
        fputcsv($file, [
            $order->getIncrementId(),
            $order->getCreatedAt(),
            $customer,
            $order->getStatus(),
            $item->getSku(),
            $item->getName(),
            $item->getQtyOrdered(),
            $item->getPrice()
        ]);
    }
}

fclose($file);
echo "Exported to orders_with_products.csv\n";

Step 3: Get Your CSV File 

Then, run this PHP script by using this command:

php export-orders-with-products.php

Now, you can find your CSV saved in the root directory.

Use Magento 2 API to Get Orders with Products

Step 1: Grant Order Permissions 

From your Magento admin panel, head to System > Integrations > Add New Integration. Add integration details with your credentials. 

Then head to Sales > Orders permissions and grant the order pres

Step 2: Use API for Order Data With Products 

Now, you can use the API to get the orders data with products. For e.g., here’s the python script for it:

import requests
import csv
from oauthlib.oauth1 import Client

# OAuth credentials
consumer_key = 'your_consumer_key'
consumer_secret = 'your_consumer_secret'
access_token = 'your_access_token'
access_token_secret = 'your_access_token_secret'

# API endpoint
url = 'https://yourstore.com/rest/V1/orders?searchCriteria[pageSize]=10'

# OAuth client
client = Client(consumer_key, consumer_secret, access_token, access_token_secret)
uri, headers, _ = client.sign(url)

# Fetch orders
try:
    response = requests.get(uri, headers=headers)
    response.raise_for_status()
    orders = response.json()['items']
    print(f"Fetched {len(orders)} orders.")
except Exception as e:
    print(f"API error: {e}")
    exit(1)

# Export to CSV
with open('orders_with_products.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(['Order ID', 'Date', 'Customer', 'Status', 'SKU', 'Product Name', 'Quantity', 'Price'])
    for order in orders:
        customer = f"{order['billing_address']['firstname']} {order['billing_address']['lastname']}"
        for item in order.get('items', []):
            writer.writerow([
                order['increment_id'],
                order['created_at'],
                customer,
                order['status'],
                item.get('sku', 'N/A'),
                item.get('name', 'N/A'),
                item.get('qty_ordered', 0),
                item.get('price', 0.0)
            ])
print("Exported to orders_with_products.csv")

Query & Export Data from Magento 2 Database

For this last method, in your Magento 2 database, run the following query to fetch orders and product details.

SELECT
    so.increment_id AS order_id,
    so.created_at AS order_date,
    CONCAT(soa.firstname, ' ', soa.lastname) AS customer,
    so.status AS order_status,
    soi.sku,
    soi.name AS product_name,
    soi.qty_ordered,
    soi.price
FROM sales_order so
JOIN sales_order_address soa ON so.entity_id = soa.parent_id AND soa.address_type = 'billing'
JOIN sales_order_item soi ON so.entity_id = soi.order_id
WHERE soi.parent_item_id IS NULL -- Exclude configurable product parents
ORDER BY so.increment_id, soi.item_id;

You can then export the data to a CSV file to get the orders with products.

Finally, What’s the Easiest Way to Get Orders with Products?

The easiest method for this task is to add a custom order grid and then directly download the CSV file. 

You get access to 50+ useful columns like customer info, shipping method, coupon codes, and more without the need to add any PHP code or use an API. 

A simple and safe method to export orders with products in Magento 2.