Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    1

    Unanswered: Bulk insert from non-delimited file

    Hi,
    I have text file which is non-delimited, and I need to import it into my table.
    Table is:
    CREATE TABLE TempTable
    (
    Field1 char(10),
    Date1 date,
    Field2 varchar(10),
    Number1 int
    )


    and my import file:
    myfile:
    abcdefghij2011-01-01abcdefghij98765
    abcdefghij2011-01-02abcdefghij99999
    abcdefghij2011-01-03abcdefghij00033
    abcdefghij2011-01-04abcdefghij12345
    abcdefghij2011-01-05abcdefghij00001


    I need to import first 10 characters to Field1 column, next 10 to Date1, next 10 to Field2, and next 5 to Number1,
    I've tried with openrowset and format file, but doesn't work

    Regards

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    use a format file

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    e.g.
    Code:
    G:\>type c:\tmp\TempTable.fmt
    9.0
    4
    1       SQLCHAR       0       10      ""       1     txt1               ""
    2       SQLCHAR       0       10      ""       2     Date1              ""
    3       SQLCHAR       0       10      ""       3     txt2               ""
    4       SQLCHAR       0       5       "\r\n"   4     Num1               ""
    
    G:\>bcp tempdb..TempTable in c:\tmp\myfile -T -S mysrv -f c:\tmp\TempTable.fmt
    
    Starting copy...
    
    5 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1      Average : (5000.00 rows per sec.)

Tags for this Thread

Posting Permissions

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