How do you get an Excel output from a SQL query? Check this excel output file as an example
The above excel output is generated without any kind of programming. All you need is a SQL query. Continue reading to find out how..
Most of the database tools available usually have the ability to export SQL table output into either ‘CSV’ or ‘XLS’ format or both. Some of the sophisticated tools also allow you to export to html, xml and other popular formats like PDF. But the fact is everybody uses Excel and I guess it is the most popular output format of all.
All of these tools that allow you to export SQL data into excel do not have any kind of formatting associated with it. In this article we will see how to get formatted results from a SQL query into Excel.
Lets consider the following query
select employee_id, first_name, last_name, email, salary from hr.employees
This is a very simple query and the output contains 5 columns. We will use InfoCaptor’s export to excel feature to extract the formatted output with the desired cosmetics.
In InfoCaptor, there are two ways you can export the output to Excel. When you open the Qlet editor (short for Query + portlet) as below
In the above Qlet editor, you will see the property named ‘Max Rows’ which is set to a value of 10. This means that only 10 rows will be extracted from the database. There are 2 ways you can export to excel as shown below.
Fig 2 Option 1: Qlet to Excel -> When you select this option and click on the Excel icon (left to the dropdown), then whatever you see in the Qlet will be exported to excel. In this e.g it will export 10 rows to excel. Option 2: Spool to Excel -> Now we have limited the query results to just display 10 rows but in the database there could be more than 10 rows, lets say 100 rows. If we select the ‘Spool to Excel’ and click on the excel icon, all the rows from the database will be exported to excel. This operation can be utilized to export all the rows from any table but it should be done with caution.In Fig 1, once you click on the ‘Apply’ button you get the qlet display as below and once you click on the Excel export icon, you get the desired formated output. The output has exactly the same colors and font settings you selected in the Qlet Editor. Behind the scenes: Some of the colors that you see in the output are not easily derivable in Excel. By default Excel (atleast in 2000) has some 40 colors available. InfoCaptor Qlet Editor allows you to pick any color from millions and when the time comes to export, InfoCaptor creates palette entries within Excel to match the colors that you have selected.Fig 4.
As you see in the figure above, you can specify what color goes for each row. In the Qlet editor you see ‘Row 0’ and ‘Row 1’ as the properties. ‘Row 0’ corresponds to the 1st row in the display and ‘Row 1’ is the next alternating row in the display. The alternating interval is decided by the ‘Row Interval’ property. Using proper values for the row interval gives effective ‘Row banding’, for e.g you could visually group rows 2 and 3 with the same colors.Fig 5.
As shown in the above figure, you can even have individual columns painted differently than the rest of them and the excel output will be what you asked for.
This ends our short feature summary and tutorial on how to export SQL contents to excel. Is this feature useful for your current work or project?