Import CSV into SQL Server where one of the columns is JSON data-CodePudding

Is there a way of importing CSV data into a SQL Server table where one of the columns contains JSON data?

An example CSV row might be the following:

1,{"testId": 2, "testName":"Hello!"},3,4,5

When I try to import this using the following SQL, it picks up the JSON commas as being delimeters

BULK INSERT TableName
FROM '<PathToCSV>'
WITH
(
  FIRSTROW = 1,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '0x0A',
  TABLOCK
)

Is there a way of intelligently filtering them out as literal commas rather than delimeters similar to how Excel handles it?

EDIT: Assume that its not feasible for me to edit the CSV's

CodePudding user response：

You can use the CSV format specifier, along with FIELDQUOTE, to ensure that quoted values are not broken up if they contain commas. This only works on SQL Server 2017 onwards.

BULK INSERT SchoolsTemp
FROM '<PathToCSV>'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '"',
    FIRSTROW = 1,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0A',
    TABLOCK
)

As for the JSON itelf, if need be you can shred that after importing using OPENJSON. For example

INSERT SomeTable (Column1, Column2, ParentId)
SELECT
  j.Column1,
  j.Column2,
  p.Id
FROM ParentTable p
CROSS APPLY OPENJSON(p.YourJson)
  WITH (
    Column1 int,
    Column2 varchar(100)
  ) j;