BCP Bulk Insert issue with double quotes

Neo 21 Reputation points
2021-04-08T16:04:18.99+00:00

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

Here is how my SQL statement looks like:
85850-statement.jpg

These are the results:
85858-results.jpg
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

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,485 questions
{count} votes

Accepted answer
  1. CarrinWu-MSFT 6,851 Reputation points
    2021-04-14T07:50:00.56+00:00

    Hi @Neo ,

    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.


3 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-04-08T21:51:55.387+00:00

    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. CarrinWu-MSFT 6,851 Reputation points
    2021-04-09T03:56:30.387+00:00

    Hi @Neo ,

    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.


  3. Tom Phillips 17,716 Reputation points
    2021-04-13T19:42:36.043+00:00

    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.

    0 comments No comments