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

    Unanswered: Import file.xlsx to sybase using interactive SQL!?

    Hi everyone!

    I have imported a table from sybase into excel 2007 using excels bulit in function.

    Data -> From Other Sources -> From Microsoft Query

    Here is the question. Can I do this the other way around ? Make changes in the excel file and then export using the same function ?

    Another way is maybe to open interactive sql in sybase and make a sql script ? But the thing is that I dont know how this is working.

    Anyone in here that have tried this or have a method to to this sort of job ?


    Regards
    Magnus

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    No.
    Best is to save as tab delimited text
    Then bcp in with the flags -c -F2

  3. #3
    Join Date
    Oct 2007
    Posts
    29
    pdreyer:

    Im pretty new with this bcp utility. Do you have a short example how to execute this function ? I have saved the file as mytable.csv, guess thats what you ment ?

    But here is the tricky part. How do execute this file (mytable.csv) into my table in sybase ?

    A little step-by-step guide would be very helpful. Do I use a .bat file or is there some bcp utility you install ? Or maybe from interactive SQL in Sybase ?

    Regards
    Magnus

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    bcp is part of the ASE PC client
    see attache picture <t1.gif> to see how I save the file
    Code:
    C:\tmp>type t1.txt
    key1    dat1    val1
    1       02-Sep-2008     abc
    2       03-Sep-2008     defg
    3       04-Sep-2008     h
    
    C:\tmp>isql -Usa -Spd6 -w333
    Password:
    1> create table tempdb..t1 (id int, indate datetime, name varchar(25))
    2> go
    1> exit
    
    C:\tmp>bcp tempdb..t1 in t1.txt -Usa -Spd6 -c -F2
    Password:
    
    Starting copy...
    
    3 rows copied.
    Clock Time (ms.): total = 16  Avg = 5 (187.50 rows per sec.)
    
    C:\tmp>isql -Usa -Spd6 -w333
    Password:
    1> select * from tempdb..t1
    2> go
     id          indate                     name
     ----------- -------------------------- -------------------------
               1        Sep  2 2008 12:00AM abc
               2        Sep  3 2008 12:00AM defg
               3        Sep  4 2008 12:00AM h
    
    (3 rows affected)
    1> drop table tempdb..t1
    2> go
    1> exit
    
    C:\tmp>
    Attached Thumbnails Attached Thumbnails t1.GIF  

  5. #5
    Join Date
    Oct 2007
    Posts
    29
    pdreyer:

    Thx, but it semas like I cant run commands from the CMD promt. Says that its not recognized. It works fine to execute sql statements within interactive sql.

    Btw, im using Adaptive Server Anywhere 9.

    Regards
    Magnus

  6. #6
    Join Date
    Oct 2007
    Posts
    29
    pdreyer:

    Hi,

    From interactive sql, this works fine. So im getting values from two tables. there are linked togheter by the common value "mi_seq". In these tables there are many other column values aswell, this is just a few of them. This is the primary once i want to change.

    SELECT table1.mi_seq, table1.obj_num, table1.name_1, table2.preset_amt_1
    FROM db.table1
    INNER JOIN db.table2
    ON table1.mi_seq = table2.mi_seq
    WHERE obj_num >1
    ORDER BY obj_num;
    OUTPUT TO C:\import.csv FORMAT ASCII

    Now i set the new name and price i the csv file and then save as txt delimited.

    So the question is how to import this txt file when the you have a joined tables?

    Regads
    Magnus
    Attached Thumbnails Attached Thumbnails excel_result.jpg   mi_def.jpg   mi_price_def.jpg  

  7. #7
    Join Date
    Oct 2007
    Posts
    29
    Instead of using the bcp utility, i have used this sql statement to get the values into an temporary table. This works fine.

    CREATE TABLE custom.tmp_mi_def (
    mi_seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
    mi_obj_num INT,
    mi_name1 CHAR (16),
    price1 MONEY12,
    maj_grp CHAR (25),
    fam_grp CHAR (25)
    );
    INPUT INTO custom.tmp_mi_def FROM c:\import1_.txt format ASCII (mi_obj_num, mi_name1, price1, maj_grp, fam_grp);


    From this table im gonna split data into different tables. That works pretty ok but the is one thing that im curious about. The issue is following.

    From the custom.tmp_mi_def table i want to move all data into an new table.
    tmp_mi_def.maj_grp

    //
    create table custom.major (
    maj_grp CHAR (16)
    )
    //

    maj_grp
    ** Shoes ** (Header)
    bla bla
    bla bla
    ** T-Shirt ** (Header)
    bla bla
    bla bla
    //
    go
    //
    ALTER TABLE custom.major
    ADD obj_num INT autoincrement

    Results looks like this:

    obj_num maj_grp
    1 ** Shoes ** (Header)
    2 bla bla
    3 bla bla
    4 ** T-Shirt ** (Header)
    5 bla bla
    6 bla bla

    Im looking for the following result

    obj_num maj_grp
    10000 ** Shoes ** (Header)
    10001 bla bla
    10002 bla bla
    20000 ** T-Shirt ** (Header)
    20001 bla bla
    20002 bla bla

    So basically im looking for some function that generates numbers into the obj_num column depending of what values you get from the maj_grp column. The must be some way to modify the obj_num column either before you put in the brand values or afterwords. so i taught you could modify autoincrement.

    Maybe first just create the maj_grp column och put in the values from tmp_mi_def.maj_grp, and then use the ALTER TABLE function and add a column called obj_num and then put in numbers in depending of what values you have in the brand column ?


    Regards
    Magnus

Posting Permissions

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