BCP command to import csv file

• world2new

  SSC Veteran

  Points: 290

  More actions

  November 22, 2011 at 10:24 pm

  #247381

  Hi,

  I currently have an issue trying to use bcp command to import data from a csv file. if the csv file have double quote in the data, it will not import the data in. But i am not able to remove the double quotes as it is crucial in keeping the data accurately. for e.g.

  "Tim Tam","Pim , Pam"

  if i remove the double quotes, it would identify and treat Pim and Pam as different data column.

  Is there anyway i can tweak my bcp command to accomodate the double quotes?

  Currently, this is the command i am using.

  bcp %DB%.dbo.information in "%MAP_PATH%%FILENAME_EQUIPMENT%" -U%user% -P%pswd% -S%SERVER% /t, -F 1 -c -o %OPATH%temp-log3.log

  Thanks all in advance..

• Devendrakumar

  SSC-Forever

  Points: 42493

  More actions

  November 22, 2011 at 11:34 pm

  #1411953

  Creating a format file would be a good idea.

  Creating a Format File

  http://msdn.microsoft.com/en-us/library/ms191516.aspx

  Sample File:

  10.0

  4

  1 SQLSMALLINT 0 2 "" 1 DepartmentID ""

  2 SQLNCHAR 2 100 "" 2 Name SQL_Latin1_General_CP1_CI_AS

  3 SQLNCHAR 2 100 "" 3 GroupName SQL_Latin1_General_CP1_CI_AS

  4 SQLDATETIME 0 8 "" 4 ModifiedDate ""

• world2new

  SSC Veteran

  Points: 290

  More actions

  November 23, 2011 at 2:27 am

  #1412007

  quite confusing with the format file.

  For my case, lets say i have 2 fields. one field is with comma, the other field is with a line break embedded within double quotes, how do i make use of the terminator correctly? my below format table is it correct?

  9.0

  2

  1 SQLCHAR 0 7 "," 1 Field1 ""

  2 SQLCHAR 0 100 "" 2 Field2 ""