Post reply

BCP Import Help

  • April 14, 2016 at 7:20 am

    #309911

    Hi all,

    I am trying to import a data file using bcp. The command I'm started with:

    bcp TestDB.dbo.BASE in "C:\Temp\BASE.txt" -c -T -t ","

    But the data file only contains 4 columns, whereas the table consists of 5 (I added a datetime column). So I created a format file for BCP to use, but even this doesn't work:

    bcp TestDB.dbo.BASE in "C:\Temp\BASE.txt" -c -T -t "," -fC:\Temp\base_format.fmt

    The data file BASE.txt:

    4,"TEST","LASTNAME","321 BLAH STREET"

    3,"JOHN","SMITH","123 NOWHERE STREET"

    5,"JANE","SMITH","500 COOL STREET"

    The format file I created using BCP TestDB.dbo.BASE format nul -T -n -f c:\temp\base_format.fmt:

    11.0

    5

    1 SQLBIGINT 1 8 "" 1 ID ""

    2 SQLCHAR 2 50 "" 2 FNAME SQL_Latin1_General_CP1_CI_AS

    3 SQLCHAR 2 50 "" 3 LNAME SQL_Latin1_General_CP1_CI_AS

    4 SQLCHAR 2 50 "" 4 STREET SQL_Latin1_General_CP1_CI_AS

    5 SQLDATE 1 3 "" 5 datetime_stamp ""

    And the table looks like:

    CREATE TABLE [dbo].[BASE](

    [ID] [bigint] NULL,

    [FNAME] [varchar](50) NULL,

    [LNAME] [varchar](50) NULL,

    [STREET] [varchar](50) NULL,

    [datetime_stamp] [date] NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[BASE] ADD DEFAULT (getdate()) FOR [datetime_stamp]

    GO

    TLDR; I am trying to import a data file containing 4 columns into a table the contains 5 columns, where the 5th column is one that stores datetime defaulting to getdate() upon insert.

  • April 14, 2016 at 7:57 am

    #1871981

    Seems to be answered HERE[/url].

    I'll be researching more on the format file to understand this better.

  • April 14, 2016 at 8:14 am

    #1871986

    In the format file, you should define the format of the data file, not the table.

    Here's what I used to make it work:

    11.0

    4

    1 SQLBIGINT 0 8 ",\"" 1 ID ""

    2 SQLCHAR 0 50 "\",\"" 2 FNAME SQL_Latin1_General_CP1_CI_AS

    3 SQLCHAR 0 50 "\",\"" 3 LNAME SQL_Latin1_General_CP1_CI_AS

    4 SQLCHAR 0 50 "\"\r" 4 STREET SQL_Latin1_General_CP1_CI_AS

    Be sure to have an empty line at the end of both your data file and format file to avoid errors.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 14, 2016 at 8:28 am

    #1871995

    Thank you, sir. This helps greatly.

  • April 14, 2016 at 9:30 am

    #1872033

    One more question, Luis Cazares. When I import, why doesn't the INT (in the first column of my data file) read in as is? When I run the import command, the ID's translate to something else. Am I missing a BCP parameter to avoid this? Or in the format file?

    I tried to change SQLBIGINT to SQLINT in the format file, and re-created the table with the ID column as INT data type.

  • April 14, 2016 at 9:52 am

    #1872052

    I honestly don't know why it does that.

    I know however, that you can change the format file and set the column as SQLCHAR (leaving your table intact) and let it do an implicit conversion. That should give you the correct results.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 14, 2016 at 10:03 am

    #1872059

    Thanks again, this was the case.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply