SQLite Forum

Can you export multiple tables into ONE excel file on ONE tab?
Login

Can you export multiple tables into ONE excel file on ONE tab?

(1) By anonymous on 2020-08-13 05:20:23 [link] [source]

Does anyone know if this is possible at all? I am not talking about merging, appending, or combining different tables, what I mean is exporting multiple different tables on only one tab in an Excel file, rather than an individual tab for each table.

I've been doing a search and don't see any solutions to do this from an SQL database to an Excel file (though it is possible to do this from an Access database, but that is not a good option for what I am trying to accomplish).

(2) By Gunter Hick (gunter_hick) on 2020-08-13 10:21:38 in reply to 1 [source]

.mode csv
SELECT <fieldlist1> FROM <table1> UNION ALL
SELECT <fieldlist2> FROM <table2> UNION ALL ...

while making sure that all field lists are the same length (add the appropriate number of NULL literal values to extend "short" fieldlists).

Maybe add selects from the column names before each table's data.

This strikes me as more of a presentation layer problem. See https://en.wikipedia.org/wiki/Microsoft_Excel#File_formats for details of the XML format.

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1" x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">Name</Data></Cell>
    <Cell><Data ss:Type="String">Example</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

While it is in principle possible to build such an XML with SQL queries, it will be tedious and error prone.

(3) By Adrian Ho (lexfiend) on 2020-08-13 12:21:00 in reply to 2 [link] [source]

Maybe add selects from the column names before each table's data.

Or just add .headers on after .mode csv.

(4) By Keith Medcalf (kmedcalf) on 2020-08-13 13:13:01 in reply to 1 [link] [source]

An Excel file is simply a data file (though, being a Microsoft file format, it contains executable data, which is antithetical to any sort of security whatsoever).

Since an Excel file is merely a data file, an application can do whatever it pleases such as reading and writing to any "cell" on any "sheet" in the file. This is a function of the application generating or working with the file.

For example, there are (at least) two libraries for Python (that I know of and use) -- openpyxl and xlrd -- that will let a python application have its way (as it were) with Excel files. Given that an Excel file is nothing more than a data file, there are most likely libraries written in other languages to permit one to have their way with Excel files.

If you mean can that piece of software known as "Microsoft Excel" do this, that is something that you will have to take up with Microsoft.

SQLite3 (the library) does not work with Excel files. It works with SQLite3 database files and provides an API so applications can use the database files. There are "extensions" which can import and export CSV files, but none that work with Excel files directly.

One such Application Program is the SQLite3 shell. It is designed for convenient and generic command line access to SQLite3 database files. It has some rudimentary I/O capabilities for dealing with the console and importing and exporting data in various formats such as CSV. It does not work with Excel directly.

So, your question is somewhat unclear because yes, an application running on a general purpose computer can do whatever your little heart desires limited only by your skill in defining and writing the application.

So the answer is yes. An application can be written that does what you want. Has it been done? Yes it has (I wrote one, for example, but you cannot have that one). Can an application that is freely available to you that you did not write do that? No idea. Google is your best buddy in that case. Can SQLite3 do that? No. The purpose of SQLite3 is to work with SQLite3 database files. You might write an application that does that that also uses the SQLite3 library to interface with SQLite3 database files, however, that is the business of the application and not of SQLite3.

(5) By Larry Brasfield (LarryBrasfield) on 2020-08-13 15:57:02 in reply to 1 [link] [source]

As Keith says, your objective can be reached by writing a program. There are numerous libraries for creating Excel spreadsheet output, adapted to a wide range of programming languages. (Perl, Go, C#, Java, C, ...)

There are also some applications intended for "data integration", to shovel and transmogrify data in many ways between many data sources and sinks. For example, Pentahoe's Kettle (aka PDI). It's been awhile since I looked into them, but their capabilities seem to keep growing in response to customer demand (such as yours.)

(6) By Tom Black (TomBlack) on 2020-08-21 16:16:48 in reply to 1 [link] [source]

Yes, We Can export multiple tables into ONE excel file on ONE tab via ZetExcel.

(7) By romankris on 2021-03-30 06:18:41 in reply to 6 [link] [source]

you can do this using OLEDB in C# excel

http://csharp.net-informations.com/excel/csharp-excel-export.htm