Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Posts
    29

    Question Unanswered: Read columns from excel into tables ?!

    Hi,

    I have an excel ark. Collumns A1, B1.
    A1 is listing articles and B1 is listing prices. This is a long list. products.xls

    A1 B1
    Article 1 50
    Article 2 40
    Article 3 30
    etc .. etc..

    In Adaptive SQL Server i have two tables.
    Article (tbl_article)
    Price (tbl_price)

    These are linked together with an unique ID called
    obj_num

    I want to read my excel ark into Adaptive SQL Server and sort out article and price into the right table.

    Is this possible ?

    Regards
    Magnus

  2. #2
    Join Date
    Mar 2007
    Posts
    86
    export the excel to a delimted file..
    bcp the file into sybase tbl

  3. #3
    Join Date
    Oct 2007
    Posts
    29
    Thx stuarta,

    You dont have any example code to show me. Have read some about the bcp utility but have no idea how it works out with sybase. The procedure from exporting excel sheets to bcp and from bcp to sybase. Or any good toturials ? Pretty newbi here ..

    Regards
    Magnus

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by mberggren
    These are linked together with an unique ID called obj_num
    What value should be used for the insert of obj_num? An identity field?

  5. #5
    Join Date
    Oct 2007
    Posts
    29
    The data type for obj_num is integer

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    From excel Save as type Text (Tab delimited)
    then e.g.
    Code:
    C:\tmp>isql -Usa -P -Spd -w333 -Dtempdb
    1> create table t1 (c1 int,c2 char(3),c3 varchar(10))
    2> go
    1> exit
    C:\tmp>more t1.txt
    c1      c2      c3
    2       x       b
    3       y       c
    4       z       a
    
    C:\tmp>bcp tempdb..t1 in c:\tmp\t1.txt -Usa -P -Spd -c -F2
    
    Starting copy...
    
    3 rows copied.
    Clock Time (ms.): total = 1  Avg = 0 (3000.00 rows per sec.)
    
    C:\tmp>isql -Usa -P -Spd -w333 -Dtempdb
    1> select * from t1
    2> go
     c1          c2  c3
     ----------- --- ----------
               2 x   b
               3 y   c
               4 z   a
    
    (3 rows affected)
    1> drop table t1

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You can read more about bcp in the ASE Utility Guide

  8. #8
    Join Date
    Oct 2007
    Posts
    29
    Thx,

    See if I have time in the weekend to check bcp in the ASE Utility Guide.
    Its a bit hard to get an clue of this, little bit of an rookie in this area.

    I think I have some understadning of what you wrote.

    In my case I want to split up the table you create in bcp and sort out price to tbl_price and article to tbl_article when you insert in Sybase

    So you write the bcp commands in CMD prompt ?

    Regards
    Magnus

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by mberggren
    In my case I want to split up the table you create in bcp and sort out price to tbl_price and article to tbl_article when you insert in Sybase
    You will need a format file to specify which columns should be skipped and if you e.g. want to insert column 3 from the text file into table column 2. It might be easier to just copy and paste your data into a new sheet.

    Quote Originally Posted by mberggren
    So you write the bcp commands in CMD prompt ?
    Yes bcp is an executable and should be executed like any other OS command
    Last edited by pdreyer; 10-05-07 at 11:03.

Posting Permissions

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