0

Currently, I am able to output the MySQL query result into an Excel sheet but I am looking for ways to output different queries into the same Excel sheet but into a different tab. Is it possible to do that using shell script?

My shell script currently has this line of code.

docker exec -it mysql3 mysql -uroot -ppassword project -B -e "select * from ITEMS;" | tail -n +2 > query.xlsx

Different tabs in Excel sheet

Foody
  • 177
  • 1
  • 10
  • 1
    I am a bit surprised that you generated XLSX format with this command. XLSX is a complex format. In fact, an XLSX file is usually a zipped archive containing several files, most of which are in XML format, organized in a hierarchy of sub-directories. If you really want to generate XLSX files you should consider using a dedicated tool. For instance, you could generate a 100% textual CSV file (easy with MySQL and MariaDB) and try to convert it to XLSX with, e.g., LibreOffice or Excel. – Renaud Pacalet Aug 05 '21 at 06:36
  • Another option would consist in using a Spreadsheet tool (e.g., LibreOffice or Excel), create several sheets, several rows and columns per sheet, save it in XLSX format and try to understand the result: `unzip foo.xlsx; ls -alR; ...` Once you will have a clear understanding of all this (look at the `xl/worksheets/*.xml` files), you will maybe find a way to create a XLSX from MySQL or MariaDB queries. – Renaud Pacalet Aug 05 '21 at 06:40
  • Maybe `ssconvert` can create multiple tabs. If not, have a look at the following python script, which creates multiple tabs. You may be able to adapt this to your needs. https://superuser.com/a/1549129/652023 – Socowi Aug 05 '21 at 07:13

1 Answers1

0

An alternative using mysql html output and having results on separate data blocks

mysql --html -b information_schema -e 'select COLUMN_NAME, TABLE_NAME from columns LIMIT 5;' >> sql.xlsx
echo '<hr>' >> sql.xlsx 
mysql --html -b information_schema -e 'select COLUMN_NAME, TABLE_NAME from columns LIMIT 100,3;' >> sql.xlsx

When opened with excel it could look like

enter image description here

LMC
  • 10,453
  • 2
  • 27
  • 52
  • But is it possible to split the result into different sheets just like the image I attached to my question above? >> Link to the image: https://i.stack.imgur.com/8bdD3.png – Foody Aug 05 '21 at 03:39
  • It is I think but not easy. Requires to generate the [xlsx xml structure](https://stackoverflow.com/a/56037196/2834978). – LMC Aug 05 '21 at 03:42