Post reply

BCP to import tab delimited file with header

  • March 19, 2013 at 6:35 am

    #275394

    Hi, i am trying to load a file which is a tab delimited file , which has a header row in it.so using F with 2 to consider second row, and create a error file.

    i am getting a error message

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '.'.

    please let me know the correct one.

    code used

    ::

    Declare @sql varchar(8000),@IncomingPath varchar(500),@FileName varchar(500)

    set @FileName='12272012_114537_AB123.txt'

    set @IncomingPath='e:\feeds\HH_feeds\procen\incoming\'

    SET @sql = 'bcp [dbo].ABt_file_load_2012 in '''+@IncomingPath+ @FileName+''' -t''|'' -r'''' -F 2 -e'+@IncomingPath+'ErrorLog\'+ @FileName+'.ERR -T -S ' + @@SERVERNAME

    print @sql

    EXEC (@sql)

  • March 19, 2013 at 7:01 am

    #1598403

    the issue is subtle: you need to use dbl quotes, and not two single quotes, to identify teh field and row delimiters:

    -t''|'' -r''\n''

    --should be

    -t"|" -r"\n"

    Declare @sql varchar(8000),@IncomingPath varchar(500),@FileName varchar(500)

    set @FileName='12272012_114537_AB123.txt'

    set @IncomingPath='e:\feeds\HH_feeds\procen\incoming\'

    SET @sql = 'bcp [dbo].ABt_file_load_2012 in '''+@IncomingPath+ @FileName+''' -t"|" -r"\n" -F 2 -e'+@IncomingPath+'ErrorLog\'+ @FileName+'.ERR -T -S ' + @@SERVERNAME

    print @sql

    EXEC (@sql)

    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!

  • March 19, 2013 at 7:47 am

    #1598428

    HI,

    I used double quotes, still same error.

  • March 19, 2013 at 7:58 am

    #1598441

    look your command over again, it's got multiple places where other dual-single quotes exist.

    try this line instead:

    SET @sql ='bcp DatabaseName.[dbo].ABt_file_load_2012 in "' + @IncomingPath + @FileName + '" -c -t"|" -r"\n" -T -S ' + @@SERVERNAME

    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!

  • March 20, 2013 at 8:09 am

    #1598921

    Hi,

    I tried with double quotes in all places, but same error.

    also the bcp is working for pipe delimted file,

    but when i do for tab delimited it is giving me the error,

    is there any order in which i need to palce the options like -t,-F

    please suggest .

  • March 21, 2013 at 9:11 am

    #1599436

    is there some thing in order i am misssing?

  • March 21, 2013 at 9:20 am

    #1599442

    dsandhyarao (3/21/2013)

    is there some thing in order i am misssing?

    Not sure, we can't tell from this side;

    you said you got it working for pipe delimited, and the only difference between pipe delimited and tab delimited is the -t"|" becomes -t"\t"

    if you posted your code, we can offer some suggestions if you are still stuck.

    specific error messages let us dig in deeper, as well.

    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!

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

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