How to export order data to Excel

[whiterabbit]

Exporting your SquirrelCart order data to Excel is easy using Excel Web Queries.

Simply use Notepad to create a text file with the following contents:

WEB
1
http://www.YOURDOMAIN.COM/squirrelcart/index.php?
show_record_links=1&table=Orders&show_all_fields=yes&cell_data_truncated_length=9999

Selection=AllTables
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False


Be sure and change the YOURDOMAIN.COM text in your file to your actual domain. Also, if you installed squirrelcart in a different folder, you'll need to modify the above URL to match the path to your cart. Be careful not to change anything else. "WEB" should be the first line in the file. Don't add any extra lines, etc. anywhere in the file.

Now, give this file a name, like "order_data.iqy" Be sure and use the extension ".iqy"

Now, open Excel click DATA on the menu bar. Click Import External Data, then click Import Data, then browse to the file you created and open it.

You might need to first login to SquirrelCart as the Admin in a separate browser to gain permissions.

Excel will import the data into your spreadsheet!

[Jamie]

Thanks for the tip! :squirrel: I haven't tested it, but it sounds like it would work fine. This should be a good alternative for exporting data until we add an export feature in the future.

[whiterabbit]

I've love to know if it works for you OK. I'm not exactly sure how Excel have permission for that page. I guess from a cookie?
Max

[Jamie]

Hi Max,

This worked fine for me. I did have it set to remember my login, which probably helps. The column headers didn't line up with the data, but I tested it in the next release, which has a different table layout that may have caused that.

Thanks,
Jamie

[welshandy]

I currently use the excel query example provided on this forum to pull orders into an MS-ACCESS DB where I can then generate nicely formatted report invoices for my customers and take care of stock data and profit reporting.

The problem with the excel query is that it is based on the ORDERS table which only pulls the details of the products into the SHIPPING DETAILS field. This then means that I have to manually extract the product data and so can't automate stock updating and other tasks that are required in the ACCESS DB.

Is there a way that the excel query can pull in data from more than one table and if so what would the necessary query be to pull the required product data into their own fields?