- 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 magical 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 in 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.
File name 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 20 MB, but Excel files are compressed, so that for sure can hold around 300 thousand rows or even more.
Columns and rows
In each sheet the data is arranged by 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 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 semicolon (;).
For 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 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 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, app didn’t know what that is.
Updating existing data
When you import the file, app tries to identify the existing item by the ID value.
If it can’t find such an item, then it tries to identify it by the Handle (or E-mail in case of Customers import).
If it can’t also find by Handle, then it assumes that it’s a new item, and it creates it.
When updating items with related items – like Product Variants, then it tries to find existing variant of the Product by its Variant ID. Or in case of Customer Addresses – by the Address ID.
If that Variant ID is empty, then it will always create a new variant.
To delete related items, just don’t include them in the update of related related items. For example, to delete one specific Product Variant, list at least Variant ID column for all the other variants you need to keep, and the ones that are not in the list, will be deleted. It’s important to remember from the other point too – if you need to update one variant of the Product, you still need to include all variants in that update, otherwise those others will be deleted.
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 only additional (by setting the Variants Add column value to TRUE).
- 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 ability to export Activation URLs. That is, if you have Customers that are not yet activated, you can export those URLs, and send mass mailing to them for example through MailChimp, with 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 it 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.
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.
Important: before importing, keep your exported file as a backup. Just in case you mess up the data, you can import the exported file to restore it back.
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.