How to Import a Spreadsheet

In some cases, you may need to import your data into Order Desk, whether that be orders, updates to orders, inventory items or shipments (package tracking information). This is useful, for instance, when you have orders coming in from a source that we don't have an integration with. You might also have a large inventory that you would rather add to Order Desk from a spreadsheet instead of manually entering each item. Many people also use this feature to import shipments from their warehouse.

To import data into Order Desk, you will need to have a .csv, .xlsx, or .txt file with your data. You won't be able to simply import that data directly into Order Desk, however. Since each file will be unique per store, fulfillment service, personal preference, etc., you'll first have to create a template to match the information in your specific file so that it can be translated into Order Desk's names for each field.

Import Data Page

To access the Import Data page in Order Desk, click on Import Data under the Tools menu in the left sidebar.

If you haven't set up any templates yet, you'll be directed to the Setup Import Template page, where you can begin to create your first template.

If you already have at least one import template created, you'll be directed to the Manage Import Templates page. From here you can edit an existing template or create a new one by clicking on Add New Import Template in the top right.

Set Up Your Template

To begin, you will need either your full file or a smaller sample file with a few entries already added to it. If you are planning to upload a file with more than a few dozen lines, we recommend using a sample file of just the first few lines to create the template.

Name

Give your template a name that will be easy for you to identify later. You may end up with several templates, so naming each something uniquely identifiable will allow you to work more quickly in the future when looking at your list of templates.

Import Type

Select the import type for this specific template from Orders, Shipments, Inventory Items or Order Modify:

• Select Orders to create a template to import a file of new orders into Order Desk.
• Select Shipments to create a template to import a file of tracking and fulfillment details for orders in Order Desk that have been fulfilled and need to be updated.
• Select Inventory Items to create a template to import your product details into Order Desk so they can be synced with your orders.
• Select Order Modify to create a template that will let you update orders that are already in Order Desk. Add a fulfillment ID, order notes, checkout data, metadata, payment status or change the folder with this template.

Delimiter

Select the delimiter type of the sample file you are using (.csv files are generally Comma Separated Values). The file may have a tab, pipe or semicolon separating the values. It's important to choose the correct delimiter type based on how your specific file separates each field.

Add your file and upload it to Order Desk.

Edit Your Template: Details

Once your file is uploaded, you'll be able to configure your import template. Let's look at Details first.

Template Name

You can name or re-name your import template in this field.

Order Match Column

Templates for importing orders will have one extra setting that templates for shipments and inventory items don't have, the Order Match Column.

If your file has more than one line per order (separate lines for each item in the order, for instance), you can set the template up to read a specific column of your file to compare the data to the same column in the previous line. If the data is the same (for instance, if the Order Number is the same on more than one line), Order Desk will add the item details into one order instead of multiple orders or skipping the extra lines.

Choose which column in your file Order Desk should read to compare details from line to line from the Order Match Column dropdown, or leave it set to None if you only have one line per order.

Skip First Line

If the first line of your file is used to describe each column, you can select Skip First Line to ignore this information:

If, however, the first line of your file is data that needs to be imported, make sure to leave this unchecked:

Send Email Confirmation

A unique email address is created for each import template. This can be found just above the Import Template details:

This email address can be used to import files to your Order Desk store as long as the file matches the import template. If you email your file to this unique address, you can select the Send Email Confirmation setting in the details to receive a report on the import. This will be sent to the email address that sent the file.

Automatically Download Files From FTP Server

Another option to import data into Order Desk is to download your files through FTP. Select Automatically Download Files From FTP Server to bring up the credential fields for this option. Order Desk will check your FTP server every 12 hours. The specific timing of this check can be adjusted on the View Appointments page after you enable this setting.

Edit Your Template: Match Fields

The most important part of the import template setup process is field matching. When you upload your sample file, Order Desk will display each of the columns from your file with the sample data and let you choose which Order Desk field is the right match for each piece of information. This allows Order Desk to import your data with the right field names, and lets you import your data without changing your file format.

The Match Fields section will look something like this, though it will use the data from your own file:

