BCP comma delimited file into a table

  • I must be overlooking something really simple, but I am stuck. keep getting the error:

    Error = [Microsoft][SQL Server Native Client 11.0]BCP host-files must contain at least one column

    Here is my bcp:

    bcp mydatabase.dbo.stg_assess_value in AVALUE.TXT -S myserver -U myusername -P mypassword -f Assess_Value.fmt

    Format file: Assess_Value.fmt

    7.0

    2

    1SQLCHAR04","1tax_year

    2SQLCHAR05"\r"2fips_code

    Input file: AVALUE.TXT

    2013,22067

    2013,22068

    2013,22069

    2013,22060

    2013,22061

    SQL Server table: mydatabase.dbo.stg_assess_value

    CREATE TABLE [dbo].[stg_assess_value](

    [tax_year] [decimal](4, 0) NOT NULL,

    [fips_code] [decimal](5, 0) NOT NULL

    )

    I am using SQL Server 2012, but I am not doing anything version specific.

    Any help would be appreciated. Thanks.

  • At a guess, unless you are terminating rows with just a carriage return instead of a carriage return and line feed, the last line of your format file should read

    2SQLCHAR 05"/r/n"2fips_code

    Note... the forward slashes should really be backward slashes, but they keep getting translated to something else, so I presume I need to escape them somehow in my reply, but don't know how.

  • Ian, thanks for the reply.

    I tried \r on the last line of the format file. I get the same error message. 🙁

  • i see two issues:

    first, teh data is so simple, i don't see any need to use a format file; i only use format files if it's delimited with quotes or something.

    second, there's no path information for the files, so unless you actually placed them in the same folder that bcp.exe exists in, i'd expect it to fail.

    bcp mydatabase.dbo.stg_assess_value in c:\Data\AVALUE.TXT -S myserver -U myusername -P mypassword -f c:\Data\Assess_Value.fmt

    i would just switch to bulk insert for this one; i think it would be faster, and it's more TSQL-ish to me anyway.:

    BULK INSERT stg_assess_value FROM 'c:\Data\AVALUE.TXT'

    WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = '\n',

    FIRSTROW = 1

    )

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell, thanks for the reply.

    I actually simplified the data for the posting here. I actually have about 20 columns of data and some with quotes. So, I decided to use cbp with a format file.

    I thought this would be simple and someone would have pointed out something that I missed...

    Driving me crazy...

  • I figured it out...

    For SQL 2012:

    1. The format file needed to start with 11.0.

    2. I removed any tabs between the columns in the format file (not sure if this was necessary)

    3. I had to add quotes at the end of each line in the format file

    resulting format file looked like this:

    11.0

    2

    1SQLCHAR 0 4 "," 1 tax_year ""

    2 SQLCHAR 0 5 "\r" 2 fips_code ""

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

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