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
Time | Type | State | AccountID | User | Rate | ChaosMoney |
---|---|---|---|---|---|---|
22:54:18 | JewelOfHarmonyItemRestoreMix | Success | User7 | Say380 | 0 | 515000 |
23:16:25 | SocketItemCreateSeedMix | Success | User7 | Jack380 | 100 | 1030000 |
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)
2条答案
按热度按时间9udxz4iz1#
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:
SQL
Output
6fe3ivhb2#
Here's another version which uses more old hat string manipulation techniques:
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.