Problem with reading CSV file inside a Blob container from SQL Database View (Using OPENROWSET/BULK)

Question

Hello, We are trying to read a CSV file that resides inside a Blob storage. The CSV was previously transformed from Excel with the use of a Data Factory data flow. We have defined a SQL View that reads the CSV file as follows:

CREATE VIEW [dbo].[view] as
SELECT 
   fields
   
FROM
    OPENROWSET(
        BULK 'url/*.csv',
		FORMAT = 'CSV',
		PARSER_VERSION = '2.0',
		FIELDTERMINATOR =';',
        ROWTERMINATOR = '\n',
		HEADER_ROW = TRUE,
		first_row = 1
		
	
        )  
		
		AS ROWS
		
		; 
GO

When the code is executed we receive the following error: Msg 15813, Level 16, State 1, Line 3
Error handling external file: 'Quotes '"' must be inside quoted fields at With trial and error we found that the problem exists in a line where the only difference from the previous ones is a single character Q. As far as i know Q is not considered a special character or anything like that. The settings of the conversion to CSV inside the Data Flow are as follows:

Any help will be greatly appreciated, T.P.

Answer 1

Hello Thodoris Polychronopoulos,

Welcome to the MS Q&A platform.

Can you try use the FIELDQUOTE property in your code?

The FIELDQUOTE property specifies a character that will be used as the quote character in the CSV file.

Specifying the FIELDQUOTE property can be helpful when the default behavior does not correctly interpret the quoted values, or when you need to handle CSV files with different quoting characters.

By explicitly setting the FIELDQUOTE property, you can ensure that the OPENROWSET function correctly handles the quoted values in your CSV file, regardless of the default behavior or any variations in the quoting characters used in different files.

CREATE VIEW [dbo].[view] as
SELECT 
   fields
   
FROM
    OPENROWSET(
        BULK 'url/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIELDTERMINATOR = ';',
        ROWTERMINATOR = '\n',
        HEADER_ROW = TRUE,
        first_row = 1,
        FIELDQUOTE = '"'
        )  
        
        AS ROWS
        
        ; 
GO

If the above solution doesn't help, then please check the CSV file for any inconsistencies in the quoting or delimiters. It's possible that there might be an issue with the file itself, such as unmatched quotes or incorrect delimiters.

You can open the CSV file in a text editor and inspect the contents to identify any issues. If you find any inconsistencies, correct them and try running the OPENROWSET function again with the updated file.