Import from SFTP to Shopify

SFTP to Shopify

This is now to Import data from an SFTP server to Shopify

You can import to Shopify from SFTP by pointing the address exactly to the file which you need to import.

To schedule the import automatically at regular time intervals, do the following.

In the Excelify app, in the Import section:

  1. Paste the link to your SFTP server file path which should be in the following format:
sftp://user:[email protected]:port/path/to/directory/file-name.xlsx

For example:

sftp://foo:[email protected]:22/test/my-file.xlsx

2. Press “Upload from URL” buttonImport to Shopify from SFTP - upload file

3. Wait for the app to download the file for the first time, and let it analyze it (or fix any issues if it says that the URL was not correct).

4. If you want to set up the scheduled or automatically repeated import, press the “Options” button and set it up there.

5. Press the “Import” button (or “Schedule Import” button if you are scheduling import to start at a certain time).

Schedule automated import from SFTP to ShopifyEach time when the next automatic import will run, it will download the fresh file from SFTP again and again.

Good to know

Export from Shopify to SFTP server

Shopify to SFTP

This is how to Export from Shopify to an SFTP server

In the Excelify app, in the Export section:

  1. Choose what data you want to export with checkboxes.
  2. Press the “Options” button
  3. Select the scheduling and repeating options if you want to repeat the export automatically.
  4. On the “Upload to:” line, choose the SFTP.
  5. Enter the SFTP link in the following format:
sftp://user:[email protected]:port/path/to/directory/

For example:

sftp://foo:[email protected]:22/test/

Don’t put the file name or file extension in that path – as this is generated automatically.

If you want your file name to be custom, then fill the field “Custom file name:” in the Options section.

Export from Shopify to SFTP - Options

Good to know

Repeat import from your supplier data feed using Google Sheets

Repeat import from supplier data feed to Shopify

Here you will see an easy trick how you can import to Shopify from any of your supplier files or data feeds.

Set it up once, and then repeat it easily manually or even automatically at any time intervals.

Short summary:

  1. Set up the new Google Sheets document.
  2. Make two sheets there: “Data” and “Products”
  3. Import your original file from your supplier to the Data sheet.
  4. Make the formulas in the Products sheet to take the data from Data sheet.
  5. Import

Now, each time you get the new file from your supplier, you can just re-import it in the Data sheet and your Products sheet will update.

Also, if your supplier gives you the link to download your file, you can use the Google Sheets formula =IMPORTDATA(…) to import that file automatically from the supplier link.

If your mapping from your supplier file is straightforward, you can use the =QUERY(…) function to grab the data from Data sheet, just assign the names to the column using the LABEL clause in the QUERY function.

Good to know

  • You can achieve also the same effect with Excel file – use the =WEBSERVICE function (works only on Windows).
  • To do it semi-manually, you can just copy-paste the data from your supplier into your “Data” sheet – and the rest will work the same.

How to adjust the Inventory Quantity for Shopify Products

Adjust Shopify Inventory Quantity

You can now import the quantity adjustment of the Shopify Product inventory by importing the delta (difference) number.
So, in case you received a new shipment to your warehouse of additional x items for each product, you can just import that x number as an adjustment for your products and variants.

There is the new column in the Products sheet for that “Variant Inventory Adjust“.

If you are importing the “Variant Inventory Adjust” values then, of course, you cannot import the “Variant Inventory Qty” number – as that wouldn’t make sense to set and adjust the quantity at the same time.

For example, if you need to adjust the inventory quantity for some products, you can import the columns:

  • ID
  • Handle
  • Variant ID (or Variant SKU, or Variant Barcode, or Options columns)
  • Variant Inventory Adjust

or, if you want to just update by SKU, you can import the following columns:

  • Variant SKU
  • Variant Inventory Adjust

And you can combine the columns – fill the values for some variants in “Variant Inventory Qty” and for some the “Variant Inventory Adjust”.

Here’s one way the import file can look:

Shopify Adjust Variant Inventory Quantity import

Read more about the columns in the Products Columns Documentation.

Archive and Unarchive Shopify Orders in Bulk

Archive Shopify Orders in Bulk

To Archive existing Shopify Orders, do the following steps:

Note that this will not replace the orders – this will truly update the existing ones.

  1. Export the Orders you want to archive – don’t include any other detailed columns unless you absolutely need them, so you have nicely one row per order in the export.
  2. Delete all the columns and rows, leave only the following columns in your Excel file:
    • ID
    • Name
    • Command
    • Closed At
  3. Update the Excel file columns with data:
    1. Set the “Command” column values to “UPDATE“.
    2. Set the “Closed At” column values to whatever date (it doesn’t matter what date it is because orders will anyway get archived with today’s date).
  4. Import the file with the orders you want to archive.

To UnArchive existing archived Shopify Orders, do the opposite steps to archiving:

  1. Export the Orders you want to unarchive.
  2. Delete all the columns and rows, leave only the following columns in your Excel file:
    • ID
    • Name
    • Command
    • Closed At
  3. Update the Excel file columns with data:
    1. Set the “Command” column values to “UPDATE“.
    2. Set the “Closed At” column values to empty.
  4. Import the file with the orders you want to unarchive.

The Orders which have the Closed At value as empty, will get unarchived.

Good to know

Migrate Blog Posts from WordPress to Shopify

Blog Posts - WordPress to Shopify

This video tutorial shows you step by step instructions how to migrate your Blog Posts (Articles) from WordPress to Shopify Blogs.

