Creating folder structure from .xlsx

Question

I’ve got an .xlsx file with a certain structure (check the image please) and I would like it to process the containing information to a certain folder structure via mkdir, the only thing I have is an .xlsx and the name of the folder is a String from the first 3 contens of the .xlsx (maybe I must work with VBA?):

At the end of the process, there should be 3 new folders with a picture and .txt file inside

Here it is what it sould look like in the end --> workflow and folder structure

The URL of an .png should be dropped into that created folder, as well as one single .txt file containing the two ProdDec (Production Description) and Collection content.

I have python 3.6.x installed and PowerShell is also on my win7 64x machine.

Many thanks

Rainer Zufall

Answer 1

You can read in your Excel file using openpyxl, gather the appropriate information, and then create a directory for each product. The images can be downloaded using the requests library. Below is a tested solution to your problem:

import os

import openpyxl
import requests

wb = openpyxl.load_workbook('A1.xlsx')

sheet = wb.active

rows = [tuple(cell.value for cell in row if cell.value is not None) for row in sheet] # convert the cells to text

dirnames = list()
images = list()
text = list()

for row in rows[1:]: # [1:], ignore column headers for better looping
    if row[0] is not None:
        dirnames.append('_'.join(row[:3])) # joins the Brand, Family, and Ref columns
        images.append(row[3:-2]) # stores the uris in a tuple
        text.append('\r\n'.join(row[-2:])) # joins the last two columns

for i in range(len(dirnames)):
    if not(os.path.exists(dirnames[i])):
        os.mkdir(dirnames[i]) # create the dir
    os.chdir(dirnames[i]) # enter the dir
    print('creating', dirnames[i])
    for j, image in enumerate(images[i]): # for all of the images
        imagereq = requests.get(image)
        imagename = 'Img{}.png'.format(j + 1)
        if imagereq.status_code == 200: # prevents filewriting errors for bad requests
            with open(imagename, 'wb') as fp:
                fp.write(imagereq.content)
            print(' ' * 4 + 'image write successful for', imagename)
        else:
            print(' ' * 4 + 'could not download image {}, error'.format(imagename), imagereq.status_code, imagereq.reason)
    with open('ProdDesc_and_Collection.txt', 'wb') as fp:
        fp.write(text[i].encode('utf8'))

    os.chdir('..') # back out of the dir

UPDATE: Code now works for multiple images per product and ignores empty cells