CODEWITHSUNDEEP
sql-servercsvexport-to-csvbcp
Data Engineer
Data Engineer

Reputation: 827

BCP utility SQL Server Export to CSV - destination CSV file never gets created

I'm experiencing the following error when using the bcp utility in SQL Server 2017.

I need to export data from a view in SQL Server to a .CSV file using a comma as the column delimiter. My data contains comma in certain columns. So I used " as a column qualifier when created a view using [+'"' column_name + '"'].

I tried 3 different ways:

Option 1:

declare @sql varchar(8000)
select @sql = 'bcp "SELECT * FROM MyDB.dbo.MyTable
          WHERE Rn BETWEEN 1 AND 100 ORDER BY Rn"
       queryout "E:\MyFolder\MyFileName.txt" -c -t, -T -S' + @@servername 
exec master..xp_cmdshell @sql

Option 2:

declare @sql varchar(8000)
select @sql = 'bcp "SELECT * FROM MyDB.dbo.MyTable
          WHERE Rn BETWEEN 1 AND 100 ORDER BY Rn"
       queryout "E:\MyFolder\MyFileName.txt" -c -t, [MyServer_Name] -T -S'
exec master..xp_cmdshell @sql

Option 3

   declare @sql varchar(8000)
   select @sql = 'bcp "SELECT * FROM MyDB.dbo.MyTable
              WHERE Rn BETWEEN 1 AND 100 ORDER BY Rn"
           queryout "E:\MyFolder\MyFileName.txt" -c -t, [MyServer_Name] -T -S ' + '@@servername' 
   exec master..xp_cmdshell @sql

In every case I'm getting the same output but the CSV file never gets created:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]  [-l login timeout]
  NULL

PS: I found alternative solution to import data into .CSV from SQL Server. But the problem here is that sqlcmd - unlike bcp - does not accept two characters as text qualifier. I decided to use a text qualifier of 2 chars ~^ following comma as a field separator.

 exec master..xp_cmdshell 'sqlcmd -s -W -Q "set nocount on;select * from MyTable" | findstr /v /c:"-" /b > "E:\MyFile.csv"'

where -s is col_separator

Specifies the column-separator character. The default is a blank space. This option sets the sqlcmd scripting variable SQLCMDCOLSEP. To use characters that have special meaning to the operating system such as the ampersand (&), or semicolon (;), enclose the character in quotation marks ("). The column separator can be any 8-bit character.( https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15 )

Upvotes: 0

Views: 3296

Answers (2)

Data Engineer
Data Engineer

Reputation: 827

P.S.

I found alternative solution to Import data into CSV fro SQL Server. But the problem here is that sqlcmd unlike bcp does not except to characters as a text qualifier. I decided to use a text qualifier of 2 chars ~^ following comma as a filed separator.

exec master..xp_cmdshell 'sqlcmd -s -W -Q "set nocount on;select * from MyTable" | findstr /v /c:"-" /b > "E:\MyFile.csv"'

Where -s is col_separator Specifies the column-separator character. The default is a blank space. This option sets the sqlcmd scripting variable SQLCMDCOLSEP. To use characters that have special meaning to the operating system such as the ampersand (&), or semicolon (;), enclose the character in quotation marks ("). The column separator can be any 8-bit character.( https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15 )

Also we have another alternative solution that includes headers, but there you have no control on how you pass your query.

set BCP_EXPORT_SERVER=YourServerName
set BCP_EXPORT_DB=YourDBName
set BCP_EXPORT_TABLE=YourTableName

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='%BCP_EXPORT_TABLE%'; select @colnames;" queryout HeadersOnly.csv -c -t, -T -S%BCP_EXPORT_SERVER%
BCP %BCP_EXPORT_DB%.dbo.%BCP_EXPORT_TABLE% out TableDataWithoutHeaders.csv -c -t"|", -T -S%BCP_EXPORT_SERVER%

set BCP_EXPORT_SERVER=
set BCP_EXPORT_DB=
set BCP_EXPORT_TABLE=

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv

del HeadersOnly.csv
del TableDataWithoutHeaders.csv

Upvotes: 0

AlwaysLearning
AlwaysLearning

Reputation: 8809

Command lines supplied to xp_cmdshell cannot span multiple lines of text, they need to be specified completely on a single line. Try building the command as a series of concatenated strings like this:

declare @sql varchar(8000) =
    'bcp' +
    ' "select * from MyDB.dbo.MyTable WHERE Rn BETWEEN 1 AND 100 order by Rn"' +
    ' queryout' +
    ' "E:\MyFolder\MyFileName.txt"' +
    ' -c -t, -T -S' + @@servername 
exec master..xp_cmdshell @sql

When concatenating the strings be mindful of where you need to include white space between each segment so that command line parameters don't run into each other.

Upvotes: 1

Related Questions