MYSQL command to export magent 2 tables in CSV format

Question

Hi Guys I need to export the Magento 2 sales order table in CSV format with the fallowing requirements. Could you guys help me. Thanks. You must Export from your Database with these exact settings:

Include column headings.

Column separator: , (comma)

Quotes symbol: “ (regular double-quotes)

Escape symbol: “ (regular double-quotes)

Newline symbol: \n (proper newline character)

NULL values to be empty.

Answer 1

Could you please try with this code:

<?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('frontend');

$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();

// Select all fields from the sales_order table
$select = $connection->select()
    ->from(['o' => $resource->getTableName('sales_order')]);

// Query the database to fetch the data
$rows = $connection->fetchAll($select);

// Set the filename for the CSV file
$filename = 'sales_orders.csv';

// Set the headers for the CSV file
$headers = [
    'Order ID',
    'Customer ID',
    'Customer Email',
    'Customer First Name',
    'Customer Last Name',
    'Order Status',
    'Base Grand Total',
    'Created At',
    'Updated At'
];

// Open a file handle for writing the CSV data
$fp = fopen('php://output', 'w');

// Write the headers to the CSV file
fputcsv($fp, $headers, ',', '"');

// Loop through each row and write it to the CSV file
foreach ($rows as $row) {
    // Replace null values with empty strings
    array_walk($row, function(&$value) {
        $value = $value ?? '';
    });
    
    // Write the row to the CSV file
    fputcsv($fp, $row, ',', '"');
}

// Close the file handle
fclose($fp);

// Send the CSV file as a download
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Pragma: no-cache');
readfile('php://output');
exit;

You can save this code to a PHP file in your Magento 2 installation and then run it from your web browser to export the sales order table to a CSV file. The script uses the Magento 2 Object Manager to query the database and fetch the sales order data, and then uses the fputcsv() function to write the data to a CSV file. The resulting CSV file is then sent as a download to the user's web browser. The script also handles null values by replacing them with empty strings, as specified in the requirements.

Answer 2

You can export Magento 2 orders table in CSV in admin in System > Export . In Export Settings, choose exact settings for you