How to pull data from a .txt file to sql

I need to pull data from a .txt file to my SQL Server into a table.

This file is constantly updating from 1 to X amount of times per day giving from 3 to 5 new rows depending on needs and txt file name changes every day example today txt name is: 2023-03-03. I'm going to use a Job every hour that pulls this data but my issue is.

How can I get the data below into my table in sql server?

22:54:18 [JewelOfHarmonyItemRestoreMix][Success][User7][Say380] - (ChaosSuccessRate: 0, ChaosMoney: 515000)
23:16:25 [SocketItemCreateSeedMix][Success][User7][Jack380] - (ChaosSuccessRate: 100, ChaosMoney: 1030000)
13:03:31 [PlusItemLevelMix][Success][User22][Blanca] - (Type: 0, ChaosSuccessRate: 60, ChaosMoney: 2060000)

I want that info like this in my table

This is the TXT document to pull data from

22:54:18 ============================= START MIX ============================= 
22:54:18 [Slot 01](Name: Dragon Knight, Index: 5149, Level: 15, Dur: 152, Serial: 0000D0A7, Option1: 0, Option2: 1, Option3: 7, NewOpt: 014, SetOpt: 000, JOH: 141, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
22:54:18 [JewelOfHarmonyItemRestoreMix][Success][User7][Say380] - (ChaosSuccessRate: 0, ChaosMoney: 515000)
23:16:25 ============================= START MIX ============================= 
23:16:25 [Slot 01](Name: Sacred, Index: 4667, Level: 04, Dur: 76, Serial: 000177D6, Option1: 0, Option2: 0, Option3: 1, NewOpt: 000, SetOpt: 005, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
23:16:25 [Slot 03](Name: Daybreak, Index: 0024, Level: 04, Dur: 109, Serial: 000177D2, Option1: 0, Option2: 0, Option3: 1, NewOpt: 002, SetOpt: 000, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
23:16:25 [Slot 05](Name: Jewel of Chaos, Index: 6159, Level: 00, Dur: 1, Serial: 000177D3, Option1: 0, Option2: 0, Option3: 0, NewOpt: 000, SetOpt: 000, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
23:16:25 [SocketItemCreateSeedMix][Success][User7][Jack380] - (ChaosSuccessRate: 100, ChaosMoney: 1030000)
13:03:31 ============================= START MIX ============================= 
13:03:31 [Slot 01](Name: Jewel of Soul, Index: 7182, Level: 00, Dur: 1, Serial: 00017912, Option1: 0, Option2: 0, Option3: 0, NewOpt: 000, SetOpt: 000, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
13:03:31 [Slot 02](Name: Jewel of Bless, Index: 7181, Level: 00, Dur: 1, Serial: 0001457A, Option1: 0, Option2: 0, Option3: 0, NewOpt: 000, SetOpt: 000, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
13:03:31 [Slot 03](Name: Jewel of Chaos, Index: 6159, Level: 00, Dur: 1, Serial: 00014253, Option1: 0, Option2: 0, Option3: 0, NewOpt: 000, SetOpt: 000, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
13:03:31 [PlusItemLevelMix][Success][User22][Blanca] - (Type: 0, ChaosSuccessRate: 60, ChaosMoney: 2060000)

Please try the following solution.

It will work starting from SQL Server 2017 onwards due to dependency on the TRIM() function.

It is using SQL Server's XML and XQuery to tokenize each line.

I saved your sample data in the 'e:\Temp\NachoSanchez.txt' file.

And created a format file 'e:\Temp\NachoSanchez-format.xml' as follows:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <RECORD>
      <FIELD ID="1" xsi:type="CharTerm" TERMINATOR='\r\n' MAX_LENGTH="2048" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
   </RECORD>
   <ROW>
      <COLUMN SOURCE="1" NAME="line" xsi:type="SQLVARYCHAR"/>
   </ROW>
</BCPFORMAT>

SQL

DECLARE @separator CHAR(1) = SPACE(1);

;WITH rs (line) AS
(
   SELECT line
   FROM  OPENROWSET(BULK 'e:\Temp\NachoSanchez.txt'
      , FORMATFILE = 'e:\Temp\NachoSanchez-format.xml'  
      , ERRORFILE = 'e:\Temp\NachoSanchez-log.err'
      , FIRSTROW = 1 -- real data starts on the 1st row
      , MAXERRORS = 100
   ) AS tbl
)
SELECT x.value('(/root/r[1]/text())[1]', 'CHAR(8)') AS [time]
	, TRIM('[]' FROM x.value('(/root/r[2]/text())[1]', 'VARCHAR(50)')) AS [Type]
	, TRIM('[]' FROM x.value('(/root/r[3]/text())[1]', 'VARCHAR(50)')) AS [State]
	, TRIM('[]' FROM x.value('(/root/r[4]/text())[1]', 'VARCHAR(50)')) AS AccountID
	, TRIM('[]' FROM x.value('(/root/r[5]/text())[1]', 'VARCHAR(50)')) AS [User]
	, TRIM(',' FROM x.value('(/root/r[last() - 2]/text())[1]', 'VARCHAR(50)')) AS [Rate]
	, TRIM(')' FROM x.value('(/root/r[last()]/text())[1]', 'VARCHAR(50)')) AS [ChaosMoney]
FROM rs
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
    REPLACE(REPLACE(line,'][','] ['), @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML)) AS t(x)
WHERE line LIKE '%User%';

Output

Here's another version which uses more old hat string manipulation techniques:

declare @file nvarchar(max)

-- Load from file
  
SELECT @file = BulkColumn
FROM OPENROWSET(BULK 'c:\path-to-your-file.txt', SINGLE_CLOB) AS DATA;

-- For testing only
set @file = N'22:54:18 ============================= START MIX ============================= 
22:54:18 [Slot 01](Name: Dragon Knight, Index: 5149, Level: 15, Dur: 152, Serial: 0000D0A7, Option1: 0, Option2: 1, Option3: 7, NewOpt: 014, SetOpt: 000, JOH: 141, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
22:54:18 [JewelOfHarmonyItemRestoreMix][Success][User7][Say380] - (ChaosSuccessRate: 0, ChaosMoney: 515000)
23:16:25 ============================= START MIX ============================= 
23:16:25 [Slot 01](Name: Sacred, Index: 4667, Level: 04, Dur: 76, Serial: 000177D6, Option1: 0, Option2: 0, Option3: 1, NewOpt: 000, SetOpt: 005, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
23:16:25 [Slot 03](Name: Daybreak, Index: 0024, Level: 04, Dur: 109, Serial: 000177D2, Option1: 0, Option2: 0, Option3: 1, NewOpt: 002, SetOpt: 000, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
23:16:25 [Slot 05](Name: Jewel of Chaos, Index: 6159, Level: 00, Dur: 1, Serial: 000177D3, Option1: 0, Option2: 0, Option3: 0, NewOpt: 000, SetOpt: 000, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
23:16:25 [SocketItemCreateSeedMix][Success][User7][Jack380] - (ChaosSuccessRate: 100, ChaosMoney: 1030000)
13:03:31 ============================= START MIX ============================= 
13:03:31 [Slot 01](Name: Jewel of Soul, Index: 7182, Level: 00, Dur: 1, Serial: 00017912, Option1: 0, Option2: 0, Option3: 0, NewOpt: 000, SetOpt: 000, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
13:03:31 [Slot 02](Name: Jewel of Bless, Index: 7181, Level: 00, Dur: 1, Serial: 0001457A, Option1: 0, Option2: 0, Option3: 0, NewOpt: 000, SetOpt: 000, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
13:03:31 [Slot 03](Name: Jewel of Chaos, Index: 6159, Level: 00, Dur: 1, Serial: 00014253, Option1: 0, Option2: 0, Option3: 0, NewOpt: 000, SetOpt: 000, JOH: 000, ItemOptEx: 000, SocketOpt: 255, 255, 255, 255, 255)
13:03:31 [PlusItemLevelMix][Success][User22][Blanca] - (Type: 0, ChaosSuccessRate: 60, ChaosMoney: 2060000)
'
    
select SUBSTRING(x.Value, 1, dateSep -1) AS dt
, ROW_NUMBER() OVER(ORDER BY @@SPID) AS id
, z.*
FROM STRING_SPLIT(@file, CHAR(10)) x
CROSS APPLY (
      select charindex(' ', x.value) AS dateSep
      , charindex(' - ', x.value) AS chaosSep
      , charindex('(', x.value) AS chaosStart
      , charindex(')', x.value) AS chaosEnd
  ) y
CROSS APPLY (
     SELECT MAX(case when [key] = 0 then value end) as item
     , MAX(case when [key] = 1 then value end) as status
     , MAX(case when [key] = 2 then value end) as userid
     , MAX(case when [key] = 3 then value end) as userName
     , MAX(case when [key] = 4 then JSON_VALUE(value, '$.ChaosSuccessRate') end) as chaosRate
     , MAX(case when [key] = 4 then JSON_VALUE(value, '$.ChaosMoney') end) as chaosMoney
     FROM OPENJSON('["' + replace(replace(substring(x.value, dateSep + 2, chaosSep - dateSep-3), ']','",'), '[', '"') + '"'
   + ',{"' + replace(replace(substring(x.value, chaosStart + 1, chaosEnd - chaosStart - 1), ':', '":'), ', ', ', "') + '}]') AS details
  ) z
WHERE x.value LIKE '%ChaosMoney%'

Basicly, we load whole file using BULK SINGLE_CLOB and split it by newlines.

Then we cut out various parts of the message and then assemble them together in a more simpler to work with json array version. Finally, we create column for each array value to get the results.