Importing comma-delimited text file into SQL Server, with a JSON column?-CodePudding

Suppose I have a text file like below,

'Name','Jobs','Dob'
'Fred','{"Job_1":"Accountant","Job_2":"Doctor"}','2009-01-01'

How can I import this into a three column table in SQL Server using OPENROWSET?

CodePudding user response：

Here is an option using OpenRowSet() and a little string manipulation & JSON

Example

Declare @S varchar(max); 
Select @S = BulkColumn FROM  OPENROWSET(BULK 'c:\working\testdata.txt', SINGLE_BLOB) x; 

Select Col1 = stuff(JSON_VALUE(JS,'$[0]'),1,1,'')
      ,Col2 = JSON_VALUE(JS,'$[1]')
      ,Col3 = replace(JSON_VALUE(JS,'$[2]') '##','''##','')
 From  string_split(replace(@S,char(13),''),char(10)) A
 Cross Apply (values ('["' replace(string_escape(Value,'json'),''',''','","') '"]') ) B(JS)
 Where JSON_VALUE(JS,'$[1]')<>'Jobs'

Results