Importing Flat file to table using format file

• Sathish Kumar. Rajaram

  Mr or Mrs. 500

  Points: 581

  More actions

  March 21, 2006 at 5:21 am

  #112333

  Hi

  I have a table say, "UserEvents". I need to import the values from a flat file to this table using BCP. The flat file will have some additional field values and the order will not be same as it appears in this table.

  Now I want to create a format file so that the values can be imported to the table without any error with the help of this format file.

  Can anyone advice me on how to create a format file for this.

  The structure of the table and the flat file is as given below.

  Column_name   Type

  UserEventID   int

  UserID        int

  EventID       int

  EventDate     smalldatetime

  EventTitle    varchar(25)

  EventNotes    varchar(200)

  EventLocation varchar(25)

  RemindViaMail bit

  RemindOn      tinyint

  RepeatEvent   tinyint

  RepeatTill    smalldatetime

  Field          Length Start  End

  UserEventID    4      1      4

  UserID         4      5      8

  DummyFld1      10     9      18

  EventID        4      19     22

  EventDate      8      23     30

  EventTitle     25     31     55

  EventNotes     200    56     255

  EventLocation  25     256    280

  DummyFld2      25     281    305

  RemindViaMail  1      306    306

  RemindOn       1      307    307

  RepeatEvent    1      308    308

  RepeatTill     8      309    316

  DummyFld3      34     317    350

  DummyFld1, DummyFld2 and  DummyFld3 were the fields to be elimated while importing the file.

  Thanks

  Sathish

  S A T ...
  Sathish's Blog

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  March 21, 2006 at 7:13 am

  #627856

  It's been a while since I made a format file, but to skip a column, in the format file for that column, specify a length of zero and no delimiter

  ......

  1       SQLCHAR       0       17      ";;"                       1     col1       

  2       SQLCHAR       0        0       ""                         2     dummycol            

  3       SQLCHAR       0       26      "\r\n"                    3     col2

  The above will skip column 2 since it's zero length and have no delimiter specified.

  /Kenneth

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  March 22, 2006 at 8:24 am

  #628112

  Not quite.  To skip columns in the input file, use the number of columns to skip as the length and zero for the output field ordinal.

  1  SQLCHAR  0  4  ""  1  UserEventID  ""

  2  SQLCHAR  0  4  ""  2  UserID  ""

  3  SQLCHAR  0  10 "" 0  Skip  ""

  4  SQLCHAR  0  4  ""  3  EventID  ""

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  March 23, 2006 at 3:25 am

  #628295

  Ah yes, that is probably more accurate.

  I had an old example lying around, but in it the last few columns were being skipped, not any in between, so I missed the column numbering part.

  /Kenneth