Share via

SQLServerBulkCSVFileRecord java and quotes problem

STELMACH Daniel Ext O-PL/Korpo 0 Reputation points
2025-05-23T14:21:07.0933333+00:00

i can't laod using SQLServerBulkCSVFileRecord (ver 12.10) a file like this:

xxx;yyyy;test"test;zzzz

simple file with 1 simple quote in nvarchar column, give error "com.microsoft.sqlserver.jdbc.SQLServerException: Unable to retrieve data from the source."

when i will change to

xxx;yyyy;test"test"test;zzzz

works perfect

fileRecord.setEscapeColumnDelimitersCSV(false); or true it's still error

any possibility to load column with single quota ?

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
197 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator
    2025-05-23T21:09:51.9833333+00:00

    I don't know about the API in Java, but when I try your sample data with:

    CREATE TABLE #four(a nvarchar(20), b nvarchar(20), cv nvarchar(20), d nvarchar(20))
BULK INSERT #four FROM 'C:\temp\slask.bcp'
WITH (FORMAT = 'CSV', FIELDTERMINATOR=';', DATAFILETYPE = 'widechar')
SELECT * FROM #four
go
DROP TABLE #four

    I get the error message:

    Msg 4879, Level 16, State 1, Line 7 Bulk load failed due to invalid column value in CSV data file C:\temp\slask.bcp in row 1, column 3.

    This is due to that this is not a legal CSV file where double quotes are used to quote data, and cannot appear in the data itself.

    However with BULK INSERT you can specify an alternate quote character with the FIELDQUOTE option:

    BULK INSERT #four FROM 'C:\temp\slask.bcp'
WITH (FORMAT = 'CSV', FIELDTERMINATOR=';', DATAFILETYPE = 'widechar', FIELDQUOTE = '?')

    With this command, the file loads.

    So you need to check the documentation for SQLServerBulkCSVFileRecord to see if it exposes a similar option.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.