How to Export Magento Orders with Product Details to CSV

How to Export Magento Orders with Product Details to CSV
Learn how to export Magento 2 orders with detailed product information to a CSV file using four proven methods: admin extensions, custom PHP scripts, REST APIs, and direct SQL queries. This guide covers step-by-step instructions for each approach, helping both developers and store owners streamline reporting, data analysis, and third-party integrations.
Table Of Content
How to Export Magento Orders with Product Details to CSV
Exporting Magento 2 orders with product details into a CSV file is essential for reporting, analytics, and integration with other systems. This guide outlines four practical methods to achieve this, catering to various technical proficiencies and requirements.
1. Export via Custom Order Grid Extension
Utilize a Magento 2 extension like Meetanshi's Custom Order Grid to add additional columns to the order grid and export data directly from the admin panel.
Steps:
- Install and configure the extension.
- Navigate to Sales > Orders in the admin panel.
- Click on Columns and select the desired product attributes to display.
- Use the Export button to download the data in CSV format.
Advantages:
- No coding required.
- Supports over 50 customizable columns.
- Quick and user-friendly.
Considerations:
- Relies on third-party extensions.
- May require additional configuration for specific needs.
2. Export Programmatically Using a PHP Script
For developers, creating a PHP script allows for automated and customizable exports.
Sample Script:
<?php
use Magento\Framework\App\Bootstrap;
require __DIR__ . '/app/bootstrap.php';
$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();
$state = $objectManager->get('Magento\Framework\App\State');
$state->setAreaCode('adminhtml');
$orderRepository = $objectManager->get('Magento\Sales\Api\OrderRepositoryInterface');
$searchCriteriaBuilder = $objectManager->get('Magento\Framework\Api\SearchCriteriaBuilder');
$searchCriteria = $searchCriteriaBuilder->create();
$orders = $orderRepository->getList($searchCriteria)->getItems();
$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";
Execution:
Run the script via CLI:
php export-orders-with-products.php
Advantages:
- Highly customizable.
- Suitable for automation and integration.
Considerations:
- Requires PHP knowledge.
- Ensure proper error handling and security measures.
3. Export Using Magento 2 REST API
Magento's REST API provides a way to programmatically access order data.
Steps:
- Set up API credentials in the Magento admin panel under System > Integrations.
- Use the API endpoint to fetch orders:
- Parse the JSON response and extract the necessary fields.
- Convert the data into CSV format using a scripting language like Python or PHP.
GET /rest/V1/orders?searchCriteria[pageSize]=10
Advantages:
- No need to access the Magento backend directly.
- Can be integrated into external systems.
Considerations:
- Requires understanding of REST APIs.
- May need to handle pagination and authentication.
4. Export Directly from the Database Using SQL
For direct access, SQL queries can extract order and product information from the Magento database.
Sample Query:
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 AS so
JOIN sales_order_address AS soa
ON so.entity_id = soa.parent_id AND soa.address_type = 'billing'
JOIN sales_order_item AS soi
ON so.entity_id = soi.order_id
WHERE soi.parent_item_id IS NULL
ORDER BY
so.increment_id,
soi.item_id;
Execution:
Run the query using a database management tool like phpMyAdmin or via CLI. Export the results to a CSV file.
Advantages:
- Direct access to all data.
- No need for Magento-specific tools.
Considerations:
- Requires knowledge of Magento's database schema.
- Risk of data inconsistency if not handled properly.
Comparison of Methods
Choosing the right number of items per page is crucial:
Method | Customizable | Best For |
---|---|---|
Custom Order Grid Extension | Moderate | Non-developers needing quick exports |
Text Lists | 10–15 | |
PHP Script | High | Developers needing automation |
REST API | High | Integration with external systems |
Direct SQL Query | High | Database administrators |
Choose the method that best aligns with your technical expertise and business requirements. For non-developers, extensions offer a user-friendly approach. Developers may prefer scripts or APIs for greater control and automation.
Tip
To enhance your eCommerce store’s performance with Magento, focus on optimizing site speed by utilizing Emmo themes and extensions. These tools are designed for efficiency, ensuring your website loads quickly and provides a smooth user experience. Start leveraging Emmo's powerful solutions today to boost customer satisfaction and drive sales!
FAQs
How can I export Magento orders with product details to CSV?
You can export orders with products to CSV using a custom grid extension, PHP script, REST API, or direct SQL queries, depending on your skill level and project needs.
What is the easiest way to export orders with products?
The simplest way is using a Magento 2 custom order grid extension that lets you add product details as columns and export the grid as a CSV without any coding.
Can I export Magento orders with PHP code?
Yes. You can write a PHP script that uses Magento’s OrderRepository to fetch order and item data, then write it into a CSV file with custom fields.
Is it possible to automate order export to CSV?
Yes, using a PHP script or the REST API, you can schedule automatic exports via cron jobs or external integrations.
How do I export orders using the Magento REST API?
Set up an API integration in Magento admin, then call the orders endpoint. Use a script (e.g., Python) to fetch data, extract order and item fields, and write them to a CSV file.
Can I get order details directly from the Magento database?
Yes. Run a SQL query joining `sales_order`, `sales_order_address`, and `sales_order_item` tables to pull order info with product SKUs, names, quantities, and prices.
Is a third-party extension required for exporting orders?
No, but using a trusted extension simplifies the process for non-developers by adding export features directly in the admin panel.
Can I choose which fields appear in the export?
Yes. With a custom grid or scripting approach, you can select fields like customer name, SKU, quantity, and price based on your reporting needs.
Does exporting large order data affect site performance?
Yes, if not optimized. Paginate or filter large datasets before exporting, and avoid running heavy queries on live production databases during peak hours.
Where can I find sample scripts or documentation for order export?
You can check Magento DevDocs, community GitHub repos, and blogs for sample PHP and API scripts that export orders with product data to CSV.