Transcript More Command Line Oracle Lab

More Oracle SQL Scripts
Make the authors, titles and titleauthor tables of the
Pubs database using the script provided
Highlight (but don’t open) authors table, got o External data
Excel, and make an external spreadsheet with the data
Excel export dialog box
Resulting Excel file
Right click on header A at top of first column and
choose Insert from the menu
Enter the text seen below into cell A2 and copy
it down (ask if you don’t know how)
Add a right parenthesis semicolon in a column
on the right-hand side
Now use Excel’s Save AS and choose Other
Formats
Use the “Save-as type” drop-down and
choose CSV
Close Excel and Open the CSV file in
Notepad
Delete the first row
Go to Edit/Replace
O’Leary!!!!!!!!!!!!! Replace single quote with two
single quotes (not a double quote). Replace All.
Replace comma with single-quote
comma single-quote. Click Replace All.
Next replace left parenthesis singlequote comma with left parenthesis
Replace comma single-quote right
parenthesis with right parenthesis
Save the file with an SQL extension
Run the script
Use a SELECT to test the script
COMMIT!!!!!!!!!!!!
Return to the Access Pubs database and save
the titles table data into an Excel file
Delete the first row and then format the
column with dates as shown below
New approach to adding single quotes: Use Excel’s CONCATENATE
function to the first argument and last arguments are double-quote
single-quote double-quote
To the same for the other Char and VARCHAR
columns but just copy the other columns. Then
copy the new data and use Paste/Special/Values
Delete the old rows above and Insert a
first and last column as before
Date column has gone crazy, reformat
it
Save as CSV, close and open ion
Notepad
Replace strange things at end with a
semi-colon
Replace any double-quotes with
nothing
Replace open-parenthesis comma with
open-parenthesis
Replace comma close-parenthesis with
close-parenthesis