Create a CSV file - Hewlett Packard

This is a discussion on Create a CSV file - Hewlett Packard ; I've read the archives - and the only info I can find on CSV files is converting to fixed from a csv file. I was wondering if there was a way to create a *real* csv file on the 3000. ...

+ Reply to Thread
Results 1 to 9 of 9

Thread: Create a CSV file

  1. Create a CSV file

    I've read the archives - and the only info I can find on CSV files is converting
    to fixed from a csv file.

    I was wondering if there was a way to create a *real* csv file on the 3000.

    I don't have many resources - I can create a csv file with COBOL or with
    QUIZ. What they really are is a fixed length file with commas in between the
    fields. Problem is, if you open the file up in excel - there are extra spaces, and
    it doesn't handle it correctly (you can see the data fine - but if you need to
    load it into a database or do a lookup - it includes the trailing spaces).. I'd
    have to do a trim in excel and re-save the file to get rid of the spaces.

    Is there a way to do this in COBOL or even just from the OS?

    tia

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *


  2. Re: Create a CSV file

    How about STRING FIELDA "," FIELDB "," FIELDC "," FIELDD DELIMITED BY
    SPACES INTO CSVFILE-REC

    Michael Berkowitz
    Project Manager, CGS Application Solutions
    707 Wilshire Blvd Suite 1900
    Los Angeles, CA 90017-3509
    T: 213 614-1308
    F: 213 614-2028
    mberkowitz@cgsinc.com




    kellie Jones
    Sent by: HP-3000 Systems Discussion
    03/07/2007 09:53 AM
    Please respond to
    kellie Jones


    To
    HP3000-L@RAVEN.UTC.EDU
    cc

    Subject
    [HP3000-L] Create a CSV file






    I've read the archives - and the only info I can find on CSV files is
    converting
    to fixed from a csv file.

    I was wondering if there was a way to create a *real* csv file on the
    3000.

    I don't have many resources - I can create a csv file with COBOL or with
    QUIZ. What they really are is a fixed length file with commas in between
    the
    fields. Problem is, if you open the file up in excel - there are extra
    spaces, and
    it doesn't handle it correctly (you can see the data fine - but if you
    need to
    load it into a database or do a lookup - it includes the trailing spaces).
    I'd
    have to do a trim in excel and re-save the file to get rid of the spaces.

    Is there a way to do this in COBOL or even just from the OS?

    tia

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *


    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *


  3. Re: Create a CSV file

    Hi Kellie
    Have you got SuprTool from Robelle? Works like a charm... believe
    ',PRN' at the end of the output statement will do just what you want ...
    otherwise it is still doable using COBOL but I would recommend BASIC/3000
    as it does trimming and such easier (in my opinion... humble or not is for
    the hearer to decide hehehe)

    I used to use SuprTool all the time to prep for CSV files that I would
    then download to PC's and network servers for loading into FoxPro, xBase,
    dBase III+, Lotus 123, QuatroPro, Vulture (that is an old one!!!) and so on
    ....

    Thanks,
    Art "ahhh... the good old days ! hehehe" Bahrs

    ================================================== =====
    Art Bahrs, CISSP Information Security The Regence Group
    (503) 225-4992 Cell 971-244-2459 FAX (503)
    220-3806



    "kellie Jones"
    ERLLC.COM> To
    Sent by: HP3000-L@RAVEN.UTC.EDU
    "HP-3000 cc
    Systems
    Discussion" Subject
    N.UTC.EDU>


    03/07/2007
    09:53 AM


    Please respond
    to
    "kellie Jones"
    ERLLC.COM>
    |------------|
    | [ ] Secure |
    | E-mail |
    |------------|





    I've read the archives - and the only info I can find on CSV files is
    converting
    to fixed from a csv file.

    I was wondering if there was a way to create a *real* csv file on the 3000.

    I don't have many resources - I can create a csv file with COBOL or with
    QUIZ. What they really are is a fixed length file with commas in between
    the
    fields. Problem is, if you open the file up in excel - there are extra
    spaces, and
    it doesn't handle it correctly (you can see the data fine - but if you need
    to
    load it into a database or do a lookup - it includes the trailing spaces).
    I'd
    have to do a trim in excel and re-save the file to get rid of the spaces.

    Is there a way to do this in COBOL or even just from the OS?

    tia

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *




    ***IMPORTANT NOTICE: This communication, including any attachment, contains information that may be confidential or privileged, and is intended solely for the entity or individual to whom it is addressed. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message is strictly prohibited. Nothing in this email, including any attachment, is intended to be a legally binding signature.***

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *


  4. Re: Create a CSV file

    Here's how I have created CSV files in QUIZ. It's a bit brute force
    with numeric fields since we're still on 7.28, but it gets the job done.
    I use the TRUNC function to eliminate trailing spaces.

    ACC HAZ-XREF
    DEF SFD-ACCT CHAR*8 = GETS("SFD_ACCT")
    DEF DLQ CHAR*1 = '"'
    DEF DLC CHAR*1 = ','
    DEF DLXX CHAR*3 = '","'
    DEF DLXN CHAR*2 = '",'
    DEF DLNX CHAR*2 = ',"'
    DEF DISCONT-DATEX CHAR*10 = ASCII(DISCONT-DATE)[5:2] + "/" + &
    ASCII(DISCONT-DATE)[7:2] + "/" + &
    ASCII(DISCONT-DATE)[1:4] IF DISCONT-DATE > 0 ELSE " "
    DEF DIF-CTRX CHAR*10 = ASCII(DIF-CTR)
    DEF QTY-SLDYTDX CHAR*12 = ASCII(QTY-SLDYTD) &
    IF QTY-SLDYTD > 0 ELSE "000"
    DEF QTY-SLDYTDX2 CHAR*12 = &
    QTY-SLDYTDX[1:INDEX(QTY-SLDYTDX," ") - 3] + "." + &
    QTY-SLDYTDX[INDEX(QTY-SLDYTDX," ") - 2:2]
    DEF QTY-SLDLYX CHAR*12 = ASCII(QTY-SLDLY) &
    IF QTY-SLDLY > 0 ELSE "000"
    DEF QTY-SLDLYX2 CHAR*12 = &
    QTY-SLDLYX[1:INDEX(QTY-SLDLYX," ") - 3] + "." + &
    QTY-SLDLYX[INDEX(QTY-SLDLYX," ") - 2:2]
    DEF QTYX CHAR*12 = ASCII(QTY) &
    IF QTY > 0 ELSE "000"
    DEF QTYX2 CHAR*12 = &
    QTYX[1:INDEX(QTYX," ") - 3] + "." + &
    QTYX[INDEX(QTYX," ") - 2:2]
    DEF DESCR92 CHAR*92 = PACK(DESCR + " " + DESCR2 + " " + DESCR3)
    DEF REC1 CHAR*900 = DLQ + "SFD-ACCT" + DLXX + &
    "S-ITEM" + DLXX + "HAZ-CODE" + DLXX + &
    "DESCRIPTION" + DLXX + "MIF-LOGICAL-DEL" + DLXX + &
    "DISCONT-DATE" + DLXX + "REPLACED-WITH" + DLXX + &
    "CIF-ACTIVE-INACTIVE" + DLXX + "#-ACTIVE-DIFS" + DLXX + &
    "UNIT-STOCK" + DLXX + "QTY-ONHAND" + DLXX + &
    "QTY-SLDYTD" + DLXX + "QTY-SLDLY" + DLQ
    DEF REC2 CHAR*900 = DLQ + TRUNC(SFD-ACCT) + DLXX + &
    TRUNC(S-ITEM) + DLXX + TRUNC(HAZ-CODE) + DLXX + &
    TRUNC(DESCR92) + DLXX + TRUNC(MIF-LOGICAL-DEL) + DLXN + &
    TRUNC(DISCONT-DATEX) + DLNX + TRUNC(REPLACED-WITH) + DLXX + &
    ACTIVE-STATUS[1:1] + DLXN + TRUNC(DIF-CTRX) + DLNX + &
    TRUNC(UNIT-STOCK) + DLXN + TRUNC(QTYX2) + DLC + &
    TRUNC(QTY-SLDYTDX2) + DLC + TRUNC(QTY-SLDLYX2)
    SET SUB NAME HAZITEMS KEEP APPEND SIZ 900000
    @IF FIRSTIME
    REP SUM REC1
    SET REP LIM 1
    SET NOWARN
    GO
    @ENDIF
    SORT ON S-ITEM
    REP SUM REC2
    SET REP LIM 900000
    SET WARN
    GO
    EXIT

    -----Original Message-----
    From: HP-3000 Systems Discussion [mailto:HP3000-L@RAVEN.UTC.EDU] On
    Behalf Of kellie Jones
    Sent: Wednesday, March 07, 2007 12:53 PM
    To: HP3000-L@RAVEN.UTC.EDU
    Subject: [HP3000-L] Create a CSV file

    I've read the archives - and the only info I can find on CSV files is
    converting
    to fixed from a csv file.

    I was wondering if there was a way to create a *real* csv file on the
    3000.

    I don't have many resources - I can create a csv file with COBOL or with

    QUIZ. What they really are is a fixed length file with commas in between
    the
    fields. Problem is, if you open the file up in excel - there are extra
    spaces, and
    it doesn't handle it correctly (you can see the data fine - but if you
    need to
    load it into a database or do a lookup - it includes the trailing
    spaces). I'd
    have to do a trim in excel and re-save the file to get rid of the
    spaces.

    Is there a way to do this in COBOL or even just from the OS?

    tia

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *


  5. Re: Create a CSV file

    If you have Suprtool it grabs the data and creates CSV files using STExport
    very nice.

    Otherwise in Quiz I have this use file:

    ;################################################# ###########
    ; VALLEY PRESBYTERIAN HOSPITAL
    ;
    ; SOURCE NAME : DOWNLOAD.VPHU
    ;
    ; FILES ACCESSED : NONE
    ;
    ; ELEMENTS ACCESSED : NONE
    ;
    ; PROGRAM DESCRIPTION : DEFINIATIONS FOR DOWNLOADED PRN FILES
    ;
    ; PROGRAMMER : CATHERINE L. LITTEN
    ;
    ; DATE ENTERED PRODUCTION : 1997/08/12
    ;
    ;################################################# ###########

    DEFINE QUOTE CHAR*1 = '"'

    DEFINE COMMA CHAR*1 = ','

    DEFINE TEXT CHAR*3 = '","'

    DEFINE TEXT-NUM CHAR*2 = '",'

    DEFINE NUM-TEXT CHAR*2 = ',"'
    *******************************************
    Then I call it in a Quiz file like this:
    *******************************************
    ;************************************************* ***********
    ; VALLEY PRESBYTERIAN HOSPITAL
    ;
    ; SOURCE NAME : PPR715VS.VPHS
    ;
    ; PASS DESCRIPTION : build PORTABLE SUBFILE
    ;************************************************* ************

    urge PPR715V1.VPHD
    urge PPR715VQ.VPHD

    access *PPR715K1.VPHD &
    link (ENTITY-ID + char(LINK-DEPT)) &
    to E-DEPT-NBR of DEPARTMENTS

    define STAYED char*1 = "Y" if NEW-TOTAL = 1 else " "

    define TIME-PERDEIM char*10 = TIME-DESC if TIME-DESC = "PER-DEIM" else &
    TIME-DESC if TIME-DESC = "UNKNOWN" else "NORMAL"


    ; Following defines Quote, Comma, Text, Text-Num, num-Text Items
    use DOWNLOAD.VPHU nolist

    sort on TIME-PERDEIM &
    on STAYED

    set report limit 100000

    set subfile portable name PPR715V1.VPHD keep size 100000

    report summary &
    EMP-NBR &
    COMMA &
    LINK-DEPT &
    num-TEXT &
    DEPT-DESC &
    TEXT &
    CATG-DESC &
    TEXT &
    TIME-PERDEIM &
    TEXT &
    STAYED &
    TEXT-NUM &
    LENGTH-EMPLOYED &
    num-TEXT &
    JOBCLASS &
    TEXT &
    GOOD-STATUS &
    TEXT-NUM &
    TIME-SORT &
    COMMA &
    START-DATE &
    COMMA &
    END-DATE

    build PPR715VC.VPHC display "VER 008 VPH 1999/04/04 (ALT 7)"

    Hope this helps,
    Cat Litten

    Ours is the age that is proud of machines that think and suspicious of men
    who try to. - H. Mumford Jones



    -----Original Message-----
    From: HP-3000 Systems Discussion [mailto:HP3000-L@RAVEN.UTC.EDU]On
    Behalf Of kellie Jones
    Sent: Wednesday, March 07, 2007 9:53 AM
    To: HP3000-L@RAVEN.UTC.EDU
    Subject: Create a CSV file


    I've read the archives - and the only info I can find on CSV files is
    converting
    to fixed from a csv file.

    I was wondering if there was a way to create a *real* csv file on the 3000.

    I don't have many resources - I can create a csv file with COBOL or with
    QUIZ. What they really are is a fixed length file with commas in between the
    fields. Problem is, if you open the file up in excel - there are extra
    spaces, and
    it doesn't handle it correctly (you can see the data fine - but if you need
    to
    load it into a database or do a lookup - it includes the trailing spaces).
    I'd
    have to do a trim in excel and re-save the file to get rid of the spaces.

    Is there a way to do this in COBOL or even just from the OS?

    tia

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *


  6. Re: Create a CSV file

    Suprtool can do it using any file for which it knows your field
    locations & types.

    > -----Original Message-----
    > From: HP-3000 Systems Discussion
    > [mailto:HP3000-L@RAVEN.UTC.EDU] On Behalf Of kellie Jones
    > Sent: Wednesday, March 07, 2007 9:53 AM
    > To: HP3000-L@RAVEN.UTC.EDU
    > Subject: Create a CSV file
    >
    > I've read the archives - and the only info I can find on CSV
    > files is converting
    > to fixed from a csv file.
    >
    > I was wondering if there was a way to create a *real* csv
    > file on the 3000.
    >
    > I don't have many resources - I can create a csv file with
    > COBOL or with
    > QUIZ. What they really are is a fixed length file with commas
    > in between the
    > fields. Problem is, if you open the file up in excel - there
    > are extra spaces, and
    > it doesn't handle it correctly (you can see the data fine -
    > but if you need to
    > load it into a database or do a lookup - it includes the
    > trailing spaces). I'd
    > have to do a trim in excel and re-save the file to get rid of
    > the spaces.
    >
    > Is there a way to do this in COBOL or even just from the OS?
    >
    > tia
    >
    > * To join/leave the list, search archives, change list settings, *
    > * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *
    >


    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *


  7. Re: Create a CSV file

    IMHO, Excel has a lame import process, it try's to out-smart you while
    doing things you just don't want it to do.

    I've written hundreds, maybe thousands of COBOL extracts to CSV, and
    then use Microsoft Access to import into a table, then export (Save As)
    whatever (xls).

    The Access import process is very full-featured, you can tell it to
    ignore portions of the input file, data names, data types, and save
    these specs for later..


    my $0.02
    Mike.


    >>> kellie Jones 3/7/2007 11:53 am >>>

    I've read the archives - and the only info I can find on CSV files is
    converting
    to fixed from a csv file.

    I was wondering if there was a way to create a *real* csv file on the
    3000.

    I don't have many resources - I can create a csv file with COBOL or
    with
    QUIZ. What they really are is a fixed length file with commas in
    between the
    fields. Problem is, if you open the file up in excel - there are extra
    spaces, and
    it doesn't handle it correctly (you can see the data fine - but if you
    need to
    load it into a database or do a lookup - it includes the trailing
    spaces). I'd
    have to do a trim in excel and re-save the file to get rid of the
    spaces.

    Is there a way to do this in COBOL or even just from the OS?

    tia

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *


  8. Re: Create a CSV file

    Michael Anderson writes:

    > IMHO, Excel has a lame import process, it try's to out-smart you while
    > doing things you just don't want it to do.


    That's what I discovered recently when I wrote a COBOL program to create
    a file for Excel to import. I had to go through all sorts of gyrations
    to get Excel to import it correctly.

    One thing that helped immensely was changing the program to create the
    file as a tab-delimited file. Once I did that, I was able to take all
    the defaults in Excel's import wizard.

    Walter

    Walter J. Murray

    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *


  9. Re: Create a CSV file

    I think OpenOffice.org's spreadsheet is much more consistant at importing
    files, and the suite is smoother and more powerful linking to a variety of
    database backends.

    On 3/7/07, Walter J. Murray wrote:
    >
    > Michael Anderson writes:
    >
    > > IMHO, Excel has a lame import process, it try's to out-smart you while
    > > doing things you just don't want it to do.

    >
    > That's what I discovered recently when I wrote a COBOL program to create
    > a file for Excel to import. I had to go through all sorts of gyrations
    > to get Excel to import it correctly.
    >
    > One thing that helped immensely was changing the program to create the
    > file as a tab-delimited file. Once I did that, I was able to take all
    > the defaults in Excel's import wizard.
    >
    > Walter
    >
    > Walter J. Murray
    >
    > * To join/leave the list, search archives, change list settings, *
    > * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *
    >


    * To join/leave the list, search archives, change list settings, *
    * etc., please visit http://raven.utc.edu/archives/hp3000-l.html *


+ Reply to Thread