Re: SQL*PLUS spooling and CR/LF

From: Kenneth C Stahl <BluesSax_at_Unforgettable.com>
Date: Tue, 24 Aug 1999 12:34:16 -0400
Message-ID: <37C2C987.393A367F@Unforgettable.com>

abrusko wrote:

> Ken,
>
> I am creating this comma-delimited file to send to an off-
> site service that has told me they require a CR/LF at the
> end of each record.
>
> I don't know what is requiring them to do this, but they
> are requesting the data this way.
>
> Unfortunately I am restricted by the tools that I have
> available and by various other department standards.
>
> I have SQL*PLUS to do my SQL statements, which I am using
> to produce the comma-delimited file that I will send off-
> site. This will be a very simple and effective means of
> producing this file if I can just assure that there is a
> CR/LF at the end of each record. My record will be of
> fixed length (416 characters long) and is I am using 416 on
> the "Set linesize" statement. Shouldn't I be able to view
> this file and shouldn't I be able to "detect" the CR/LF by
> putting the cursor at the end of the line an hitting the
> "arrow" key and being "bounced" down to the next line?
>
> So I'm back to the original question of: How can I ensure
> that there is a CR/LF at the end of each physical line in
> my comma-delimited file?
>
> P.S. Thanks alot for your reply and info!
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
> The fastest and easiest way to search and participate in Usenet - Free!

In that case you don't need to do anything at all to the file itself.

For example, I have a table that has three columns. To dump this out to a comma-delimited file I run something like the following:

rem - myscript.sql
set feedback off;
set termout off;
set trimout on;
set trimspool on;
spool c:\temp\mydata.dat
select col1 ||','||

          col2 ||','||
          col3

from mytable;
exit success;

If I were to look at the file with a file viewer (NOT notepad, wordpad, or any other editor!!!)
I see that at the end of very line there is a 0D 0A sequence - a carriage return and a line feed.

The problem then becomes how do you get rid of the LF character. You probably can't do this with a text-based editor because DOS/Windows text-based editors translate the cr/lf sequence so that it appears as if it is only a line feed.

Probably what you are going to need to do is write a small C program that will read your file in as a binary file and then write the record back out without the line feed, leaving only the carriage return.

Sounds like you have an extremely strange machine at the other end if it cannot accomodate a normal text file.

Ken Received on Tue Aug 24 1999 - 11:34:16 CDT