question

Neo-7914 avatar image
0 Votes"
Neo-7914 asked CarrinWu-MSFT commented

BCP Bulk Insert issue with double quotes

I am using sql-server (2014) to import data from a .csv file to a sql-server table. I am using bcp and a format file . Everything works fine, except that all my data including the columns have double quotes. I do not need the double quotes. Here is how the data in the .csv file looks:

![85750-csv.jpg][1]


Here is how my SQL statement looks like:
![85850-statement.jpg][2]


These are the results:
![85858-results.jpg][3]
So besides getting the headers in the first row, I also see everything is in double quotes. I want to eliminate the double quotes while importing and exclude the headers as part of my rows.
Here is the format file I am using:

![85873-format.jpg][4]

Please provide any guidance. Even a different approach. Ultimately, I want to be able to execute a store procedure so using a wizard is not an option as it involves manual intervention.
[1]: /answers/storage/attachments/85750-csv.jpg
[2]: /answers/storage/attachments/85850-statement.jpg
[3]: /answers/storage/attachments/85858-results.jpg
[4]: /answers/storage/attachments/85873-format.jpg

sql-server-general
csv.jpg (27.7 KiB)
statement.jpg (25.5 KiB)
results.jpg (20.3 KiB)
format.jpg (55.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Neo-7914, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If it is not work, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·

Hi CarrinWu-MSFT. I tried the code and it 'almost' got me there. I tried adjusting the code to my needs, but when I ran it, the results show double quotes at the end of every character in the last column. I see it also included the headers (I am not sure where to put -F2 within the code). Also, I am not sure if this has anything to do with the version of bcp (I ran bcp /v and it says version: 12.0.2569.0).
Here is my new results:
results2.JPG


Here is the adjusted sql statement:
SQLCODE.jpg


and this is how I executed the store procedure along with single parameter:
EXECUTE+SP.JPG

I am reviewing ErlandSommarskog answer and will get back today.



0 Votes 0 ·

I am not sure where to put -F2 within the code

  • 2 is for BCP. For BULK INSERT, you need FIRSTROW=2.

But in any case, you will need a format file as I said in my initial reply.

By the way, I see that you have SET QUOTE_IDENTIFIER OFF in your script. Stay away from that setting! This is a legacy setting, and there are features in SQL Server which does not work when this setting is in the OFF position. You will sooner or later run into one of these situations, so save yourselves from a confusing error message.

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT commented

Hi @Neo-7914,

Please try below script, it will work:

 USE [TestDemo]
 GO
    
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER OFF
 GO
 CREATE OR ALTER PROCEDURE [dbo].[ps_test_Import]
 @PathFileName varchar(100),--='c:\TxtFile1.txt',
 @FileType tinyint
 AS
    
 --Step 1: Build Valid BULK INSERT Statement
 DECLARE @SQL varchar(2000)
 IF @FileType = 1
  BEGIN
   -- Valid format: "John","Smith","john@smith.com"
   SET @SQL = 'BULK INSERT TmpStList FROM '''+@PathFileName+''' WITH (FIELDTERMINATOR = '','',FIRSTROW=2) '
  END
 ELSE
  BEGIN
   -- Valid format: John,Smith,john@smith.com
   SET @SQL = 'BULK INSERT TmpStList FROM '''+@PathFileName+''' WITH (FIELDTERMINATOR = '','',FIRSTROW=2) '
  END
    
 -- print @SQL
 --Step 2: Execute BULK INSERT statement
 EXEC (@SQL)
    
  --select * from TmpStList
  --declare @FileType tinyint=1
 --Step 3: INSERT data into final table
 INSERT TEST ([DATE],[CD],[MY-ACC],[YOUR-ACC],[BAL])
 SELECT  CASE WHEN @FileType = 1 THEN SUBSTRING([DATE],2,DATALENGTH([DATE])-2)
              ELSE [DATE]
         END,
         SUBSTRING([CD],2,DATALENGTH([CD])-2),
         SUBSTRING([MY-ACC],2,DATALENGTH([MY-ACC])-2),
         SUBSTRING([YOUR-ACC],2,DATALENGTH([YOUR-ACC])-2),
         CASE WHEN @FileType = 1 THEN SUBSTRING([BAL],2,DATALENGTH([BAL])-2)
              ELSE [BAL]
         END
            
 FROM TmpStList
    
 -- exec [dbo].[ps_test_Import] 'C:\1.Temp\Test.txt',1

Output:
87597-output.png

In addition, there is no special switch in BULK INSERT or BCP to strip double-quotes. If you know the number of double-quotes that needs to be removed then you can use a format file. Of course in this case, each line/value should have the same format. See BOL for more details on using format files especially the topic that talks about inserting data files that has more columns than the table.





Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



output.png (4.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Carrin! it worked. I just added back the last line to empty the temp table (TRUNCATE TABLE TmpStList). For the benefit of this question, I will post a comment on ErlandSommarskog's suggestion after I try again today (the link provided has been informative, but I was unsuccessful yesterday when I attempted the format file. I will attempt again today and post results again for the benefit of those interested)

0 Votes 0 ·

No problem, have a nice day!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

To get rid of the headers, use the option -F 2.

To get rid of the quotes, you need to specify them as part of the delimiter. They way to deal with the opening delimiter is to say that the file has 7 fields, where the first has a line double quote as the delimiter, and for this you set the column number to 0, so it is not imported.

I have an article on my web site, http://www.sommarskog.se/bulkload.html, where I discuss the bulk load tools in more details, and I have examples on how to deal with these type of files.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog , I tried with the below format file and it did not work. When I executed the statement it said '0 rows affected'. Could you provide the correct format file?
This is the new format file I used:
87856-newformatfile.jpg


and this is how i executed the statement:
87877-bcp-try.jpg


0 Votes 0 ·
newformatfile.jpg (48.9 KiB)
bcp-try.jpg (19.5 KiB)

I don't have access to your data file, so I can't test it. But looking at your screenshot above, it appears that there is one character between the final double quote and the CR-LF. This character needs to be included in the delimiter for the sixth field.

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered ErlandSommarskog commented

Hi @Neo-7914,

Welcome to Microsoft Q&A!

I want to eliminate the double quotes while importing and exclude the headers as part of my rows.

  1. If you would like to eliminate double queotes, please refer to this blog, it may help you. There has a store procedure that use BULK INSERT statement, this statement allows you to interact with bcp. See below:
    86074-bcp.png

  2. -F2: Start at row 2 column, skip field headers. Please refer to System Requirements to get more information about the arguments.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




bcp.png (52.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BULK INSERT statement, this statement allows you to interact with

bcp.

Not really. BULK INSERT and bcp perform the same task but from different origins, and they are different implementations. (As one can tell by the small subtle differences.)


0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

Although you can work around the issues with quotes strings in BCP/Bulk Insert, I would highly recommend you use SSIS to import this type of file.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.