[SQL] Split string text in TSQL

I have the below string which needs splitting to allow for insert into a table.

The data is coming from:

http://smartgriddashboard.eirgrid.c...om=30-Aug-2021 00:00&dateto=30-Aug-2021 23:59

I am developing a function to run on an agent job to read the data and then parse it into MSSQL DB. e.g.

DECLARE @fromTime NVARCHAR(MAX)
DECLARE @toTime NVARCHAR(MAX)
DECLARE @URL2 NVARCHAR(MAX)

Set @fromTime = REPLACE(FORMAT(DATEADD(DAY, -1, GETDATE()), 'dd-MMM-yyyy 00:00' ), ' ','%20')
Set @toTime = REPLACE(FORMAT(DATEADD(DAY, -1,GETDATE()), 'dd-MMM-yyyy 23:59' ), ' ','%20')

select @URL2 =
concat('http://smartgriddashboard.eirgrid.com/DashboardService.svc/csv?area=co2Intensity&region=ALL&datefrom=',@fromTime,'&dateto=',@toTime)

DECLARE @URL NVARCHAR(MAX) = @URL2
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @currenttime as datetime;
set @currenttime = GETDATE()

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
@URL,
'False'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

IF((Select @ResponseText) <> '')
BEGIN
DECLARE @json NVARCHAR(MAX) = (Select @ResponseText)

--do something here to parce the return data
;with CTE(raw) as
(

select replace(replace(replace(replace(@json,' ','-'),' ','-'),' ','-'),' ','-')
)
select * from CTE

select @json

--write to SQL table.

END
ELSE
BEGIN
DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
Print @ErroMsg;
END
Exec sp_OADestroy @Object


The delimitator for each line in the table is a double space. The delimitator for each column is a ','.

The first set of data before he fist double space delimitator is the column/table headings and is fixed. e.g. DATE & TIME, CO2 INTENSITY (gCO2/kWh), REGION

Example output from URL above

DATE & TIME, CO2 INTENSITY (gCO2/kWh), REGION 28 August 2021 00:00,470,All Island 28 August 2021 00:15,471,All Island 28 August 2021 00:30,471,All Island 28 August 2021 00:45,472,All Island 28 August 2021 01:00,470,All Island 28 August 2021 01:15,471,All Island 28 August 2021 01:30,468,All Island 28 August 2021 01:45,471,All Island 28 August 2021 02:00,468,All Island 28 August 2021 02:15,460,All Island 28 August 2021 02:30,452,All Island 28 August 2021 02:45,450,All Island 28 August 2021 03:00,464,All Island 28 August 2021 03:15,469,All Island 28 August 2021 03:30,472,All Island 28 August 2021 03:45,476,All Island 28 August 2021 04:00,472,All Island 28 August 2021 04:15,477,All Island 28 August 2021 04:30,488,All Island 28 August 2021 04:45,485,All Island 28 August 2021 05:00,488,All Island 28 August 2021 05:15,489,All Island 28 August 2021 05:30,487,All Island 28 August 2021 05:45,493,All Island 28 August 2021 06:00,492,All Island 28 August 2021 06:15,472,All Island 28 August 2021 06:30,469,All Island 28 August 2021 06:45,468,All Island 28 August 2021 07:00,472,All Island 28 August 2021 07:15,476,All Island 28 August 2021 07:30,482,All Island 28 August 2021 07:45,473,All Island 28 August 2021 08:00,465,All Island 28 August 2021 08:15,463,All Island 28 August 2021 08:30,467,All Island 28 August 2021 08:45,470,All Island 28 August 2021 09:00,460,All Island 28 August 2021 09:15,461,All Island 28 August 2021 09:30,463,All Island 28 August 2021 09:45,472,All Island 28 August 2021 10:00,478,All Island 28 August 2021 10:15,475,All Island 28 August 2021 10:30,475,All Island 28 August 2021 10:45,473,All Island 28 August 2021 11:00,459,All Island 28 August 2021 11:15,462,All Island 28 August 2021 11:30,445,All Island 28 August 2021 11:45,438,All Island 28 August 2021 12:00,433,All Island 28 August 2021 12:15,435,All Island 28 August 2021 12:30,429,All Island 28 August 2021 12:45,424,All Island 28 August 2021 13:00,430,All Island 28 August 2021 13:15,430,All Island 28 August 2021 13:30,431,All Island 28 August 2021 13:45,429,All Island 28 August 2021 14:00,419,All Island 28 August 2021 14:15,418,All Island 28 August 2021 14:30,416,All Island 28 August 2021 14:45,421,All Island 28 August 2021 15:00,411,All Island 28 August 2021 15:15,424,All Island 28 August 2021 15:30,430,All Island 28 August 2021 15:45,429,All Island 28 August 2021 16:00,424,All Island 28 August 2021 16:15,423,All Island 28 August 2021 16:30,416,All Island 28 August 2021 16:45,422,All Island 28 August 2021 17:00,425,All Island 28 August 2021 17:15,439,All Island 28 August 2021 17:30,439,All Island 28 August 2021 17:45,440,All Island 28 August 2021 18:00,440,All Island 28 August 2021 18:15,426,All Island 28 August 2021 18:30,439,All Island 28 August 2021 18:45,429,All Island 28 August 2021 19:00,437,All Island 28 August 2021 19:15,440,All Island 28 August 2021 19:30,436,All Island 28 August 2021 19:45,441,All Island 28 August 2021 20:00,434,All Island 28 August 2021 20:15,446,All Island 28 August 2021 20:30,434,All Island 28 August 2021 20:45,447,All Island 28 August 2021 21:00,443,All Island 28 August 2021 21:15,456,All Island 28 August 2021 21:30,447,All Island 28 August 2021 21:45,448,All Island 28 August 2021 22:00,447,All Island 28 August 2021 22:15,426,All Island 28 August 2021 22:30,418,All Island 28 August 2021 22:45,433,All Island 28 August 2021 23:00,438,All Island 28 August 2021 23:15,440,All Island 28 August 2021 23:30,434,All Island 28 August 2021 23:45,434,All Island


Example of the first 3 rows would be:

28 August 2021 00:00,470,All Island
28 August 2021 00:15,471,All Island
28 August 2021 00:30,471,All Island


I have tried a verity of split strings and bulk imports and have not managed it. e.g.

--;with CTE(raw) as
--(
----select @Data as raw
--select replace(replace(replace(replace(@Data ,' ','-'),' ','-'),'
','-
'),' ','-')
-- )
--select * from CTE

-- write to file first and then import e.g.
--BULK INSERT [AutoData].[dbo].[Eirgrid_Co2Data]
--FROM 'C:\TempData\FileName.csv'
--WITH ( FORMAT='CSV');

-- Tried to read it as JSON and XML!
--select * from OPENJSON(@Data)
select @Data

-- splitting!
-- SELECT value
--FROM STRING_SPLIT(@Data, ',')
----FROM STRING_SPLIT(@Data, ' ') 'split of double space!
--WHERE RTRIM(value) <> '';

Continue reading...