Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: how to load the csv into temp table in sybase

    Dear Sir/Madam,

    I have the excel sheet which contains OrdId and BillId values. I just wanted to read these two values one by one from excel and loaded them into one temp table in Sybase.
    here is the sample code for your reference:

    begin tran

    create table tempdb..ordBill (ord_id int identity,bill_id int
    )
    go
    load table tempdb..ordBill from 'c:\\test\\test.csv'

    Error:
    Incorrect syntax near the keyword 'table'.

    I am having only one tool ISQLW.exe from which i connect to remote sybase DB and run some queries like sp_help , create etc.. from there

    Awaiting for your valuable reply
    thanks
    pannar

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Save your excel file as a tab delimited file, and then use bcp to load it to the table:

    bcp thedb..the_table in file.txt -U... -P... -S... -c

    Michael

  3. #3
    Join Date
    Apr 2008
    Posts
    8
    Thank you Sir.

    The problem is that we dont have any tool BCP other than Microsoft ISQL/W.

    Is there any other way to acheive this thru ISQLW tool only? plz help me, as this is very urgent requirement.

    regards

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    bcp is a tool that comes standard with a Sybase serverinstallation, or with a sybase Open Client-installation. You don't have to install it separately.
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Apr 2008
    Posts
    8
    Dear Martijnvs,

    Thanks for the info. However, i am unable to run the below command from Microsoft ISQL/W. Plz remember that i directly connect to remote DB from this tool and work on some sybase queries. No sybase server/client installed on my local system.
    when i run the below command,
    bcp dbo..the_table in file.txt
    it has throw the following error:
    Msg 102, Level 15, State 1
    Incorrect syntax near '.'.

    Could you please write the exact full right query be accepted in MS ISQL/W tool.

    Thanks

  6. #6
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by pannar
    Dear Martijnvs,

    Thanks for the info. However, i am unable to run the below command from Microsoft ISQL/W. Plz remember that i directly connect to remote DB from this tool and work on some sybase queries. No sybase server/client installed on my local system.
    when i run the below command,
    bcp dbo..the_table in file.txt
    it has throw the following error:
    Msg 102, Level 15, State 1
    Incorrect syntax near '.'.

    Could you please write the exact full right query be accepted in MS ISQL/W tool.

    Thanks
    Just so I have it clear: you have a Sybase-database, and you connect to it from your wokstation using MS ISQL? I'm not 100% sure, but as far as I know, the Sybase open client is needed to connect to a Sybase-database.

    BCP is a separate tool, to be executes outside of any tool like ISQL.
    The proper syntax for BCP would be:
    bcp <databasename>..<tablename> in <filename> -S<sybaseservername> -U<username> -P<password>

    Substitute the <..>-values with the proper ones for your environment.
    I'm not crazy, I'm an aeroplane!

  7. #7
    Join Date
    Apr 2008
    Posts
    8
    I think the following query works perfectly,

    drop table tempdb..empRec
    create table tempdb..empRec(

    emp_id int null,

    emp_name varchar(100) null)


    bcp tempdb..empRec in empRec.txt -c -Upannar -PXXXXX -Sdb -e error1.txt

    but the problem is that i couldn't run the bcp command from MS ISQL/W or even from command prompt. Seems like i dont have anything other MS ISQL/W.

    Would you provide me the location where i can get this tool downloaded free? ;-)

  8. #8
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    The Sybase Open Client, including the bcp-utility is available for download somewhere on the Sybase-website (http://www.sybase.com).
    I'm not crazy, I'm an aeroplane!

  9. #9
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Look for the developer edition of ASE - this includes the client.

    Michael

  10. #10
    Join Date
    Apr 2008
    Posts
    8
    Thank you Dear, your information is more valuable to me :-)

    Thanks a ton for your kindful help
    regards
    pannar

  11. #11
    Join Date
    Apr 2008
    Posts
    8
    Hi,

    I've installed the developer edition of sybase open client as you suggested.
    it is installed successfully and could run the following bcp command

    bcp tempdb..ordBill in test.csv -c -Upannar -PXXXXX -Sdb1 -e error1.txt

    But it has thrown the following error:
    CTLIB Message: - L6/O8/S5/N3/5/0:
    ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
    Establishing connection failed.

    plz help me how to get rid of this error. Under the installed folder C:\sql10\

    sql.ini file is empty. do i need to specify anything interfaces here?

  12. #12
    Join Date
    Apr 2008
    Posts
    8
    Hi,

    I could connect to the DB after configuring it thru ASE server config.

    now i am getting the following error :
    >>bcp tempdb..ordBill in test.txt -c -Sbilldb1 -Ukamal -P**** -e error1.txt
    Starting copy...
    Unexpected EOF encountered in BCP data-file.
    bcp copy in failed

    my test.txt saved as Unicode type. and it contains the below
    49693,Sybase Product ,12345
    50281,Oracle Product,1222

  13. #13
    Join Date
    Apr 2008
    Posts
    8
    Hi,

    I was able to upload the records from tab delimited text file into temp table successfully.

    Very much thankful to you..

    regards
    pannar

  14. #14
    Join Date
    Nov 2008
    Posts
    1
    I'm trying to run the same commands and got the following error "Cannot run query--referenced object (name NAME NOT RECOVERABLE) dropped during query optimization." Can anyone tell me why I'm getting this error.

Posting Permissions

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