The column number will be listed down the left, the First Row, with your field descriptions, is listed next. Field Setup is where you will choose which Order Desk field matches yours. Your Sample Data will be listed down the right side to help you choose which Order Desk Field should be used to match your data.

In this screenshot, as you can see, the first field is the Item Code. If you want this field included in your import template, click on the Field Setup dropdown and scroll through the available fields until you find the one that matches yours. In this example, we're looking for Order ID, which is called Code/SKU in Order Desk.

Match each field necessary. Your fields likely won't all match Order Desk's name for each, which is why this matching process is necessary. In the second column, for instance, the sample file calls it First Name, but Order Desk will need it to either be Shipping First Name or Customer First Name.

Edit Your Template: Manual Mapping

The manual mapping section allows you to set specific data in your imports. For instance, you can always set the shipping.country to “US” or the source_name to “Shopify”. Add one per line in this format: name=value for example:

shipping.country = US
source_name = Shopify

For a list of field names, refer to this guide or contact Order Desk support for help.

There are also some special mapping features that can be used in the manual mapping box. Details are given for each below.

source_id_prefix

For order imports, use source_id_prefix to set a value as the prefix on all Order IDs. For example, if you want all Order IDs to have the prefix SH, use:

source_id_prefix = SH

code_prefix

For inventory item imports, use code_prefix to set a prefix on all SKUs being imported. For example, if you want all SKUs in the import to have the prefix SH, use:

code_prefix = SH

SKU Replace 

To replace any part of a SKU on an inventory import, use the following code, replacing a and b with the characters you need to look for (a) and replace (b):

code_twig={{ code|replace({'a': 'b'}) }}

As an example, if you need to change the SKU 12 34 to 12-34 (replacing the space with the dash), you would enter this in the manual mapping field:

code_twig={{ code|replace({' ': '-'}) }}

order_id_prefix_requirement

For shipment and order import templates, if a specific prefix is required on the order number in the file for it to be added to Order Desk, use order_id_prefix_requirement to look for it in the file. For example, if a file has several types of orders in it, but only the orders with the prefix OD are meant for Order Desk, use:

order_id_prefix_requirement = OD

All other orders in the file will be skipped.

order_id_remove

For shipment and order import templates, use order_id_remove to remove a specific part of the order number when adding it to Order Desk. As an example, if a fulfillment service adds its own prefix of WHS- to order numbers before sending a shipment file back to Order Desk, remove the prefix by using:

order_id_remove = WHS-

This can be used for any part of the order number, not just a prefix.

carrier_conversion

For shipment imports, use carrier_conversion to correct a non-standard carrier code. For example, if the shipper writes FDX for FedEx shipments, to correct it when the file imports into Order Desk, use:

carrier_conversion = FDX=FedEx

N/A = BLANK

For any import type, add N/A = BLANK to the manual mapping box to remove any fields that have N/A in the file import. These fields will be left blank instead.

remove_leading_zeroes = true

To remove leading zeros from a shipment file, use remove_leading_zeroes = true to the manual mapping box.

metadata_carrier_reference_field=carrier

When importing a shipment file that does not include the carrier with the tracking details, you can pull the carrier name from the order directly if it was pre-set as order metadata by a rule.

To do this, create a rule, using the Order Rule type, and choose the event you want this rule to run on. Add filters, if necessary, then for the action, choose  Set Order Metadata Value as the action and set the Field Name to carrier and the Field Value to the carrier name you need to use:

In the manual mapping field of the import template, add metadata_carrier_reference_field=carrier to pull the order metadata value from the order. It will be added to the order shipment information when the tracking details are added from the file.

checkout_data_carrier_reference_field_name=carrier

When importing a shipment file that does not include the carrier with the tracking details, you can pull the carrier name from the order directly if it was pre-set as checkout data with a rule.

To do this, create a rule, using the Order Rule type, and choose the event you want this rule to run on. Add filters, if necessary, then for the action, choose Set Checkout Data Value as the action and set the Field Name to carrier and the Field Value to the carrier name you need to use:

