Have your say in Community Polls: What was/is your greatest motivation to start your own business?

Re: Shopify CSV Import Deleting Images: Here's My Solution

Shopify CSV Import Deleting Images: Here's My Solution

pauldrecksler
Shopify Partner
29 0 17

It's a known issue that importing a CSV file can break your image associations and delete your images. I learned this the hard way. In this post I'm going to share what causes it, how to avoid it, and how to fix it if it's already happened.

What Causes It

I believe we've diagnosed that the issue is caused by Sorting the columns in Excel, which puts the rows in a different order than the original Export file. Shopify's system doesn't like this, and Shopify support recommends that you DO NOT sort your Excel file. However as store owners, we know that NOT SORTING is next to impossible when dealing with a large product database. We have to be able to sort and filter in order to edit quickly. Not sorting is a non-option (and anyone who thinks otherwise doesn't know what a large product database looks like, LOL). 

Solution: Before you import the CSV file after editing, you need to "Double Sort" by Handle and then Title. Doing this makes sure that your product spreadsheet is in a Parent Product / Subvariant order - where the first instance of each handle is in the same row with the first/only instance of the product title. It doesn't matter what order the products are in at this point (or whether it's in the same order as the original Export file), as long as you follow that Parent Product / Subvariant order. 

How To Sort By Two Columns In Excel

Below is a quick recap of these instructions

1. Highlight all your data. 

2. Click Data --> Sort button. 

3. Sort By "Handle", Sort on "Values, Order "A-Z"

4. Click "Add Level"

5. Sort By "Title", Sort on "Values, Order "A-Z"

So it'll look something like this: 

Screenshot (302).png

6. Now your CSV will be ready for import to Shopify. 

Good: Notice that the first instance of each handle is parallel with the first/only instance of the product title. 

HandleTitleOption1 NameOption1 Value
iphone-12-caseiPhone 12 CaseColorBlack
iphone-12-case ColorBlue
iphone-12-case ColorPurple
samsung-galaxy-s7-caseSamsung Galaxy S7 CaseColorPink
samsung-galaxy-s7-case ColorBlue
samsung-galaxy-s7-case ColorGrey

 

Bad: Notice that instances of the handle appear above the row that includes the first instance of the title. Shopify's import system ignores all variants above the first instance of the title and subsequently breaks / deletes the images. 

HandleTitleOption1 NameOption1 Value
iphone-12-caseiPhone 12 CaseColorBlack
iphone-12-case ColorBlue
iphone-12-case ColorPurple
samsung-galaxy-s7-case ColorBlue
samsung-galaxy-s7-caseSamsung Galaxy S7 CaseColorPink
samsung-galaxy-s7-case ColorGrey

 

How To Fix Broken Images After CSV Import

Okay, so now you know how to AVOID the problem in the future, but what if you've already experienced the problem and half your images are gone? 

Shopify support (or another community member) might advise you to re-import your original CSV file, but this will not always work! You've already deleted the images, and referencing the old image file paths using your original CSV won't work because the images are no longer there. Shopify will merely skip over those lines and you'll receive an Import Error that reads something like, "Line 126-133: Validation failed: Image xxx.jpg failed to download. - file not found. Check that file can be found at your URL." Plus you'll lose all the hard work you did to your edited spreadsheet and have to do it all over again. 

So then you eventually come across this thread where @jackieiga provides the following solution: 

Hi! I resolved this with support. Even when you upload the original file sometimes that doesn't fix all the images, but in my case it did fix some so first i would recommend you upload the original untouched file. We found that for the remaining missing images the problem was that the image urls were broken. To fix this you would need to get the link from your original csv file from the 'Image Src' column (note: I would recommend that you do all edits on Google Sheets since different versions of Excel or Numbers might break the document). 

Once you have the link you need to add "/deleted" before /products/ and that will load the image. then add the new image url to the csv or add each photo manually to each product on Shopify.

Example:

 https://cdn.shopify.com/s/files/1/0121/5859/9268/products/casco-orbea-r10-mips-800x800_iKWNGsn.jpg?v...

Change to:

https://cdn.shopify.com/s/files/1/0121/5859/9268/deleted/products/casco-orbea-r10-mips-800x800_iKWNGsn.jpg?v=1616111708 

This will only work if the original image link is broken.

Hope it helps!

 This solution was super helpful (thanks @jackieiga), but it only gets you halfway there. Because if you were to add "/deleted/products" to ALL of your file paths, it would break the ones that previously worked. Well, not necessarily "break" -- but it would screw up your Import and Shopify would not recognize your Image Order (which can be a huge problem). So the solution is that you need to add "deleted" only to the broken images and not the working images.

