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?
-
1What you have tried?sa-es-ir– sa-es-ir2023-01-27 04:42:45 +00:00Commented Jan 27, 2023 at 4:42
-
@sa-es-ir I tried BULK INSERT #TEMP FROM 'PATH' WITH (FIRSTROW = 2, FIELDTERMINATOR = ''',''', ROWTERIMNATOR = 0x0A') which is a bit hacky because it means the first and last fields have a leading and trailing '. I was hoping there is a more elegant solutionKenshin– Kenshin2023-01-27 04:44:39 +00:00Commented Jan 27, 2023 at 4:44
-
Please use SSISBogdan Sahlean– Bogdan Sahlean2023-01-27 09:52:30 +00:00Commented Jan 27, 2023 at 9:52
-
bcp, sqlcmd, ssms, bulkinsert, etc.Bogdan Sahlean– Bogdan Sahlean2023-01-27 10:00:04 +00:00Commented Jan 27, 2023 at 10:00
-
First: SQL Server Instance should have direct access to text/csv file. This means that files should be on server machine.Bogdan Sahlean– Bogdan Sahlean2023-01-27 11:29:56 +00:00Commented Jan 27, 2023 at 11:29
|
Show 1 more comment
1 Answer
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
