Results 1 to 9 of 9

Thread: BCP Utility

  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: BCP Utility

    Hi all,

    I want to use the BCP utility to import data from a .dat file into my database. The .dat file contains a table called xv_Appointments containing the following fields:

    AppointmentKey
    SurgerySlotKey
    PatientKey
    Cancelled
    Continuation
    Deleted
    TimeArrived

    I would like to import only two of these fields into a table called tbl_Appointments e.g.

    AppointmentKey
    TimeArrived

    I can't seem to get the BCP util to do this. It only works if I import all of the fields from xv_Appointments. Does anyone know if this is possible?

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Replace IN with FORMAT and add "-f file_name.fmt" without quotes on the command line when issuing BCP statement.

    Open your favorite text editor and modify the format file to look something like this:
    Code:
    8.0
    7
    1	   SQLINT		0	   0	   "\t"					1	 AppointmentKey   ""
    2	   SQLCHAR	   0	   255	 "\t"					0	 SurgerySlotKey   SQL_Latin1_General_CP1_CI_AS
    3	   SQLCHAR	   0	   255	 "\t"					0	 PatientKey	   SQL_Latin1_General_CP1_CI_AS
    4	   SQLCHAR	   0	   255	 "\t"					0	 Cancelled		SQL_Latin1_General_CP1_CI_AS
    5	   SQLCHAR	   0	   255	 "\t"					0	 Continuation	 SQL_Latin1_General_CP1_CI_AS
    6	   SQLCHAR	   0	   255	 "\t"					0	 Deleted		  SQL_Latin1_General_CP1_CI_AS
    7	   SQLCHAR	   0	   255	 "\r\n"				  7	 TimeArrived	  SQL_Latin1_General_CP1_CI_AS
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Thanks for replying and you've certainly pointed me in the right direction. I've almost got it working but for a couple of problems. The first problem is that when it imports the Appointment key it seems to be adding a tab character or something to before the key e.g. 341084.80096.Appointment becomes 341084.80096.Appointment when imported to the new table. Also, I can't get it to import the date field 'TimeArrived' (please see attached), SQL server throws up the following error message:

    Server: Msg 4829, Level 16, State 1, Line 1
    Could not bulk insert. Error reading destination table column name for source column 8 in format file

    Even although the column name is correct...I have tried importing the other date fileds and it does the same thing.

    I have attached my .fmt file for you to have a look at.

    Thanks
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Posts
    88

    format file

    The second token in the format file (where you have SQLCHAR etc) refers to the format of the field IN THE HOST FILE, not on the server. If this a date in ASCII readable string format, e.g. "Jan 1 2004 10:00AM" or "2004-01-01" the this should be set to SQLCHAR

    see Books online : bcp utility / format files / Using format files

  5. #5
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Tried setting the date field to SQLCHAR (see attached file) but it's still giving me the same error message:

    Server: Msg 4829, Level 16, State 1, Line 1
    Could not bulk insert. Error reading destination table column name for source column 8 in format file '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\bcp.fmt'

    Here's the SQL I'm using:

    BULK INSERT tbl_UpdateTimeArrived FROM '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\xv_Appointment.dat'
    WITH (FORMATFILE = '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\bcp.fmt

    The only other thing that may affect it is the .dat file in question is from a SQL Server 6.5 database and I'm importing it into a SQL 2000 database.
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Posts
    88
    maybe your SQLBIT columns should be SQLCHAR as well...

  7. #7
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Still didn't work but I'm slowly getting to the bottom of it. Tried importing a non-date, non-bit field and it imported fine (again see attached) - so it seems to be the date fields that are causing the problem....Any ideas?
    Attached Files Attached Files

  8. #8
    Join Date
    Feb 2004
    Posts
    88
    can you attach say top 5 lines of your data file ?

  9. #9
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    I've figured it out. I need the same number of fields in the destination table as the table in the .dat file. Even although I only want to import two fileds. In effect, the destination table needs to be an exact replica regardless of whether I want to use all the fields or not!!

    If anybodyt knows any different then please let me know.

Posting Permissions

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