- To import, export and update data, you work with normal Excel (.xlsx) files.
- To have your existing Shopify store data in Excel file, just export it with the Excelify app.
- If you have an empty store, create one item through the Shopify Admin and then Export it. Then you will see precisely, which fields from Shopify go to which Columns. Or if you don’t want to do that, you can download the Template file.
- To import data, prepare the Excel file in the same structure, and then import it by uploading it in the Excelify app.
- After you do the import, you are offered to download the Results File, which contains all your import data, and two additional columns that show what happened to each item.
- If you need to import from the file format which you don’t know how to prepare, or you ever get confused about anything, contact our Support.
Sheets inside Excel file
One Excel file can hold several kinds of Shopify data items like Products, Customers, and so on.
Inside the Excel file, there are sheets (tabs at the bottom). Sheet names tell the app what kind of data is inside each sheet.
Recognized sheet names are:
- Smart Collections
- Custom Collections
Sheets must be named precisely as that – also big/small letters matter, so be sharp when giving those names, or better just use the exported file, or the template.
All the other sheets are ignored, so you can keep your reference data sheets also in the same file.
Filename doesn’t matter, name it as you need. To be able to import that file, it must be real Excel (.xlsx) file and must contain at least one sheet with the proper name that app can recognize.
Maximum file size that you can upload is 100 MB, but Excel files are compressed so that for sure can hold any data you need.
Columns and rows
In each sheet, the data is arranged in columns and rows.
Those columns are basically the same data fields as you fill through Shopify Admin. For example, Product columns would be: Handle, Title, Vendor, and so on.
If you don’t have data for that column, you can leave it empty, and it will just be set to empty.
When you are updating data, sometimes you want to update just one field, like for example, product price. In that case, you can remove all the other columns, and the app will update only this one field, and leave other fields unchanged.
Rows correspond to items in Shopify. Basically, one row is one item in Shopify (except, when you need more rows for one item).
Values in cells correspond to appropriate field value for that item. Sometimes values are text, sometimes numbers, sometimes TRUE or FALSE (which means yes or no).
Usually, when you enter that data in Excel, it will automatically understand, what is a text, what is a number, and what is TRUE or FALSE.
Items that need several rows
For example, for Products, you might need several rows per product, if the product has several images or several variants. For Customers, you can have several rows, because one Customer can have several addresses. Smart Collections can have several Rules. And so on.
In case of Products, each new variant and each exported image will go in its own row. However, you can import several images by splitting them with the semicolon (;).
For the app to know that the row belongs to the same item, it needs to have either the same ID value, or the same Handle value (if ID is empty), or the same Title (if Handle is empty).
The ID is the unique number that Shopify generates for each item. When you create new items, leave it empty – Shopify will generate them for you. The ID is used when you need to update existing items – this ID will allow the app to identify which item you need to update.
When you repeat rows, you can just copy them down with all the values also for those columns, where only one value per item is needed. For example, if you will copy the same Title value for all the Variants rows, the app will use the only value from the first row and will ignore the rest.
You can tell by the column color and by its starting name that it belongs to repeated row. For example, all variant columns start with “Variant …” and are the same color.
When importing, the file must be sorted so that repeated rows for the same item are together (sorted by ID, Handle, or Title). Because, as soon as ID or Handle, or Title will change, it will be treated as next item.
Columns have colors, and those are visual indicators for you how those columns group logically together.
When viewing import results, it will also color those columns. The columns that have white color means that those were ignored, the app didn’t know what that is.
In each sheet, you can use the “Command” column, by which you can tell, what to do with the row that is being imported:
The possible commands are:
- NEW: will try to create a new item with the provided details. If such item will already exist (with the same ID or same Handle), then the result will be Failed.
- UPDATE: will try to find existing item by ID or by Handle, and update it with data from the row. If such item will not be found, then it will create new.
- REPLACE: at first, if such item with provided ID or Handle exists, then the app will delete it. After that, it will create a new one. If an existing item will not be found, then still the app will create a new one. The difference from UPDATE is that it will not keep any of columns, which were not included in the import file since the item will be deleted and re-created.
- DELETE: the app will delete the existing item, which is found by ID or Handle. If such item will not be found, then the result will be Failed. This DELETE command is very handy for deleting items in bulk.
If no Command is specified, then the app will assume the UPDATE command.
When updating items with related items – like Product Variants, then it merges your variant rows with existing variants, by find existing variant of the Product by its Variant ID, then by SKU, then by Barcode, then by Option values.
To delete Variants, use “Variant Command” column with value “DELETE”. Or put “REPLACE” if you want to replace all variants of that product to your new variant rows.
The same principle works with Product images. By default, it merges existing images with your image links, but you can DELETE or REPLACE images too.
This stuff is powerful!
- You can update all images of all Products at once.
- You can link images to Product variants all at once.
- You can add new variants without having to list the existing variants – just import the additional variant rows, and the app will merge them automatically.
- You can import, export and update Metafields by having them just as additional columns. You can have unlimited additional columns of Metafields.
You can export any of those items: Products, Smart Collections (Automated Collections), Custom Collections (Manual Collections), Customers, Discounts.
If you have Metafields, you can click the checkbox to include those too. Just be aware that Metafields export takes longer time.
Custom Collections allow you to export Linked Products, which means that in Custom Collections sheet you will have additional columns with Product ID, and Product Handle and each collection will have as many rows, as there are products in that Collection.
Customers have also an ability to export Activation URLs. That is, if you have Customers that are not yet activated, you can export those URLs, and send a mass mailing to them for example through MailChimp, with an individual link for each Customer to activate their account.
As you click around, the time estimates change, so you get the approximate idea, how long the export will take.
Once you start your export for real, it will recalculate estimates based on your actual items.
You can import the same kind of files that you get from Export.
If you have Shopify CSV file, you just need to save it as Excel file and name the sheet as “Products” and you are good to import it. Most of the columns will just match. If you don’t have Excel, you can open CSV file in Google Spreadsheets, and then go to File -> Download as -> Microsoft Excel (.xlsx).
Once you upload that Excel file, first it will analyze the file, count the items in that file and show approximate estimate. When it says “Ready to import”, you can press the “Import” button and it will start importing.
As it imports, it will show how many items are New, Updated, or Failed. It will also calculate more precise time estimate as it moves on – from the real speed of your items.
If you notice that you get too many Failed, or just that you forgot to add something, you can cancel the import any time. When you cancel the import, you will have a chance to download the Results file. It will contain only those rows, which were imported. That way you can review, what was imported, what were errors. And that way you know, which items it actually imported, so that if you need to continue, you can exclude those already imported items from the next import.
Import Results File
When import will be finished, you will be able to download the full Import Results Excel file, which will tell you precisely for each row – what was the import result, and the comment about it.
The “Import Comment” will always start with one of the following texts, where it will indicate, what command it actually did for the item, and how it found it (in case of updates):
|UPDATE: Pushed by ID||If you see this, it means you did the best job in helping the app to identify the existing item by ID, and it was updated with just one simple push request.|
|UPDATE: Found by ID (pre-loaded)||Here the app searched for the existing item by ID in the pre-loaded items list, because it can’t push it, it needs additional data about the item to be able to do the update. You can improve this by removing columns, which you don’t need to update.|
|UPDATE: Found by ID||The same reason as for pre-loaded, just app decided that in this case searching items one by one is faster than pre-loading all existing.|
|UPDATE: Found by Handle (pre-loaded)||Either you don’t have the ID, or the item with such ID doesn’t exist in this store. But it exists with the same Handle (or Code in case of Discounts, or Email in case of Customers). Also, app decided that pre-loading items will be the fastest way to find them.|
|Found by Handle||The same reason as for pre-loaded, just app decided that in this case searching items one by one is faster than pre-loading all existing.|
|Found by SKU (pre-loaded)||For Products, when Product is being identified by Variant SKU – it will always be pre-loaded, because app will essentially load all the store into memory, to know, which SKU belongs to which Product.|
|NEW||The new item was created – meaning, that it didn’t find any existing item by ID or by Handle.|
Read more about improving import speed in the tutorial: How to maximize Shopify bulk import speed?
Then, if the item import will be Failed, then the same line will be followed with detailed reason, as much as is known, of the error that happened to it.
Maybe strange, but really useful is that each Product will have the same “Import Comment” value, so when you read them, consider them about the product as a whole, not about each line. The convenience here is that in case you need to filter rows by the import comment, you can easily filter them in Excel, and know that your filtered results will contain all the rows of that product, because “Import Comment” column value is the same.
Test Your Imports
Before importing the whole file, import one item to make sure you have set everything as you need.
Once you made your test import, and results are as you need, only then import the whole file.