OpenCart orders and options Excel export tool


This script should work in all OpenCart 2.x and OpenCart 3.x because it extracts the data directly from the OpenCart Database and does not rely on any OpenCart directory files. No VQMod, OCMod, theme or permissions required!

The script just dumps readable order data in an Excel file, you may later use Excel functions to auto-format the data according to your needs.

Inside the package

There are two versions of the script within the ZIP package. The first one exports the contents directly to the Excel format and should work in most of the installations if your computer/database/server uses English/Latin character sets.

If the exported XLS appears with strange characters you should use the UTF-8 TXT version of the script. It allows conversion of different database charsets to UTF-8 and then exports it to a semicolon delimited .TXT file that can later be imported into Excel.


1. Edit the script in any text editor. Set the configurations of your database and set an access password. The password will protect the script from outside access and should be different than the DB password.

2. Place the modified script in any accessible (but safe) folder in your server, for example, the OpenCart /admin directory.

3. Access the script by using the following URL. Remember to replace YOURPASSWORD with the password you set in the configuration file.


4. If everything is OK you will be prompted to download the Excel file.

5. Each line of the Excel file represents one order item or item option. You may group options for the same item by using the column product_item.

6. In case of errors, the file will be downloaded in the PHP format. Just edit it to see what's wrong.

7. If the characters are not displaying correctly consider the UTF-8 TXT version of the script. This requires an additional configuration of the source database charset.

Import to Excel - Convert product options to columns

Requirements: Excel 2016 for Windows or previous versions + Microsoft Power Query free addon.

  1. Open the exported file (CSV or XLS).
  2. Select the table columns you want to use. Do not select all the 16000 available spreadsheet columns, just the ones that have data.
  3. In Excel 2016, go to the "Data" menu and then click on "From Table Range". In older Excel versions with Power Query, go to the "Power Query" menu and click on "From Table Range" button.
  4. The query editor will open.
  5. Select the "option_name" column.
  6. Go to "Transform" menu and click on the "Replace Values" button.
  7. Replace "null" by any text, for example "empty".
  8. Keep the "option_name" column selected.
  9. Back in the "Transform" menu, click on the  "Pivot Column" button.
  10. In the dialog box, select "options_value" column to the "Values Column" field and then, inside advanced options, select "Don't aggregate".
  11. Now the table options should appear as columns for each order.
  12. Close the query editor and chose to keep/save the changes. You are back to Excel.

For more information, check this article.

Release notes

Version 5
  • Implemented MySQLi functions for modern PHP 7+ compatibility.


MIT. Do whatever you want with it.

OpenCart 2.x and 3.x
File size
8 KB

Post Categories


Hello, please how to export only orders from a customer group or multistore, of even possible both ?

example oc_customer_group id 8

Hi Dan

Thanks for the extension which I have been happily using for about a year.  But it has suddenly stopped working.   Just doesn't give any download file.  Any idea why?



Submitted by Mark on Tue, 04/14/2020 Permalink

Is it easy enough to add shipping cost onto this?


Great work BTW, works much better than any of the [paid!!!] plugins!

Submitted by Anonymous on Thu, 10/03/2019 Permalink

Thank for yor code. I'm using Turkish language and some characters ( as İ, ı, ğ, ş) appear as ? :( How can i solve tihs problem. Pls help



What is the language of the Database? Can you see the data properly with other software?

The script should be configured to the same language/collatio as the database, otherwise strange characters will appear.

Submitted by TASOS KARAGIANNIS on Mon, 07/23/2018 Permalink

thank you for the extension it is works fine.

is it possible to export custom fields as different columns in excel?

Submitted by Martin on Wed, 08/30/2017 Permalink


could you please help me. 

want to add something like this:

(SELECT substring_index(substring_index(`shipping_custom_field`, '\{"6":"', -1), '\","', 1) FROM `".PREFIX."order`) AS test,
    CONCAT (`".PREFIX."order`.invoice_prefix,`".PREFIX."order`.invoice_no) AS invoice_no,
    SELECT total*currency_value AS order_total,


but the first line doesn´t work:

(SELECT substring_index(substring_index(`shipping_custom_field`, '{"6":"', -1), '","', 1) FROM `".PREFIX."order`) AS test

how can I make it work in your file?

Thank you very much.


SELECT can only accept column names as parameters. You are trying to fetch data inside the column. This won't work.

You should first get the column data and then process the returned values to filter the information you need.

Submitted by Paul on Mon, 06/05/2017 Permalink


Your script opencart2.xorderxlsexport works is fine. But my data on Russian lang. And script Saved data with Question marks.

I search the code on stackoverflow:

header('Content-Encoding: UTF-8');
header('Content-type: text/csv; charset=UTF-8');
header("Content-disposition: attachment; filename=filename.csv");
header("Pragma: public");
header("Expires: 0");
echo "\xEF\xBB\xBF"; // UTF-8 BOM

but this not work too.

Please help me ;)

Hello Paul,

The version 2 comes with an additional script that exports the data to a UTF-8 TXT file that later can be imported into Excel.

This version requires an additional configuration for the source database charset. Most of the databases should be UTF-8 but yours could be configured to ISO-8859-5 (Russian) so you need to check this information.

To open it, open the TXT file from Excel and then select the option "Delimited fields" and then set the delimiter to "semicolon" (;).