So does that mean that you have to check each image file path one by one to see which work and which don't? I felt defeated when I reached this point in the process because I had no solution other than to manually check each image file path one by one and add "/deleted/" to the ones that were broke. For THOUSANDS of images, that sounded terrible. 

So I Googled "bulk URL checker" and came across tools that would check all of my image file paths at once and let me know which ones needed to be fixed. I ended up using Headmaster SEO Bulk URL Software which has a free license that allows you to check up to 500 URLs at once -- which was no big deal because I could just copy/paste 500 URLs at a time into their tool. The software checked all my image file paths in seconds and spit out which ones were 404 errored. I then went back and Find / Replaced ONLY the broken images with the /deleted/ version of the file paths. For example: 

OLD: https://cdn.shopify.com/s/files/1/0055/5544/5467/products/iphone-11-case-blue.jpg?v=1596128045 

NEW: https://cdn.shopify.com/s/files/1/0055/5544/5467/deleted/products/iphone-11-case-blue.jpg?v=1596128045 

This part still required some manual work, but it was nothing in comparison to having to check each URL one by one. I could have used a script or formula to automatically add /deleted/ to the broken image file paths that were output by the software, but I didn't have enough to make it worth the time to write it, so I just manually Find & Replace and would paste the broken (OLD) link as the "Find" and enter the deleted version (NEW) link as the "Replace". 

Afterwards I "double sorted" my spreadsheet using the method I outlined above, reuploaded to Shopify, and my store was back to normal with all my variant images restored, assigned to their respective SKUs, and in the order I wanted (based on the Image Order column). 

NOTE: After you upload the modified version of the spreadsheet above which includes your "/deleted/" file paths -- that spreadsheet is dead to you! Do not use it anymore. After importing, Shopify restored your images, and the images no longer contain the "/deleted/" file paths. So best is to scrap this spreadsheet and Export a new spreadsheet to move forward with. 

I hope this helps someone out there! Having my client e-mail me yesterday and say that all their variant images were gone scared the crap out of me. I thought I'd have to go back and manually upload each image one-by-one and associate it with each variant. There would've gone my week! But then I figured out this roundabout way to things rolling again and thought I'd take the time to document it for anyone who it may help in the future. Good luck!

Join my Shopifreaks E-commerce Newsletter: www.shopifreaks.com
Learn about affiliate marketing: www.shopaffiliateapps.com
Hire me to help with your store: www.ideasfocused.com

I wish you much success with your Shopify store!
Replies 5 (5)

GusGianetti
Visitor
1 0 1

¡Thanks! Saved my life.

Garotas
Tourist
5 1 1

Thanks! It's works!

SergeWebsites
Shopify Partner
5 0 6

Thank you so much! Saved me a lot of trouble. By the way, if anyone wants a python script to automatically check the urls and add /deleted if they 404, here:

 

import csv
import requests

def check_url(url):
    try:
        response = requests.head(url)
        return response.status_code != 404
    except:
        return False

with open('YOUR_FILE.csv', mode='r') as csv_file, open('updated_urls.csv', mode='w', newline='')  as updated_csv_file:
    csv_reader = csv.DictReader(csv_file)
    fieldnames = csv_reader.fieldnames
    csv_writer = csv.DictWriter(updated_csv_file, fieldnames=fieldnames)
    csv_writer.writeheader()
    for row in csv_reader:
        url = row['Image Src']
        if not check_url(url):
            new_url = url.replace('/products/', '/deleted/products/')
            if check_url(new_url):
                print(f"{url} is invalid, but {new_url} is valid")
                row['Image Src'] = new_url
            else:
                print(f"{url} and {new_url} are both invalid")
        csv_writer.writerow(row)

You need to have Python and requests installed, put this script into the same folder as your CSV, change YOUR_FILE.csv to the correct file name and run it. Takes a bit of time to complete.

bandrei
Visitor
1 0 1

You deserve a medal for sharing this information in such a detailed and well structured manner. In addition, it worked perfectly. I just saved a lot of work and stress applying your fix.

What I noticed in my case was that it was easy to determine which URLs were broken. Those were found on CSV lines that only contained the "Handle" and "Image src" column data

Dane34
Shopify Partner
1 0 0

Thank you so much for sharing. Saved the day!

I can't believe Shopify still hasn't fixed this issue. And I can't believe they don't signpost the risk anywhere