OpenCart orders and options Excel export tool

Introduction

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.

Instructions

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.

URL: https://www.yourserver.com/yourfolder/admin/order_export.php?pw=YOURPAS…

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.

License

MIT. Do whatever you want with it.

Files
Version
5.0
Platforms
OpenCart 2.x and 3.x
File size
8 KB
Screenshots

Post Categories

Comments

Submitted by Annie on Tue, 01/26/2021 Permalink

Hi, this is a great script but I am having problem in the sense that each order is appearing multiple times because of options name and option value. I have tried the pivot table method in Excel (as you described above) but that just creates another sheet which does not help.

You mentioned above: 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. I am not good with SQL queries so I would be grateful if you could help me add some code in your script so that option names and values are concatenated and there is a single entry for each order in the final spreadsheet?

 

Hi Annie,

This would be a quite heavy code for the SQL query because it would require several requests to the database. The best option in my opinion would be modify the PHP code to post-process the database output and aggregate the options in the same line as the products. The code should take the $setData variable after the line 142 and re-iterate each one of the lines aggregating all options in the same cell.

Regards,

 

Dear Daniel,

Thank you so much for your quick response. Unfortunately I am not into PHP and SQL so I struggled a lot last night. I dont even care about option names. What I really want is that all my option values (I don't have more than 3) to just be concatenated and appear in one cell / row.

I tried something like:  CONCAT(".PREFIX."order_option.value, ',', ".PREFIX."order_option.value) AS Details. Obviously it only duplicates the same value so perhaps it needs some counter here or as you said, somewhere near line 142?

Any hint or help will be highly appreciated :)

Kind Regards

Annie

 

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?

Cheers

Gary

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

order

Hello,

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

hello,
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

Hello, 

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,
    order_id,
    CONCAT (`".PREFIX."order`.invoice_prefix,`".PREFIX."order`.invoice_no) AS invoice_no,
    telephone,
    SELECT total*currency_value AS order_total,
    currency_code,
    shipping_firstname, 
    shipping_custom_field,
    shipping_address_1,

etc...

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

Hi.

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" (;).