BCP to import tab delimited file with header

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » BCP to import tab delimited file with header

BCP to import tab delimited file with header

Rate Topic

Display Mode

Topic Options

Author

Message

dsandhyarao

Posted Tuesday, March 19, 2013 6:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 4:06 AM
Points: 103, Visits: 105

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)

Post #1432608

Lowell

Posted Tuesday, March 19, 2013 7:01 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 11,638, Visits: 27,721

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1432622

dsandhyarao

Posted Tuesday, March 19, 2013 7:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 4:06 AM
Points: 103, Visits: 105

HI,
I used double quotes, still same error.

Post #1432651

Lowell

Posted Tuesday, March 19, 2013 7:58 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 11,638, Visits: 27,721

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1432665

dsandhyarao

Posted Wednesday, March 20, 2013 8:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 4:06 AM
Points: 103, Visits: 105

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 .

Post #1433240

dsandhyarao

Posted Thursday, March 21, 2013 9:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 4:06 AM
Points: 103, Visits: 105

is there some thing in order i am misssing?

Post #1433852

Lowell

Posted Thursday, March 21, 2013 9:20 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 11,638, Visits: 27,721

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1433860

« Prev Topic | Next Topic »

Permissions