1

I'm developing a web-scraping tool by Python for my own use. I will run regularly the Python program to write data to a database. I hope with not too much manipulation/script we can export data from this database into a same worksheet of an Excel file everyday.

Does anyone know which database (e.g., MySQL, MongoDB) should I choose? How can I update easily the Excel worksheet everyday?

  • All databases have third-party tools to export to CSV. And I don't think any database can inject data into an Excel file, as it is not the role of a database, it is the role of a data integration tool. – Nicolas Raoul Mar 6 at 9:48
  • It is a very rare (and boring database that can be represented well in Excel. I recommend you to forget Excel. First define your data, then structure it, then choose a database, then ask for a good free GUI to view (and manipulate) its contents - there are many. Please, choose the right tool for the job, or you will regret it. – Mawg Mar 6 at 9:58
  • I suggest you reformulate your question as: What tool can take a CSV file and put it into an Excel file's worksheet automatically?. Then you can choose your database on more important criteria such as the nature of your data (row-oriented or document-oriented) or ease-of-use. – Nicolas Raoul Mar 6 at 10:04
  • @NicolasRaoul - Python is itself a powerful data integration tool and has a number of ways to get data into Excel workbooks. I think this is better than using CSV as an intermediate format because CSV doesn't give you much control over the distinction between numeric and string data (CSV leaves you at the mercy of Excel's parsing algorithm). – John Y Mar 6 at 18:20
  • 1
    @Mawg - Databases that are represented well in Excel are certainly boring, but not at all rare. (Most databases are dreadfully boring!) I fully appreciate that Excel is not a database, but the reality is that most people much prefer using Excel over any database tool out there. You can also send someone an Excel workbook easily. Not so easy to do the same with a true database. – John Y Mar 6 at 18:29
1

The answer is: It doesn't matter which database you choose. Any major database will be able to be accessed via Python. And once you can access the data in Python, it is a simple matter to write data out to an Excel workbook using Python.

The easiest and most efficient way to write to an Excel workbook in Python is to use one of the ready-made packages, of which the leading one for your use case (updating an existing workbook) is openpyxl.

If you can adjust your workflow such that you're always creating a new workbook, instead of updating an existing one, then I strongly prefer XlsxWriter (and I use xlrd for reading data from existing workbooks).

You could also try pandas, which is a very popular higher-level package that uses openpyxl and/or XlsxWriter (as well as many other components) under the covers.

Finally, if you are doing this on Windows or Mac, and you have Excel itself installed, then another possible approach is to use Python to script a running instance of Excel. This is the most reliable way to preserve the fidelity of an existing workbook as you're updating it (particularly if you are going to incorporate fancy formatting, charts, or other advanced Excel features). The leading package for this is xlwings.

1

Instead of generating a Excel file why not connect excel directly to the database and skip the middle man.

enter image description here

  • I'm afraid that's one-go, we cannot refresh the content every day. – SoftTimur Mar 7 at 3:44
  • @SoftTimur This establishes a real-time connection to the database. Therefore, it doesn't need to be refreshed. Maybe that isn't what you want, but I thought I would offer it any way. – cybernard Mar 7 at 14:13
  • I do want it to be as real-time as possible. I will test this functionality. – SoftTimur Mar 7 at 14:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.