CSV order exporter

Selecting the columns to include in the CSV file (and renaming columns)

Each column that can be included in the CSV file is displayed on the left hand column of the table in the 'CSV Data' section:

You can select which columns you want to include in the export by ticking/unticking the tick box for that particular row:

Each column in the CSV export will have a heading. The heading text is determined by the text set in the Heading in CSV Export File column of the table:

To change the column heading enter your new heading text in the field.

An explanation of each column and the data it contains can be found in the column definitions section of this help guide.

---

Where an order contains more than one product, you can choose to not repeat the 'order information' for subsequent lines of the same order by selecting the tick box next to 'Yes' for this setting:

'Order information' is information that is not unique to each product in an order (e.g. customer's name, address, order reference, order totals).

Once you've configured the format of the CSV file to your requirements you can move onto the next section, either opting to receive order CSV files by email or via an upload to your server.

---

If you would like to change the order in which columns appear in the CSV, add blank columns or manipulate the CSV in other ways, you must use the advanced configuration option.

Uploading the CSV file to a server (by FTP/SFTP)

The CSV file can be exported by the app automatically uploading it to a server. Please note that ShopWired cannot provide a server location for the files to be uploaded, and you will need to provision your own. ShopWired's support team cannot assist with the provisioning of your server or offer support on how to use it.

To activate the file upload feature tick the Activate FTP export tick box.

You can either upload a separate CSV file for each order, by ticking the Send a separate file for each order tick box, or a CSV file will be uploaded each day for the previous days orders.

If you do not want the file to upload if it is blank (if you had no orders the previous day), then place a tick in the box beside 'Yes' for the Don't upload a file if blank setting:

The app can connect to your server either via FTP or SFTP. Make your selection using the drop-down list under 'Protocol':

Enter your connection information (host, port, username and password) into the fields provided. If you do not know what these are, you will need to obtain them from your server hosting company or other provider.

Enter the path that the app should upload the file to in the 'Path' field:

For most servers this will be / but will be different depending on your own server's configuration and where you would like the order file(s) to be uploaded.

Once you have entered the information for your source use the test source button to make sure your connection is working correctly.

Emailing the CSV file to your designated email address

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 email/csv file each time you receive a new order (the email will take between 5-15 minutes to be delivered) or email you a single CSV file containing all order data for the previous day.

Use the 'Exporter Email Settings' section to configure your settings:

To activate the email export feature tick the Activate email export setting.

By default a single CSV file will be sent for the previous day's orders, but you can tick the Send a separate file for each order setting to receive an email/CSV file each time a new order is placed. The CSV file names will be the order reference numbers.

If you opt to receive a single CSV file each day, but you don't want to receive an email if the file is blank, then tick the Don't send an email if blank setting.

Finally, enter the email address that you wish to receive the emails to. Please note that you can only enter a single email address here; the app cannot send the email to multiple email addresses.

Export on demand

Use the ‘Export on Demand’ section to download orders from a particular date period or starting from a particular order reference:

For each export a maximum of 500 orders can be downloaded.

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

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 what columns to include in your export and what the column headings should be, as this will then be determined by the code you enter in the advanced configuration code editor.

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. Liquid is very similar to Twig (the templating language used for ShopWired themes).

---

Coding in advanced mode

The code has access to object, which is an array of orders. By iterating over this array, you will have access to the data for each order.

The order object has the same properties as specified in the ShopWired API orders endpoint. For example, using the order.total variable will return the grand total of the order.

Field names are downcased and underscored. 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

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

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, for a full list of filters read here.

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

When using ShopWired Payments, additional variables are available in advanced mode to output details about the transaction.

{{ order.transaction.id }} The transaction ID

{{ order.transaction.risk_level }} The transaction's risk level

{{ order.transaction.card.last4 }} The last 4 digits of the card used

{{ order.transaction.card.brand }} The brand of the card used (e.g. Visa)

{{ order.transaction.card.cvc_check }} The result of the CVC check

{{ order.transaction.card.postcode_check }} The result of the postcode check

{{ order.transaction.card.three_d_secure_result }} The result of the 3D secure check (blank if no check occurred)

{{ order.transaction.fee_amount }} The fee amount

Column definitions

The table below displays an exact definition of the data that will be returned for each column:

 

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