SQLCI: Select statement ouput to excel sheet. How?
bandi
Can anyone tell me, any way or easiest way of capturing a SQLCI
'SELECT' statement output into a Microsoft Excel Sheet?
Waiting for your valuable inputs.
Thanks,
Sateesh.
wbreidbach
In fact there are several ways to do that.
The "conventional" one: Write your output to a file and import that
file into excel. Maybe it would make sense to put commas or semicolons
between the values. If you open that textfile with excel, excel will
guide you through the rest of the process.
I am using another way to access SQL-tables from office products: I
have installed ODBC/MX (it works with ODBC/MP, too). For using ODBC/
MX, also known as MXCS, you need to install SQL/MX. You create a user
catalog within SQL/MX and create an SQLMP ALIAS for your table.
In addition you have to install the ODBC/MX driver on your PC. You set
up an ODBC connection to your NonStop and you are able to access your
table directly from excel, access and so on.
If you want to do this once, use the first way, it is much faster. If
you want to do it again and again, use the other one. You will need
several hours to do the setup but you will save lots of time in the
future.
Keith Dick
I believe others have done this by making the SELECT write the output in the form of comma-separated values.
You can put commas between the values in the output line like this:
SELECT col1,",",col2,",",col3 FROM table ...
If some of the columns are character columns and the values of those columns might contain commas, you can include quote marks in the select list. Suppose col3 is a character column:
SELECT col1,",",col2,",""",col3,"""" FROM table ...
or
SELECT col1,',',col2,',"',col3,'"' FROM table ...
You may want to include a SET STYLE HEADINGS OFF; to keep the SELECT from including headings every 60 lines.
If the default way of formatting some of the values is not suitable for Excel, you may have to use an AS clause on some columns in the select list to control how the values are formatted.
Use the OUT_REPORT command to direct the output of the SELECT command to a file, check the file to be sure there are no extraneous lines at the beginning or end of the report, use your favorite file transfer method to transfer that file to your PC, then open it with Excel, telling Excel that the file contains comma-separated values.
I have never done this myself, so there might be some details I am overlooking, but I believe the general approach is correct.
bandi
Thanks for your valuable inputs.
Right now, i will use the first approach, as it takes less time. I
will try to configure the ODBC thing, when I get somt time!!
Comp.sys.tandem rocks !!!
bandi
Thanks for a detailed explanation.
Will update after i do this.
wbreidbach
>
> - Zitierten Text anzeigen -
Usually ther is no need to insert commas as excel as long as the
textfile has a fixed format.
TEP
IIRC from a previous project there are potential line length problems,
as SQLCI still lives in the world of black&white cathode ray tubes and
matrix dot printers.
The OP doesn't say much about his environment, but a web-based
Apache-Tomcat solution might be more flexible. You can generate Excel
pages for direct download by defining the appropriate HTML headers.
TEP (retired).
Rich S
I don't know if this will work for you but I use an old, unsupported
Compaq product called ATP SQL for this purpose. It's from 1999 but it
still works fine in our non-Integrity, SQL/MP environment.
It has a server component that runs in Guardian and a web-based
client. You can use a query wizard or enter the select statement
manually and save output to a file in a variety of formats including
comma-separated values.
I don't think it can be downloaded from HP's site anymore but if
you're interested, send me an e-mail and I'll see if I can locate it.
I think it was supplied as binary and you FTP'd it to the NonStop and
changed the file code to 700.
Harald
Output to edit file from SQLCI is limited to about 250 characters per
line.
If you want to output longer rows, do the output into a relative file.
Create the file like this:
FUP
> set type r
> set rec 2000
> set ext(1000,1000)
> set maxextents 500
> create tempfile
in SQLCI do output with:
SET LAYOUT PAGE_LENGTH ALL ;
SET SESSION LIST_COUNT 0 ;
SET SESSION WRAP OFF ;
SET STYLE HEADINGS OFF ;
SELECT ... ;
out_report tempfile;
list all;
You can download the file by FTP with mode ASCII
MicroTech
> I don't know if this will work for you but I use an old, unsupported
> Compaq product called ATP SQL for this purpose. It's from 1999 but it
> still works fine in our non-Integrity, SQL/MP environment.
Also called MiniSQL. You can grab a copy (unsupported, "as is," TNS/R
(object code 700) only) from:
https://sites.google.com/site/microtechnonstop/f/MiniSQL.zip
Cheers,
Henry Norman
MicroTech Consulting
sites.google.com/site/microtechnonstop
raghavn...@gmail.com
I'm a newbee to SQLCI and my project works on Tandem. Can anyone of you provide link to any document or reference manual that could help me write small programs using SQLCI ?
Any help would be greatly appreciated.
Thanks,
Raghav
dimandja
http://bizsupport2.austin.hp.com/bc/docs/support/SupportManual/c02132143/c02132143.pdf
Anupam Das
Program using SQLCI? Or Embedded SQL programs?
Randall
> >> programs using SQLCI ? <<
>
>
>
> Program using SQLCI? Or Embedded SQL programs?
Anupam Das
Randall
> >> I hate to state the obvious but why is ODBC not an option? That would get the data directly into EXCEL. <<
>
>
>
> I guess Raghav mixed up topics. The initial topic and his question are not the same.