Steps to migrate articles from WordPress to Shopify:

  1. Go to your WordPress site, and install the Plugin: All Export.
  2. Export all your WordPress blog posts using “All Export” plugin to the CSV file.
  3. Open the CSV file, save it as Excel file.
  4. Rename the Excel file sheet (tab) to “Blog Posts” so that the Excelify app knows what’s inside that sheet.
  5. Set the column titles of the columns you want to import – copy column names from the Excelify template file.
  6. Import the new Excel file to Shopify using the Excelify app.

 

Clone Shopify Blogs, Blog Posts (Articles) and their Comments to other store?

Shopify Blog Posts - Clone

This video will show you how to copy all your current Blogs, Blog Posts (Articles) and their comments to your new store.

This is needed when you are migrating to a new Shopify store, or when you need to set up the test/development store and want to use the data from your real store.

Summary of steps to clone your Articles:

  1. Install the Excelify app on both stores – old and the new one (get the second store for free, remember that you don’t have to pay for two stores here).
  2. Go to source store, select to export all Blog Posts, Blogs, Comments and Metafields (if you have those).
  3. When export is finished, download the exported Excel file.
  4. Go to the new store and upload the exported file.
  5. Press “Import”.
  6. Wait for import to finish.

Done!

 

How to import large Shopify CSV file with the Excelify App

Import large CSV file to Shopify

Your supplier has given you the Shopify CSV file for importing, just there is one small issue – that CSV file is pretty large, but Shopify limits the import of CSV files to 15 MB.

The Excelify App comes to the rescue – with it you can import the same Shopify CSV file up to 2 GB in size.

All you need to do is:

Import Shopify CSV file with the Excelify app

1. Open Excelify application. Scroll to the Import section. Upload your Shopify .csv file:

Import csv file into Shopify with Excelify

2. Press Import and take a nap:

[image of the import progress]

3. Wake up. Products are imported:

Import Shopify csv with Excelify app

You may also compress the file, if needed and upload and import .zip file:

Import compressed Shopify CVS file

Good to know

  • You can zip that CSV file to make it smaller – and upload the .zip file.
  • Since Excel format has a limit of about 1 million rows, the Excelify has also its own “Excelify: CSV” format to work with really large data – with the same columns as “Excelify: Excel” format. You can export and import that format the same way as you do with Excel files. For more details about exporting to “Excelify: CSV” – follow this tutorial.
  • Read about all the formats you can import and export.

Rearrange Product Variant Positions

Shopify Product Variants

To rearrange Shopify product variants:

  1. Export Products + Inventory / Variants (without images or Metafields)
  2. Find the column “Variant Position” and change the numbers as you like them to be. (no need to change the row order, just change the numbers)
  3. You could just import that same file back but it will update all the columns that you import. So, to make the import smaller and quicker, and to update just the positions and nothing else, you can select just the following columns:
    • ID
    • Handle
    • Variant ID
    • Variant Position
  4. Copy those columns to new empty Excel file.
  5. Set the name of the sheet “Products” (so that the app knows that those are the Products data there).
  6. Import that file. It will just update the variant positions of those variants.

You can, of course, import only the product rows that you really want to change. No need to import all the products back.

See the full tutorial in the video.

How to remove duplicates from Shopify store

Remove duplicates

To see how to remove duplicated Products from your Shopify store, watch this video or read on.

How to remove duplicate Products from your Shopify store?

So you have this store with your products duplicated for some unfortulate chain of events.

Shopify store with duplicate products

1. Export your Products to Excel file – without any variants or images, just plain Products

This will give you one row per product.

Export only Products from Shopify

 

2. Sort your exported Excel file by two columns at the same time: Title, Handle

Take your exported file, open it.

Use the Excel “Sort” feature to sort by two columns at the same time:

  • Title
  • Handle

This will make all the same titles get together one after the other. And then it will sort those same Titles by Handle – in such a way that your correct product will be the always the first one.

If you have another reason for duplication and you don’t have equal titles then you still can find other factors that could allow you to sort all the rows so that the correct one is always the first one.

Sort exported Products by Title and by Handle

3. Make the formula that will show TRUE for duplicated row of the same title, and FALSE for the first one

Now that all your duplicate rows are nicely sorted together with the first row as the correct one, you can proceed to mark – which ones you want to keep, and which to delete.

Add new column with name “Duplicate?” and put the following formula in the row 2 there:

=IF(D2=D1,TRUE,FALSE)

Excel Formula to mark the duplicates

Copy this formula all the way down.

4. Filter Excel rows to show only the duplicate rows

Set the filter to keep only those rows where “Duplicate?” equals TRUE.

Filter Excel to show only duplicates

5. Copy the columns ID, Handle and Command to the new clear sheet

Select the A, B, C columns and copy filtered values to a new clear file.

Then set the name of the sheet to “Products” so that the app knows that those are Products in this sheet (double-click the “Sheet1” to rename it).

And then change the Command column value to “DELETE” for all those copied products.

Result should look like this:

Copy duplicate products to new Excel sheet and set to delete

6. Import that file with Excelify app

Upload that file to Excelify app and press “Import” button.

Watch your duplicate products get deleted.

Excelify results of deleted duplicates

See in your Shopify Admin – and be pleased by the result that all the duplicates are now gone, and you have just one copy of each product.

It should look like this now:

Shopify store with duplicates removedGood to know

  • You can de-duplicate also Collections, Customers, Pages, Orders, or anything else – the same way.
  • If you have the export file with Variants, just first copy your ID, Handle, Command, Title columns to new sheet and use Excel function “Remove Duplicates” – then you will have just one row for each product. And then you can use this tutorial from there.
  • If you have more advanced situation, our Support can help you remove duplicates.