How to put SQL output into a spreadsheet

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Product: Aleph
Product Version: 20, 21, 22, 23
Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care

Question
How to put SQL output into a spreadsheet?

Answer

1. The following command should be placed in the SQL script prior to the "spool" command:

SET MARKUP HTML ON SPOOL ON

Then the .lst output file produced by the spool command will be in HTML format.

2. Place the file produced by step #1 in the library's $data_print directory.

3. In the GUI Task Manager -> File List -> Print Configuration options, select "Browse HTML".

4. Highlight the report and click the "Print" button.

5. Task Manager will open a new window with the report displayed as HTML tags

6. Right click in this "preview" window and you should see an option to "Export to Microsoft Excel".

7. Click on the "Export to Microsoft Excel" option after right-clicking in the browser window with the report

8. Several options within Excel will appear as it imports the report. Select the defaults presented.

9. Note: If you don't see the "Export to Microsoft Excel" option, then highlight or do "select all" for the entire report, then "copy", then paste it into an empty Excel sheet.

10. The report data will be parsed into separate rows and columns in the Excel spreadsheet. You may have to do some cleanup to remove extraneous columns that are part of the HTML file but aren't needed in the spreadsheet.

Additional Info

Article " Report of items which have never been checked out (for weeding) " describes the use of a delimiter between each SQL column.

Article " XSL: Create Catalog-records-columnar text delimited output " is a similar article for putting XML-format output (such as that produced by Aleph GUI Services) onto a spreadsheet.

Article last edited: 28-Feb-2016