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

Question

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

Does this answer your question? [Commas within CSV Data](https://stackoverflow.com/questions/4123875/commas-within-csv-data) Not sure if it will accept both a { and a } as the wrap character though — Dijkgraaf, Aug 24 '22 at 04:21
I solved this by using the "Import Flat File" feature in SSMS which I didn't realise existed — MoonMist, Aug 24 '22 at 05:06

score 1 · Accepted Answer · answered Aug 24 '22 at 05:40

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;

Thank you! Just the first part was all I needed as I didn't need to parse the JSON through SQL, but the FORMAT='CSV' is great. — MoonMist, Aug 25 '22 at 03:42

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

1 Answers1