I think you’ll have to use regular expressions (-match) but you said
which makes regex difficult but maybe not impossible. Using your posted data I was successful at pulling out user, logtime, logcode, and description. I don’t know which field is obj_type and obj_code. If you can point those out to me I might be able to help you capture them as well.
$testfile = New-TemporaryFile
@'
<Commit ts="20210311040006" client=" automation_user"> "144"," automation_user","122.2.2.2","User 'automation _user' successfully logged in from address '122.2.2.2'."
<Commit ts="20210311040006" client=""> "39",”Save Data","Process 'Save Data' was executed."
<Commit ts="20210311125155" client="Steve"> "144","Steve","11.111.111.111","User 'Steve' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311130404" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131122" client="Daniel"> "144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131122" client="Daniel"> "144","Daniel","11.111.111.111","User 'Daniel' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311131747" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135147" client="Shelly"> "144","Shelly","11.111.111.111","User 'Shelly' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135151" client="Tom"> "144","Tom","11.111.111.111","User 'Tom' successfully logged in from address '11.111.111.111'."
<Commit ts="20210311135250" client=""> "21","Version","Dimension 'Version' was updated."
<Commit ts="20210311135250" client=""> "27","Version:Working Forecast","Version:07_05 Vs Working Forecast","1.0","-1.0","Element 'Version:Working Forecast' in consolidation 'Version:07_05 Vs Working Forecast' was changed from weight '1.0' to weight '-1.0'."
<Commit ts="20210311135250" client=""> "21","Version","Dimension 'Version' was updated."
<Commit ts="20210311135250" client=""> "27","Version:Working Forecast","Version:04_08 Vs Working Forecast","1.0","-1.0","Element 'Version:Working Forecast' in consolidation 'Version:04_08 Vs Working Forecast' was changed from weight '1.0' to weight '-1.0'."
'@ | Set-Content -Path $testfile.FullName
foreach ($record in Get-Content -Path $testfile.FullName) {
$record -match '^<Commit ts="(?<LOGTIME>[0-9]{14})" client="(?<USER>[ a-zA-Z_]*)"> "(?<LOGCODE>[0-9]+)",.+"(?<DESCRIPTION>.*)"$' | Out-Null
$sqlCmd = @"
DECLARE @LOGTIME nvarchar(50) = '$($Matches.LOGTIME)'
DECLARE @LOGCODE nvarchar(50) = '$($MAtches.LOGCODE)'
DECLARE @USER nvarchar(50) = '$($Matches.USER)'
DECLARE @DESCRIPTION nvarchar(50) = '$($Matches.DESCRIPTION)'
DECLARE @OBJ_TYPE nvarchar(50) = '$()'
DECLARE @OBJ_NAME nvarchar(50) = '$()'
INSERT INTO TM1_Logs (
LOGTIME,
LOGCODE,
USER,
DESCRIPTION,
OBJ_TYPE,
OBJ_NAME
)
VALUES (
@LOGTIME,
@LOGCODE,
@USER,
@DESCRIPTION,
@OBJ_TYPE,
@OBJ_Name
)
"@
$sqlCmd
} #foreach