CSV order exporter
The CSV order exporter app can export order data into a CSV file. The app allows you to customise the output of the CSV file by:
- Giving new headings to the columns in the CSV file
- Changing the order that columns appear in
- Hiding/removing columns that you don't need
- Performing 'advanced configuration' to entirely reformat the data
Using the app you can:
- Generate either one CSV file per order or one CSV file for the previous day's orders
- Email the CSV file to your designated email address automatically
- Upload the CSV file to your server (either via FTP or SFTP)
- Generate a CSV file on demand containing multiple orders over a particular time period
To install the CSV order exporter app:
- Navigate to Apps
- Locate the CSV order exporter app
- Select
install this app
Once you've installed the app, navigate to Apps > Manage > CSV order exporter from your ShopWired account to configure the exporter settings.
Selecting the columns to include in the CSV file (and renaming columns)
Selecting the columns to include in the CSV file (and renaming columns)
Each column available to be included in the exported CSV file is displayed on the left-hand column of the table in the CSV Data section:
Select the columns you require in the export:
Each column in the CSV file is exported with a heading. The heading text can be set using the Heading in CSV export file column:
An explanation of each column and the data it contains can be found in the column definitions section of this help guide.
CSV rows
Each product in an order is included in the CSV file in its own row, and by default, the general order information is repeated on each row. To remove the repetition of order information, select the setting Don't repeat order information on subsequent lines for the same order.
Order information is information that is not unique to each product in an order (e.g. customer's name, address, order reference, order totals).
Uploading the CSV file to a server (by FTP/SFTP)
Uploading the CSV file to a server (by FTP/SFTP)
The CSV file can be exported to a server via an FTP/SFTP connection.
To activate the file upload feature:
- Select the
activate FTP exportsetting - Select
send a separate file for each orderif you'd like a single file to be uploaded for each order, or don't select this option and a file will be uploaded each day at 8.00am - Select the connection Protocol (either FTP or SFTP) and configure the connection information for your server (Host, Port, Username and Password) - these will be provided by your server provider
- Enter the path
- Select the
test sourceoption to ensure the connection information is correct and that the app can successfully connect to your server
Emailing the CSV file
Emailing the CSV file
The CSV file can be exported by the app emailing the file directly to your chosen email address. The app can either:
- Send a separate CSV file for each order
- Send a single CSV file each day (at 8.00am UTC)
To activate the email feature:
- Select the
activate email exportsetting - Select
send a separate file for each orderif you'd like a single email for each order, or don't select this option and an email will be sent each day at 8.00am UTC - Configure the email address that you'd like to receive the email to
Export on demand
Export on demand
Use the Export on Demand section to download orders from a particular date period or starting from a particular order reference:
Any request to export on demand a total of more than 100 orders will be placed into a queuing system. The CSV file will not be generated instantly and instead will appear as an available download in the Export history section of the Export on demand app. Previous exports are retained in the Export history section indefinitely.
Cancelled orders
- When exporting orders using the From reference feature, cancelled orders are not included in the export
- When exporting orders using the From date to To date feature, cancelled orders are included in the export
Advanced configuration
Advanced configuration
You can enable advanced configuration mode in the app if you want more control over the way in which data in the CSV file is presented:
Enabling advanced mode will disable any settings you have set for the standard mode. The advanced mode is configured completely separately.
Advanced mode uses variables to output data about the order (customer name, address, etc.) and the products within an order.
You can read more about Liquid here.
Advanced mode coding
Advanced mode coding
The app has access to ShopWired's API, specifically the orders API endpoint through an orders object, an array of orders. By iterating over this array, you will have access to the data for each order.
Field names are . For example, to access the trackingUrl, you would use the variable order.tracking_url.
To access deeper properties, for example, the billingAddress, first access the child object using the same dot notation and then access the property itself, e.g., order.billing_address.country.
Examples
Examples
A template containing just order information (no product information) with two columns is shown below.
Order Reference,Customer Email
{% for order in orders -%}
{{ order.reference }},{{ order.billing_address.email_address }}
{% endfor -%}
A template containing just product information as well as order information is shown below.
Order Reference,SKU,Quantity,Price
{% for order in orders -%}
{% for product in order.products -%}
{{ order.reference }},{{ product.sku }},{{ product.quantity }},{{ product.price }}
{% endfor -%}
{% endfor -%}
Tips
Tips
Comparison and calculated values
You cannot use filters and then do a comparison in the same statement. This won't work:
{% if product.quantity * product.price > 1000 %}
Expensive
{% endif %}
Use the assign statement to create a variable and then do the comparison:
{% assign amount = product.quantity * product.price %}
{% if amount > 1000 %}
Expensive
{% endif %}
Whitespace and special characters
Anything not outside of the double brackets will go directly to the output, including whitespace, commas and new lines. They may also be produced by dynamic parts in the template (double brackets).
As they have special meanings within CSV files, they have to be escaped. Failure to escape may lead to undesirable changes with the layout. You can use the quote filter to avoid this. For example, if you are using order.comments in the template, you should quote it as a comment is likely to include special characters:
{{ order.comments | quote }}
Likewise, any new line in the template will appear as is in the generated CSV, which may cause unwanted empty rows. For example, if you use:
{% for order in orders %}
{{ order.reference }}
{% endfor %}
There will be an empty row between each order. Using dashes on opening or closing tags will strip any whitespace to the left or right respectively:
{% for order in orders -%}
{{ order.reference }}
{% endfor -%}
Using the "Do not send/upload if empty" option
For this option to come into effect, the generated file must be completely empty. The app is unable to detect if a row is a header row or not. So you are responsible for putting the necessary logic in place so that no header line is generated. The example below will generate an empty file if the orders do not contain a product with SKU 'XXX':
{% assign empty = true -%}
{% for order in orders %}
{% for product in order.products %}
{% if product.sku == 'XXX' %}
{% assign empty = false %}
{% endif %}
{% endfor %}
{% endfor -%}
{% unless empty -%}
Order Reference,Customer Email
{% endunless -%}
...
Creating empty cells
To create empty cells, just put extra commas:
{% for order in orders -%}
{{ order.reference }},,{{ order.billing_address.email_address }}
{% endfor -%}
There will be an empty column between the reference and email.
Printing order information only on the first row
If you are exporting line item information (like quantity) along with order information (like reference), you will find that the order information is repeated on multiple rows. If this is undesirable, you can use the forloop object (read more here). For example:
{% for order in orders -%}
{% for product in order.products -%}
{% if forloop.first %}
{{ order.reference }},{{ order.billing_address.email_address }}
{% else %}
,
{% endif %}
,{{ product.price }}
{% endfor -%}
{% endfor -%}
Take note of the comma in the else part. This is required so that there are two empty cells; one for the 'Reference' column and one for the 'Email address' column.
Dates
Using the order.created variable will return the date that the order was completed in the format Mon, 09 Jan 2017 17:15:24 +0000. If this is undesirable, you can use a date filter, .
If you would like to return the date in the format DD/MM/YYYY you can use order.created | date: "%d/%m/%Y".
Errors
If there are errors in the template, the generated CSV will contain the error message instead of the order data.
ShopWired Payments
ShopWired Payments
When using ShopWired Payments, additional variables are available in advanced mode to output details about the transaction.
order.transaction.id
Returns the transaction ID
order.transaction.risk_level
Returns the transaction's risk level
order.transaction.card.last4
Returns the last 4 digits of the card used
order.transaction.card.brand
Returns the brand of the card used (e.g. Visa)
order.transaction.card.cvc_check
Returns the result of the CVC check
order.transaction.card.postcode_check
Returns the result of the postcode check
order.transaction.card.three_d_secure_result
Returns the result of the 3D secure check (blank if no check occurred)
order.transaction.fee_amount
Returns the processing fee amount
Column definitions
Column definitions
The table below displays an exact definition of the data that will be returned for each column:
| Column Name | Description |
|---|---|
| Order total | The grand total of the order (including tax/VAT) |
| Order created | The date that the order was completed |
| Order created dd mm yyyy | The date that the order was completed in dd mm yyyy format |
| Order archived | Will return true if the order has been archived or false if it hasn't |
| Order comments | Any comments entered for the order |
| Order sub total | The subtotal of the products in the order |
| Order discount total | The total of the discounts (e.g. offers, voucher codes) that were applied to the order |
| Order grand total | The grand total of the order, including delivery costs and any discounts that were applied |
| Order reference | Returns the ShopWired reference number of the order |
| Order id | Returns the unique ID of the order |
| Order transaction | Returns the transaction ID given to the order by the payment gateway |
| Order anonymized | Will return true if the order has been anonymised |
| Order total weight | Returns the total weight of the order |
| Order tracking url | Returns the tracking URL of the order |
| Order payment method | Returns the payment method used for the order |
| Order shipping total | Returns the total shipping charges on the order, after discounts have been applied (not including tax/VAT) |
| Order customer source | If using the 'how you heard about us app' this will return the value selected by the customer |
| Order earned reward points | Returns the number of reward points earned on the order |
| Order partial payment total | Returns the value of any gift cards used in partial payment for the order |
| Order original shipping total | Returns the total shipping charges on the order, before discounts have been applied (not including tax/VAT) |
| Order delivery date | The delivery date chosen for the order |
| Order tax type | Returns the type of tax levied on the order, e.g. VAT, Sales Tax |
| Order tax value | Returns the total value of tax levied on the order (a total of product and shipping taxes) |
| Order status id | Returns the ID of the status the order has |
| Order status name | Returns the name of the status the order has |
| Order status type | Returns the type that the order's status has from the default order status defined here |
| Order customer id | Returns the unique ID of the customer |
| Order customer type | Returns the type of customer, e.g. trade |
| Order billing address city | Billing address city |
| Order billing address name | Billing address full name |
| Order billing address country | Billing address country, e.g. United Kingdom |
| Order billing address postcode | Billing address postcode |
| Order billing address telephone | Billing address telephone number |
| Order billing address company name | Billing address company name |
| Order billing address address line 1 | Billing address line 1 |
| Order billing address address line 2 | Billing address line 2 |
| Order billing address address line 3 | Billing address line 3 |
| Order billing address email address | Billing address email address |
| Order billing address province | Billing address county/province, for US orders this will be replaced with the state |
| Order shipping address city | Shipping address city |
| Order shipping address name | Shipping address full name |
| Order shipping address country | Shipping address country, e.g. United Kingdom |
| Order shipping address postcode | Shipping address postcode |
| Order shipping address telephone | Shipping address telephone number |
| Order shipping address company name | Shipping address company name |
| Order shipping address address line 1 | Shipping address line 1 |
| Order shipping address address line 2 | Shipping address line 2 |
| Order shipping address address line 3 | Shipping address line 3 |
| Order shipping address email address | Shipping address email address |
| Order shipping address province | Shipping address county/province, for US orders this will be replaced with the state |
| Order shipping id | The ID of the shipping rate selected |
| Order shipping name | The name of the shipping rate selected |
| Order shipping value | The cost of the shipping rate selected, after discounts have been applied (before tax/VAT) |
| Order shipping vat rate | The VAT rate applied to the shipping cost, e.g. 0 or 20 |
| Order shipping value including vat | The cost of the shipping rate selected, including any VAT applied. |
| Product id | The unique ShopWired ID for the product |
| Product sku | The SKU code of the product (for products with variations with SKU codes, this will return the SKU code of the variation selected) |
| Product gtin | The GTIN of the product (for products with variations with GTIN, this will return the GTIN of the variation selected) |
| Product price | The cost of the product, after discounts have been applied (before tax/VAT) |
| Product title | The product name |
| Product total | The product cost multiplied by the product quantity |
| Product weight | The weight of the product |
| Product vat rate | The VAT rate applied to the product cost, e.g. 0 or 20 |
| Product comments | Any comments entered for the product when it was added to the basket |
| Product price vat | The VAT value charged for the product, after discounts have been applied |
| Product quantity | The quantity ordered |
| Product total vat | The VAT value charged for the product multiplied by the quantity selected |
| Product gift voucher | Will return true if the product ordered was a gift voucher |
| Product original price | The cost of the product, before discounts have been applied (before tax/VAT) |
| Product warehouse notes | If using the warehouse notes app, any notes entered for the product will display |
| Product reward points earned | The total number of reward points earned for purchasing a single quantity of the product |
| Product cost price | If using the gross profit report app, will display the cost price of the product if one has been entered |
| Product hs code | Displays the HS tariff code of the product if one has been entered |
| Product total sales tax | The total amount of sales tax charged on the product line item |
| Product variation text | The names of any variations selected |
| Product extras text | The names of any product extras selected |
| Product choices text | The names of any product choices selected |
| Refund ID | The unique reference/ID of the refund |
| Refund created | The date/time the refund was created |
| Refund name | The refund description entered/selected |
| Refund value | The amount of the refund |