In the manual mapping field of the import template, add checkout_data_carrier_reference_field_name=carrier to pull the checkout data value from the order. It will be added to the order shipment information when the tracking details are added from the file.

tracking_url_template

Set tracking_url_template=http://customcarrierurl/?tracking=XXXXXX. We will replace the X's with your tracking number and set this as the tracking URL on the shipment.

metadata_tracking_url_field

Set metadata_tracking_url_field=CUSTOM_URL and we will look in the metadata of the for the CUSTOM_URL field in order to build our tracking URL template. This is a way to use rules to set your custom tracking URL per order.

reset_stock_to_zero

Set reset_stock_to_zero in an inventory import template to have all SKUs reset to 0 right before the import runs. The SKUs from the file will then be adjusted accordingly, and all other SKUs will remain 0.

stock_subtract

When importing an inventory file, you can use stock_subtract=0 in the Manual Mapping field to have any negative quantities set to 0 on import. As long as the stock_subtract value is higher than the quantity value in the file, the quantity will be set to 0 on import.

If the stock_subtract value is lower than the quantity value in the file, the difference between the two numbers (the quantity minus the stock_subtract value) will be set as the quantity.

Import Your File

Once all the mapping is complete, you can save your import template and go back to the list of import templates. Your new template will be available and ready for you to use to import your data.

You can import your data through one of the three methods discussed earlier in this guide: uploading, emailing or FTP.

To upload your file, return to the Import Data page and, using the import template you created, choose your file and click Upload. If you want to test your file before uploading, you can select Just Preview Output first to see if there are any errors.

To email your file to Order Desk, send your file as an attachment to the email address listed just above the template details:

To download your files automatically through FTP, click on Automatically Download Files From FTP Server and configure the template to connect to a provided FTP server:

Order Desk checks the provided FTP server twice a day and will process all files it finds. After processing, the files will either be archived or deleted, based on your preference. If you would like to specify what time the files are downloaded, you can edit the appointment on the View Appointments page.

Files should be no larger than 8MB in size, and imports should not exceed a few thousand records at a time as larger file sizes can cause the request to time out.

Remove Variations and Metadata from Inventory Items

If you need to remove certain variations or metadata fields from your inventory items without deleting the entire product or going through manually to delete the specific fields from each item, you can do this using an import template and the special field value [REMOVE].

To set this up, add [REMOVE] to the field that you want to delete for each item in your spreadsheet:

Follow the steps in this guide for creating an import template (if one does not already exist for this spreadsheet), and when imported, those fields will be deleted from the inventory items, leaving the rest of the fields and/or adding any new ones based on the data in the spreadsheet.

Using the above example, the Blue Mug item had the print_sku field set as BM9903 in Order Desk:

And after importing the spreadsheet with [REMOVE] in the print_sku field, it was deleted from the item in Order Desk:

Bulk Delete Inventory Items

If you need to delete inventory items in bulk, you can import an inventory file to do so. Make sure the item SKUs are in the file, so Order Desk can find the items in your store. Set the item name to REMOVE (this must be capitalized).

When the file is imported, any SKU in the file that has the item name REMOVE will be deleted from your Order Desk store.

Check Import Results

Once your spreadsheet has been imported, you’ll see a link appear to Show All Completed Imports. Click this link to see the results of all the imports that you've done thus far.

Your imports will be listed based on the import templates that were used for each template.

Under results you’ll see three different columns. Green results have been added/updated, grey results were skipped, and red results were invalid results that have not been added. If your results are coming up red, you’ll need to check the mapping on your import template to make sure it matches up with the columns and fields on the spreadsheet you’re importing.

Copying a Template to Another Store

If you have multiple Order Desk stores and would like to duplicate a template, you can copy the template over to another store. If you're not sure how to manage multiple stores in Order Desk, you can refer to our guide here. 

When you're viewing all the templates you've created, there will be an option on the right side of the screen called Copy Template.

Clicking on this button will present you with a dropdown menu where you can select which of your stores you would like to copy the template over to, as well as offering the option to overwrite an existing template there if needed.

Once the template has been copied, you can then begin to use it in your other store and make any necessary changes to it.