Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2004
    Posts
    5

    Unanswered: BCP in with selected column in table

    Hello,

    I try to load into a table on ASE 12.5 with bcp, but I don't know how to do with the columns selected in the flat file. What i can do in the bcp.fmt ?


    You find below some information concerned this question :

    Create a table on ASE server
    CREATE TABLE Test
    (
    Id numeric(18,0) IDENTITY,
    CustomId varchar(30) NULL,
    Book varchar(255) NULL,
    Account varchar(255) NULL,
    Amount varchar(255) NULL,
    )

    BCP.FMT file

    10.0
    5
    1 SYBCHAR 0 512 "," 2 CustomId
    2 SYBCHAR 0 512 "," 4 Account
    3 SYBCHAR 0 512 "," 0 Date
    4 SYBCHAR 0 512 "," 5 Amount
    5 SYBCHAR 0 512 "\r\n" 3 Book


    Format flat file imported : CustomId,Account,Date,Amount,Book

    2345T,33897,20011222,35.00,Java
    3456S,437898,20011025,79.34,Programming in C++


    But it does not work.

  2. #2
    Join Date
    Oct 2004
    Location
    Mumbai
    Posts
    15

    Resolution

    Follow the steps given below :-

    In the eg below I have used pubs2 as my database

    Make sure before you bcp in the data the following dboption is set
    use master
    go
    sp_dboption pubs2, "select into",true
    go



    Take a fresh copy of the data out to flat file

    bcp pubs2..tab2 out "c:\bcp_out.txt" -Usa -P -SASEP -c

    there will be no bcp.fmt file created.

    To bcp in the data from the flat file

    bcp pubs2..tab2 in"c:\bcp_out.txt" -Usa -P -SASEP -c

  3. #3
    Join Date
    Dec 2004
    Posts
    5

    BCP with selected columns

    Hi Rayan,

    Thank you for your reply, but by this way it does not work.

    - I have a flat file with 5 columns

    CustomId,Account,Date,Amount,Book
    2345T,33897,20011222,35.00,Java
    3456S,437898,20011025,79.34,Programming in C++

    - My BCP.FMT file
    10.0
    5
    1 SYBCHAR 0 512 "," 2 CustomId
    2 SYBCHAR 0 512 "," 4 Account
    4 SYBCHAR 0 512 "," 5 Amount
    5 SYBCHAR 0 512 "\r\n" 3 Book

    - My table on ASE server has this form
    CREATE TABLE Test
    (
    Id numeric(18,0) IDENTITY,
    CustomId varchar(30) NULL,
    Book varchar(255) NULL,
    Account varchar(255) NULL,
    Amount varchar(255) NULL,
    )

    ==> After BCP copy, I must have in this table these datas
    Id CustomId Book Account Amount
    22210 2345T Java 33897 35.00
    22221 3456S Programming in C++ 437898 79.34


    Thank for your help

  4. #4
    Join Date
    Oct 2004
    Location
    Mumbai
    Posts
    15

    try this out

    You cannot do a bcp in/out of selected columns from a flat file/table in to a table since bcp utility requires the number of coulmns and the datatypes in the flat file and the table to be same and in the correct sequence , just like an insert.

    From the description of the problem ,the columns sequence specified in the flat file and in the table in ASE do not match each other as there is a column called date and also the sequence is not followed ,so you may have to change the sequence since data that needs to be inserted through bcp should match the table definition exactly.

    The best way to insert the records is create a table say test1
    CREATE TABLE Test1
    (
    CustomId varchar(30) NULL,
    Account varchar(255) NULL,
    Date varchar(10) NULL,
    Amount varchar(255) NULL,
    Book varchar(255) NULL
    )

    once you have created this table bcp in the data from the flat file . Make sure you have included the proper field terminator and row terminator ( -t , -r) parameters properly else the bcp in will not be successfull.
    In my case , the flat file looks like this row terminator /r field terminator ,

    2345T,33897,20011222,35.00,Java/3456S,437898,20011025,79.34,Programming in C++/


    so my bcp statement is

    bcp pubs2..Test1 in "c:\test1.txt" -Usa -P -SASEP -t , -c -r /



    once the data is brought in to test1 you can do a insert embedded with a select .

    insert into Test
    select (CustomId ,Book,Account ,Amount ) from pubs2..Test1

  5. #5
    Join Date
    Dec 2004
    Posts
    5
    Thank Ryan.

  6. #6
    Join Date
    Dec 2004
    Posts
    5
    Thank Ryan.

    I tried this solution before my post. I want to know if there another solution with bcp.

  7. #7
    Join Date
    Dec 2004
    Posts
    1

    Ryan's answer was not correct.

    Ryan told you "since bcp utility requires the number of coulmns and the datatypes in the flat file and the table to be same and in the correct sequence, just like an insert."

    This is not correct. When you're importing with BCP, you can skip columns in the input file, you can decide not to load anything into some columns in the table, and you can load columns in any order you want.

    Look in the online help for SQL (called Books Online), look under BCP Utility, and double-click the item called Format Files. It will bring up a menu with choices called "Using a Data File with Fewer Fields", "Using a Data File with Fields in a Different Order", etc.

    The documentation isn't 100% clear and understandable, but at last you should know what BCP can do.

    ("Just like an insert" -- even an insert doesn't require the fields to be in the same order -- Insert allows you to specify the field list, which means the fields can be in any order you want.)


    David Walker

  8. #8
    Join Date
    Oct 2004
    Location
    Mumbai
    Posts
    15

    Hi

    Hi ,

    Maybe I did not read the documentation for BCP clearly but , when I executed the bcp command it did not give me the option for specifing individual column names , this is taking into consideration that I have no
    format file .

    Also in an insert into a table how can you skip the field if the column has the property not NULL or does not have a default bound to it .

    Walker do you have a working example of the bcp in with selected columns if , yes please post , coz it could be very useful for all of us.

    Thanks

  9. #9
    Join Date
    Dec 2002
    Posts
    104

    BCP in with selected column in table

    Hello All,

    sample format file to insert selective columns from a flat file
    create table tab1
    (col1 int NULL,
    col2 Char(5) NULL,
    col3 int NULL)

    in flat file(tab1.out) (lets take it tab seperated)
    abc 1 11 ABC
    def 2 22 DEF
    ghi 3 33 GHI


    Assume , we need to populate only col1 and col2 of table "tab1" from flat file , wher mapping of the columns is

    Table Flat File
    col1 2nd column
    col2 1st column

    so we can make a format file like this.
    ----------------------------------format file start --------------------
    10.0
    4
    1 SYBCHAR 0 255 "\t" 2 col2
    2 SYBCHAR 0 255 "\t" 1 col1
    3 SYBCHAR 0 255 "\t"
    4 SYBCHAR 0 255 "\n"
    -------------------------------end format file --------------------------

    now give the command

    bcp db..tab1 in tab1.out -Uuser -Sserver -Ppassword -eerrorfile -fbcp.fmt

    In case of csv file the "/t" can re replaced with "," in the format file
    I think this should work for you.It works for me

    In case of further problems , please feel free to contact

    --Pooja
    Last edited by pooja; 12-23-04 at 02:16.

  10. #10
    Join Date
    Dec 2008
    Posts
    3

    Similar Query

    Input File:

    1,abc,xyx
    2,def,mno
    3,ghi,suv

    DB Table Structure:

    Col1 char
    col2 char
    col3 char
    col4 char
    col5 char

    Data in Table after BCP:

    col1 col2 col3 col4 col5

    1 abc xyz ab xy

    2 def mno de mn

    3 ghi suv gh su

    Basically the col4 and col5 are calculated values from col2 and col3 values.

    Does SQL Server BCP utility support such kind of operation? Any pointers will be appreciated.

  11. #11
    Join Date
    Mar 2007
    Posts
    72
    Hi,
    You can create a view as
    create view v1 as select new column order.... from orginal_table

    if you bcp your data which is in the different column order into the view, it will be in the right order.

    suda

Posting Permissions

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