Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP to import tab delimited file with header Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 6:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 12:40 PM
Points: 105, Visits: 109
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
Posted Tuesday, March 19, 2013 7:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 12,908, Visits: 32,005
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
Posted Tuesday, March 19, 2013 7:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 12:40 PM
Points: 105, Visits: 109
HI,
I used double quotes, still same error.
Post #1432651
Posted Tuesday, March 19, 2013 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 12,908, Visits: 32,005
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
Posted Wednesday, March 20, 2013 8:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 12:40 PM
Points: 105, Visits: 109
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
Posted Thursday, March 21, 2013 9:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 12:40 PM
Points: 105, Visits: 109
is there some thing in order i am misssing?
Post #1433852
Posted Thursday, March 21, 2013 9:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 12,908, Visits: 32,005
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 »

Add to briefcase

Permissions Expand / Collapse