Saving scraped documents in two sheets in an excel file

Question

I've created a scraper which is supposed to parse some documents from a webpage and save it to an excel file creating two sheets. However, when I run it, I can see that It only saves the documents of last link in a single sheet whereas there should be two sheets with documents from two links properly. I even printed the results to see what is happening in the background but i found there nothing wrong. I thing the first sheet is overwritten and second one is never created. How to get around this so that data will be saved in two sheets in an excel file. Thanks in advance to take a look into it.

Here is my code:

import requests
from lxml import html
from pyexcel_ods3 import save_data

name_list = ['Altronix','APC']

def docs_parser(link, name):   
    res = requests.get(link)
    root = html.fromstring(res.text)
    vault = {}
    for post in root.cssselect(".SubBrandList a"):
        if post.text == name:
            refining_docs(post.attrib['href'], vault)

def refining_docs(new_link, vault):
    res = requests.get(new_link).text
    root = html.fromstring(res)  
    sheet = root.cssselect("#BrandContent h2")[0].text   
    for elem in root.cssselect(".ProductDetails"):
        name_url = elem.cssselect("a[class]")[0].attrib['href']
        vault.setdefault(sheet, []).append([str(name_url)])
        save_data("docs.ods", vault)

if __name__ == '__main__':
    for name in name_list:
        docs_parser("http://store.immediasys.com/brands/" , name)

But, the same way when I write code for another site, it meets the expectation creating different sheets and saving documents in those. Here is the link: https://www.dropbox.com/s/bgyh1xxhew8hcvm/Pyexcel_so.txt?dl=0

Answer 1

Question: I thing the first sheet is overwritten and second one is never created. How to get around this so that data will be saved in two sheets in an excel file.

You overwrite the Workbook File on every Link that's be appended.
You should never call save_data(... within a loop, only once at the End of your Script.

Comparing you Two Scripts there is No difference, both behave the same, again and again overwriting the Workbook File. Maybe the File IO get overloaded as you doing more than 160 Times overwriting the Workbook File within a short Time.

The First Script should create 13 Sheets:

data sheet:powerpivot-etc links:20
data sheet:flappy-owl-videos links:1
data sheet:reporting-services-videos links:20
data sheet:csharp links:14
data sheet:excel-videos links:9
data sheet:excel-vba-videos links:20
data sheet:sql-server-videos links:9
data sheet:report-builder-2016-videos links:4
data sheet:ssrs-2016-videos links:5
data sheet:sql-videos links:20
data sheet:integration-services links:19
data sheet:excel-vba-user-form links:20
data sheet:archived-videos links:16

The Second Script should create 2 Sheets:

vault sheet:Altronix links:16
vault sheet:APC links:16