Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: Field Separators

    Hello,

    I would like some help please. I have this script which delimits the output from SQL with ;. However, I get a ; at the start of each record which implies there is a blank field at the start. How can I get rid of the starting ;?


    isql -U${DBLOGIN} -S${DSQUERY} -P${DBPASSWD} -w2000 -s ';' -o bookreport.dat <<EOF

    use ${DBNAME}
    go

    select * from UserParam
    where AuthProfile = 'book'
    go

    quit

    EOF

    Also is there any way of putting in an end-of-line character?

    I appreciate any help that can be given as I am quite new to all this.

    Many thanks

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    12

    Talking

    In 'select' instead of using '*' you specify all the column names except the first one.

    -

    Pat

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    Unfortunately, this did not work. It still puts a leading ; before the first field.

    It is the -s parameter that puts the leading ; in. It is when you come to import, say into Excel, that the leading ; implies that there is a blank first field when this is not actually the case.

    Does anyone else have any other ideas? I appreciate any help.

    Many thanks

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4

    Lightbulb

    I just tried it myself, and I think I get it.
    The columnseparator is used as a kind of framework. The first ';' doesn't mean there's an empty field, but it is meant as the left line of the frame.

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    While I agree with what you say. If you take the file that is created and try to import into Excel, Excel will read this leading ; and create a blank first column. If I can strip out this first ; then the import into Excel will be straight forward for my enduser.

    Thanks

  6. #6
    Join Date
    Nov 2002
    Posts
    833
    Originally posted by JMLawton
    While I agree with what you say. If you take the file that is created and try to import into Excel, Excel will read this leading ; and create a blank first column. If I can strip out this first ; then the import into Excel will be straight forward for my enduser.

    Thanks
    what about manipulating the output file using sed command ??

  7. #7
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Originally posted by JMLawton
    While I agree with what you say. If you take the file that is created and try to import into Excel, Excel will read this leading ; and create a blank first column. If I can strip out this first ; then the import into Excel will be straight forward for my enduser.

    Thanks
    Ah...now I see what yo're planning to do.
    In that case, you can make a selectstatement like

    select dbid, ";" as ";", name from sysdatabases

    That would produce something like
    Code:
    dbid  ;  name
    ------ - ------------------------------
     31515 ; dbccdb
         1 ; master
         3 ; model
     31513 ; sybsystemdb
     31514 ; sybsystemprocs
         2 ; tempdb
    It requiers a bit more typing, but I think the result is what you need. You can import this data into Excel.
    Apart from that, Excel can import files with so called 'fixed width columns', so you don't have to use field separators per se.

    Let me know if this solves your problem .

  8. #8
    Join Date
    Mar 2004
    Posts
    5
    Thanks for this. I will give it a go. However, I was trying to do something with sed.

    If I run the commands below from the command prompt then it works exactly as I want it. As soon as I put in into a script (with the only other addition of #! /usr/bin/ksh), I get the following error:

    ./booktest2.sql[2]: : not found

    I am not quite sure what it is complaining about. Any ideas???

    Many thanks


    isql -U${DBLOGIN} -S${DSQUERY} -P${DBPASSWD} -w2000 -s';' <<EOF | \
    sed 's/^;//g' > /home/lawtonj/bookreport.dat
    use ln_dev01_sung83
    go
    select * from UserParam
    where AuthProfile = 'book'
    go
    quit
    EOF

  9. #9
    Join Date
    Mar 2004
    Posts
    2
    If you're doing this a lot, I'd suggest you try bcp with a view.
    Something like this...

    create view UsrParmBks as
    select * from UserParam
    where AuthProfile = 'book'
    go
    grant select on UsrParmBks to public
    go

    bcp UsrParmBks out results.txt -c -t';'

    Then import results.txt wherever you'd like.

    Now you'll have another oddity, though: no semicolon after the final column values. I don't think Excel will mind, but I'm not sure.

    Also not sure about that last error.
    ./booktest2.sql is complaining about something, but what's booktest2.sql?
    Feels like you're quoting something correctly for the command line, but not for the script.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •