BCP Utility Comma
Question
Wednesday, October 15, 2014 4:42 PM
I have the following problem. I need to create a .csv file with SQL (with the bcp utility). I can create this .csv file and the following data is inserted:
1","ad","dsa","333","","2","0","","","","""
I want to avoid this double quotes. I want only double quotes if there is a comma in one field. For better understanding, the following 3 cases must be done.
Fields containin commas can be surounded by quotation marks, these must be double quotation marks (“ ”) and not single (‘ ’) e.g.
“a, b, c” would be accepted as 1 single field but a, b, c would be 3 separate fields.
In adition, any double quotation marks which need to be displayed in a field must also be surounded by double quotation marks e.g.
If an address should display as “The Street”, this should be sent as ““The Street””
Apostrophes can be sent in the file e.g.
A client with surname O’Clay and forename Jenny with a free text field holding “comma, example” would be sent in the file as O’Clay,Jenny,”comma,example”.
This is my code:
bcp TESTPROJECT.dbo.table out c:\aa.txt -c -t"\,\" -r"\\n\" -S .\sql2014 -U username -P password
So the problem is in the -t"\,\" -r"\\n\" part but how I can do this?
All replies (10)
Monday, October 20, 2014 2:11 AM ✅Answered | 2 votes
Use a custom format file
In any case you wont be able to selective add "" only to some of values within a column. Either it has to be for the entire column or ignore it altogether.
If you cant predict which columns can have comma values inside data then best solution is to add " as text qualifiers to all of them.
See
http://msdn.microsoft.com/en-IN/library/ms191516(v=sql.110).aspx
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html
Please Mark This As Answer if it helps to solve the issue Visakh http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
Monday, October 20, 2014 2:16 AM ✅Answered | 2 votes
Try SSIS Import/Export Wizard:
http://www.sqlusa.com/bestpractices/ssis-wizard/
Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014
Monday, October 20, 2014 5:11 AM ✅Answered | 2 votes
What you describe is not possible to do with BCP via the command line. BCP uses the same format for all rows. Quoted strings are either on or off. There is no way to selectively turn them on without using a format file.
In addition, whatever you import the data into, will have a problem if you do what you describe.
Monday, October 20, 2014 7:54 AM ✅Answered | 2 votes
This is fully explained using import in this article (there is no problem with that):
Using Bulk Insert to Import Inconsistent Data Format (Using Pure T-SQL)
http://social.technet.microsoft.com/wiki/contents/articles/19709.using-bulk-insert-to-import-inconsistent-data-format-using-pure-t-sql.aspx
But you want to export to CSV. This can (export Inconsistent Data Format) can be done easily, but will be very heavy operation and will work slowly.
The idea is the same as the article: You can just create your own string from the "row data", by combining all columns into one nvarchar value (this is like simple select query and if you will post DDL+DML then we could help with this), and next just export this result SET as CSV. In the query you can use CASE in order to chose when to add quotation marks or not to add it.
* I will not do this personally, and I will prefer to use the build in export and then if need I will use C# to clear the text, but this solution will work for you. I agree with Kalman in this issue, and if you are not a developer then probably your best option is to use SSIS.
| Ronen Ariely [Personal Site] [Blog] [Facebook] |
Monday, October 20, 2014 11:49 AM ✅Answered | 3 votes
Hello ,
Firstly I am requesting you to see the below article :
Details with example has given there.
https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/
Monday, October 20, 2014 1:54 AM | 2 votes
Have you tried SSIS?
Friday, November 7, 2014 11:41 AM | 1 vote
Hi Soner, Is this still an Issue ?
Regards, RSingh
Friday, November 7, 2014 2:48 PM | 1 vote
I created a proc, that created a view, and then bcp used the view, instead of the table. It worked well, avoided many issues and allowed unlimited customization. My proc is below.
CREATE PROCEDURE [dbo].[ifs_ExportTablesCreateView]/****************************************************************************************ifs_ExportTablesCreateViewDescription:Read in a tablename or viewname with parameters and create view v_ExportTable, which contains the formated data. The view will typically be used by bcp or another download utility to export the SQL Table data to flat files. bcp and most of the other download utilities do not have theoptions include or exclude column headings, enclose fields in double quotes, format dates,select the values for integer NULL values. Also, most have a limit to the size of theSELECT statement that can be used. With some wider tables (like ddabal) can run into this sizelimit issue trying to create formatted SELECT statements. By creating a view with the formatingalready included, the bcp issues a "SELECT * from v_ExportTables". Parameters:@pTableName VARCHAR(128) - table or view to create v_ExportTable from@pColumnHeader INT =1 - include column headers in the first row@pDoubleQuoteFields INT = 1 - put double quotes " around all column values including column headers@pDouble_EmbeddedDoubleQuotes INT = 1 - This is usually used with @pDoubleQuoteFields INT = 1. 'ab"c"d would be 'ab""c""d. @pNumNULLValue VARCHAR(1) = '0' - NULL number data types will export this value instead of bcp default of ''@pDateTimeFormat INT = 121 - DateTime data types will use this format valueExample:EXEC ifs_ExportTablesCreateView 'ddabal', 1, 1, 1, '0', 121*****************************************************************************************/@pTableName VARCHAR(128),@pColumnHeader INT = 1,@pDoubleQuoteFields INT = 1,@pDouble_EmbeddedDoubleQuotes INT = 1, @pNumNULLValue VARCHAR(1) = '0',@pDateTimeFormat INT = 121ASBEGINDECLARE @columnname varchar(128)DECLARE @columnsize intDECLARE @data_type varchar(128)DECLARE @HeaderRow nvarchar(max) DECLARE @ColumnSelect nvarchar(max)DECLARE @SQLSelect nvarchar(max)DECLARE @SQLCommand nvarchar(max)DECLARE @ReturnCode INTDECLARE @Note VARCHAR(500)SET @Note = 'ifs_ExportTablesCreateView Input Parameters: @Tablename-''' + @pTableName + '''; @pColumnHeaderDBName-''' + CONVERT(VARCHAR, @pColumnHeader) + ''';'SET @Note = @Note + ' @pDoubleQuoteField-''' + CONVERT(VARCHAR,@pDoubleQuoteFields) + '''; @pDouble_EmbeddedDoubleQuotes-''' + CONVERT(VARCHAR, @pDouble_EmbeddedDoubleQuotes) + ''';'SET @Note = @Note + ' @pNumNULLValue-''' + CONVERT(VARCHAR,@pNumNULLValue) + '''; @pDateTimeFormat-''' + CONVERT(VARCHAR, @pDateTimeFormat) + ''''EXECUTE ifs_RunHistoryInsertV2 @pTableName, @Note, 'ExportTablesSQL', 1 DECLARE db_cursor CURSOR FOR SELECT COLUMN_NAME, ISNULL(Character_maximum_length,0), Data_type FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE TABLE_NAME = @pTableName AND TABLE_SCHEMA='dbo' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @ColumnName, @ColumnSize, @Data_typeSET @HeaderRow = ''SET @ColumnSelect = '' -- Loop through each of the @pTableColumns to build the SELECT StatementWHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY -- Put double quotes around each field - example "MARIA","SHARAPOVA" IF @pDoubleQuoteFields = 1 BEGIN -- Include column headers in the first row - example "FirstName","LastName" IF @pColumnHeader = 1 SET @HeaderRow = @HeaderRow + '''"' + @ColumnName + '"'' as ''' + @columnname + ''',' -- Unsupported Export data type returns "" - example "", IF @Data_Type in ('image', 'varbinary', 'binary', 'timestamp', 'cursor', 'hierarchyid', 'sql_variant', 'xml', 'table', 'spatial Types') SET @ColumnSelect = @ColumnSelect + '''""'' as [' + @ColumnName + '],' -- Format DateTime data types according to input parameter ELSE IF @Data_Type in ('datetime', 'smalldatetime', 'datetime2', 'date', 'datetimeoffset') -- example - CASE when [aaa] IS NULL THEN '""' ELSE QUOTENAME(CONVERT(VARCHAR,[aaa], 121), CHAR(34)) END AS [aaa], SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN ''""'' ELSE QUOTENAME(CONVERT(VARCHAR,[' + @columnname + '],' + CONVERT(VARCHAR,@pDateTimeFormat) + '), CHAR(34)) END AS [' + @ColumnName + '],' -- SET Numeric data types with NULL value according to input parameter ELSE IF @Data_Type in ('bigint', 'numeric', 'bit', 'smallint', 'decimal', 'smallmoney', 'int', 'tinyint', 'money', 'float', 'real') -- example - CASE when [aaa] IS NULL THEN '"0"' ELSE QUOTENAME([aaa], CHAR(34)) END AS [aaa], SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN ''"' + @pNumNULLValue + '"'' ELSE QUOTENAME([' + @columnname + '], CHAR(34)) END AS [' + @ColumnName + '],' ELSE -- Double embedded double quotes - example "abc"d"ed" to "abc""d""ed". Only applicible for character data types. IF @pDouble_EmbeddedDoubleQuotes = 1 BEGIN QUOTENAME was not only adding double quotes around the field, it was also doubling up internal quotes. So stopped using it. plus at this point, only character data is coming through so do not need quotename to handle other data types. QUOTENAME is preferred since it works on all data type and handles embedded double quotes, but it only handles fields up to length 128. --IF @columnsize < 129 example - CASE when [aaa] IS NULL THEN '""' ELSE QUOTENAME(REPLACE([aaa], '"', '""'), CHAR(34)) END AS [aaa], --SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN ''""'' ELSE QUOTENAME(REPLACE([' + @ColumnName + '],''"'',''""''),CHAR(34)) END AS [' + @ColumnName + '],' --ELSE Alternative to QUOTENAME. Only works on character (string) data types, but have eliminiated all other currently known data types so should be OK. -- example - CASE when [aaa] IS NULL THEN '""' ELSE '"' + REPLACE([aaa],'"','""') + '"' END AS [aaa], SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @ColumnName + '] IS NULL THEN ''""'' ELSE ''"'' + REPLACE([' + @ColumnName + '],''"'',''""'') + ''"'' END AS [' + @ColumnName + '],' END -- DO NOT PUT Double embedded double quotes - example "abc"d"ed" unchanged to "abc"d"ed" ELSE BEGIN QUOTENAME was not only adding double quotes around the field, it was also doubling up internal quotes. So stopped using it. plus at this point, only character data is coming through so do not need quotename to handle other data types. QUOTENAME is preferred since it works on all data type, but it only handles fields up to length 128. --IF @columnsize < 129 example - CASE when [aaa] IS NULL THEN '""' ELSE QUOTENAME([aaa], CHAR(34)) END AS [aaa], --SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN ''""'' ELSE QUOTENAME([' + @ColumnName + '], CHAR(34)) END AS [' + @ColumnName + '],' Alternative to QUOTENAME. Only works on character (string) data types, but have eliminiated all other currently known data types so should be OK. --ELSE -- example - CASE when [aaa] IS NULL THEN '""' ELSE '"' + [aaa] + '"' END AS [aaa], SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @ColumnName + '] IS NULL THEN ''""'' ELSE ''"'' + [' + @ColumnName + '] + ''"'' END AS [' + @ColumnName + '],' END END -- DO NOT PUT double quotes around each field - example MARIA,SHARAPOVA ELSE BEGIN -- Include column headers in the first row - example "FirstName","LastName" IF @pColumnHeader = 1 SET @HeaderRow = @HeaderRow + '''' + @ColumnName + ''' as ''' + @columnname + ''',' -- Unsupported Export data type returns '' - example '', IF @Data_Type in ('image', 'varbinary', 'binary', 'timestamp', 'cursor', 'hierarchyid', 'sql_variant', 'xml', 'table', 'spatial Types') SET @ColumnSelect = @ColumnSelect + ''''' as [' + @ColumnName + '],' -- Format DateTime data types according to input parameter ELSE IF @Data_Type in ('datetime', 'smalldatetime', 'datetime2','date', 'datetimeoffset') -- example - CASE when [aaa] IS NULL THEN '''' ELSE CONVERT(VARCHAR,[aaa], 121) END AS [aaa], SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN '''' ELSE CONVERT(VARCHAR,[' + @columnname + '],' + CONVERT(VARCHAR,@pDateTimeFormat) + ') END AS [' + @ColumnName + '],' -- SET Numeric data types with NULL value according to input parameter ELSE IF @Data_Type in ('bigint', 'numeric', 'bit', 'smallint', 'decimal', 'smallmoney', 'int', 'tinyint', 'money', 'float', 'real') -- example - CASE when [aaa] IS NULL THEN '"0"' ELSE CONVERT(VARCHAR, [aaa]) END AS [aaa], SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN ''' + @pNumNULLValue + ''' ELSE CONVERT(VARCHAR,[' + @columnname + ']) END AS [' + @ColumnName + '],' ELSE BEGIN -- Double embedded double quotes - example "abc"d"ed" to "abc""d""ed". Only applicible for character data types. IF @pDouble_EmbeddedDoubleQuotes = 1 -- example - CASE when [aaa] IS NULL THEN '' ELSE CONVERT(VARCHAR,REPLACE([aaa],'"','""')) END AS [aaa], SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN '''' ELSE CONVERT(VARCHAR,REPLACE([' + @columnname + '],''"'',''""'')) END AS [' + @ColumnName + '],' ELSE -- example - CASE when [aaa] IS NULL THEN '' ELSE CONVERT(VARCHAR,[aaa]) END AS [aaa], SET @ColumnSelect = @ColumnSelect + 'CASE WHEN [' + @columnname + '] IS NULL THEN '''' ELSE CONVERT(VARCHAR,[' + @columnname + ']) END AS [' + @ColumnName + '],' END END FETCH NEXT FROM db_cursor INTO @ColumnName, @ColumnSize, @Data_Type END TRY BEGIN CATCH SET @Note = 'Error: Creating SELECT. ' + @Note + ' Error Number/Message - ' + CAST(Error_Number() AS VARCHAR(5)) + ' ' + Error_Message() EXECUTE ifs_RunHistoryUpdateV2 @pTableName, @Note, 'Fail', 0 RETURN (1) END CATCH END CLOSE db_cursor DEALLOCATE db_cursor BEGIN TRY -- remove last comma IF @pColumnHeader = 1 SET @HeaderRow = SUBSTRING(@HeaderRow , 1, LEN(@HeaderRow ) - 1) SET @ColumnSelect = SUBSTRING(@ColumnSelect, 1, LEN(@ColumnSelect) - 1) -- Put on the finishing touches on the SELECT IF @pColumnHeader = 1 SET @SQLSelect = 'SELECT ' + @HeaderRow + ' UNION ALL ' + 'SELECT ' + @ColumnSelect + ' FROM [' + @pTableName + ']' ELSE SET @SQLSelect = 'SELECT ' + @ColumnSelect + ' FROM [' + @pTableName + ']' diagnostics PRINT truncates at 4k or 8k, not sure, my tables have many colummns --PRINT @SQLSelect --DECLARE @END varchar(max) = RIGHT(@SQLSelect, 3000) --PRINT @end --EXECUTE sp_executesql @SQLSelect -- drop view if exists -- using view because some tables are very wide. one of my tables had a 33k select statement SET @SQLCommand = ' IF EXISTS (SELECT * FROM SYS.views WHERE name = ''v_ExportTable'') BEGIN DROP VIEW v_ExportTable END' EXECUTE @ReturnCode = sp_executesql @SQLCommand IF @returncode = 1 BEGIN SET @Note = 'Error: @ReturnCode=1 from DROP VIEW. ' + @Note + ' @SQLCommand: ' + @SQLCommand EXECUTE ifs_RunHistoryUpdateV2 @pTablename, @Note, 'Fail', 0 RETURN (1) END -- create the view SET @SQLCommand = ' CREATE VIEW v_ExportTable AS ' + @SQLSelect -- diagnostics --print @sqlcommand EXECUTE @ReturnCode = sp_executesql @SQLCommand IF @returncode = 1 BEGIN SET @Note = 'Error: @ReturnCode=1 from CREATE VIEW. ' + @Note + ' @SQLCommand: ' + @SQLCommand EXECUTE ifs_RunHistoryUpdateV2 @pTablename, @Note, 'Fail', 0 RETURN (1) END END TRYBEGIN CATCH SET @Note = 'Error: Creating VIEW. ' + @Note + ' Error Number/Message - ' + CAST(Error_Number() AS VARCHAR(5)) + ' ' + Error_Message() EXECUTE ifs_RunHistoryUpdateV2 @pTablename, @Note, 'Fail', 0 RETURN (1)END CATCHDECLARE @RowCount INTSET @RowCount = (SELECT COUNT(*) FROM v_ExportTable)EXECUTE ifs_RunHistoryUpdateV2 @pTablename, @Note, 'Success', @RowCountRETURN (0)END -- CREATE PROCEDURE [dbo].[ifs_ExportTablesCreateView]
Friday, November 7, 2014 5:14 PM | 1 vote
You could also modify the proc to just build the query, and not create the view. Make it into a function that returns a string and then delete the code that creates the view at the end. Theny use bcp with a query, instead of a table or view. bcp will execute a query and my proc builds a query. BUt the reason I created a view from the query is that bcp has a limit on the query size and some of my tables are really, really wide and they went over the bcp query limit. so the view worked regardless of the number of columns in underlying table.
Wednesday, November 12, 2014 10:01 AM | 1 vote
Hi Soner,
I’m writing to follow up with you on this post.Does the workarounds above solve your issue? If the issue is resolved, please help to close this thread.
Thanks,
Lydia Zhang