BCP Error

• Jared

  SSC Guru

  Points: 61793

  More actions

  June 6, 2012 at 12:05 pm

  #1497817

  DBA_SQL (6/6/2012)

  ---

  My CSV file is of "|" limiter. I am trying using the following BCP command but receives error.

  SET IDENTITY_INSERT table1 On

  DECLARE @cmd nvarchar(500)

  SELECT @cmd = 'bcp slcdb.dbo.table1 IN "D:\table.csv" -c -t, -S @@servername -T'

  EXEC master..xp_cmdshell @cmd;

  SET IDENTITY_INSERT SLCAgenciestbl OFF

  Error:

  NULL

  Starting copy...

  SQLState = S1000, NativeError = 0

  Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file

  NULL

  0 rows copied.

  Network packet size (bytes): 4096

  Clock Time (ms.) Total : 1

  NULL

  So, I tried using openrowset command

  SELECT * FROM

  OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\..\..\Desktop;',

  'SELECT * from table.csv');

  Here I can able to see the data, but I am not receiving the data in right format. It is showing just 2 columns instead of 10 columns. I do not know how to insert format option in this query. Can some one help me in getting the output.

  No Idea, for small issues, making complex..

  Change this: SELECT @cmd = 'bcp slcdb.dbo.table1 IN "D:\table.csv" -c -t, -S @@servername -T'

  to this: SELECT @cmd = 'bcp slcdb.dbo.table1 IN "D:\table.csv" -c -t^| -S @@servername -T'

  Your file is not comma delimited, it is pipe delimited.

  Jared
  CE - Microsoft