How to Import a Spreadsheet
IN THIS GUIDE
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.
This guide will show you how to create an import template and then use that template to import your data.
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.
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.
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.
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.
You will need to create a new template or update your existing one if your file layout or number of columns changes. Each template will only work when importing data from a file that matches the exact same columns in the exact same order as the template was created to receive.
Edit Your Template: Details
Once your file is uploaded, you'll be able to configure your import template. Let's look at Details first.
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 Order ID. If you want this field included in your import template, click on the Field Setup dropdown where it says --Skip-- 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 convenient because Order Desk calls it the same name.
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.
When a file has multiple tracking numbers in one row for an order, Order Desk will only accept the first tracking number to prevent any upload errors. As an example, if Order #1234 has three tracking numbers listed under the Tracking Number field (trackingnumber1, trackingnumber2, trackingnumber3), only trackingnumber1 will be imported.
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.
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
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
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.
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.
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.
If the dates in a file aren’t formatted properly for PHP, for instance if it contains forward slashes like in d/m/Y, this can be fixed by Order Desk on import to adjust it to the standard Y-m-d H:i:s format to prevent errors with displaying the correct date.
To set an import template up for this conversion, you will need to tell Order Desk what the format in the file is by setting date_format= in the Manual Mapping field. Using d/m/Y H:i:s as an example:
Order Desk will look for that format in the file and convert it to Y-m-d H:i:s.
This setting also requires the date to be set to Order Date (Local Time) when creating the import template:
remove_leading_zeroes = true
To remove leading zeros from a shipment file, use
remove_leading_zeroes = true to the manual mapping box.
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 and use carrier|xxx, where xxx is the carrier name:
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.
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=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 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.
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.
Item Code/SKUs will overwrite themselves when being added to Order Desk if they share the same code. Please make sure each of your items have unique SKUs for all items to successfully import.
Remove Data from Inventory Items
If you need to remove certain 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
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.