Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Dump whole schema into Excel template (Read 4216 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Dump whole schema into Excel template
23.05.06 at 10:28:16
Dump the whole database schema into an Excel template
SQL*XL user Abdul from Quatar asked the following question.
Our requirement is to pull data from Oracle database to Excel sheets schema wise not table wise.That is an entire Oracle schema should be able to be copied to Excel sheets.And while doing so, it would be better , if  we can have the data in a predefined Excel templates instead of plain sheets.We have some Excel templates and we want to make use of them for  the conversion.
Yes this is possible.
It would be best if your template has a sheet for each table. Now, if your schema is large, creating such a spreadsheet template may be tedious to do. With a macro you can easially create many sheets. To start I suggest you get a list of tables first. You can use the SQL*XL SQL editor to make this. You can drag the tables into the SQL area and save it as a file or that you can copy to the clipboard. You can also use SQL to retrieve the list of tables. Use the all_tables database view to select the tables you want. Use select * from all_tables where rownum <= 10 to get 10 rows to see how this view looks and how to select only the data you want.
You could use a macro like this: Code:
Public Sub CreateSheets()
    Dim lng As Long
    'Create 20 sheets:
    For lng = 1 To 20
    Next lng
    Worksheets(1).Name = "EMP"
    Worksheets(2).Name = "DEPT"
    Worksheets(3).Name = "SALGRADE"
    '... etc
End Sub

The code can be written easially in Excel using the concatenate function. Suppose you have the sheet numbers in column A and the table names in column B, you can write the following formula in column C to write this code:
=concatenate("Worksheets(",A1,").Name = """,B1,"""")
Now you have a sheet for each table you can dump the table data into the template. Using a similar technique as above you can make the select statements. Make the statements like this:
select * from mytable to sheetname!A1;
Just make a big list of these and run them all through SQL*XL in one go. You can select the specify options once so SQL*XL does not keep asking for the options.
Suppose you have the table names in column B, you can use the following formula to create the SQL statements:
=concatenate("select * from ", B1, " to ", B1, "!A1;")
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1