sql server - make rows to become columns in txt with powershell -
i have input txt file different data this:
------------------------------------------------------------------------ current local time: fri jul 01 04:54:27 2016 current gmt time: thu jun 30 20:54:27 2016 machine id: 6090 machine name: wl6090 display s/n: 0253g020tg terrain version: 5.3.843.843 caestasklist dll version: 5.3.843.843 application type: terrain / caes ultra operating system: 1.04 total ram used active files: 339.72 kb ---------------------------------------------------------------------------------------------------------- disk space free disk space: 6758 mb total disk space: 7076 mb ---------------------------------------------------------------------------------------------------------- current local time: 07-01-16, 04:54:27 current service hours: 314.41 and have many such files every day. i'm trying real newbie powershell grab information need these files , this:
$location = "d:\terrain_dia\diag01jul2016_045427.dia" $a = get-content d:\terrain_dia\diag01jul2016_045427.dia #common $option = [system.stringsplitoptions]::removeemptyentries #id $lineid = select-string -path $location -pattern "machine id:" | select-object -expand linenumber $id = ($a)[$lineid-1] $id -replace '(?:\s|\r|\n)','' #name $linename = select-string -path $location -pattern "machine name:" | select-object -expand linenumber $name = ($a)[$linename-1] $name -replace '(?:\s|\r|\n)','' #sn $linesn = select-string -path $location -pattern "display s/n:" | select-object -expand linenumber $sn = ($a)[$linesn-1] $sn -replace '(?:\s|\r|\n)','' #version $lineversion = select-string -path $location -pattern "terrain version:" | select-object -expand linenumber $version = ($a)[$lineversion-1] $version -replace '(?:\s|\r|\n)','' #ram $lineram = select-string -path $location -pattern "total ram" | select-object -expand linenumber $ram = ($a)[$lineram-1] $ram -replace '(?:\s|\r|\n)','' #gpssn $linegpssn = select-string -path $location -pattern "gps receiver sn:" | select-object -expand linenumber $gpssn = ($a)[$linegpssn-1] $gpssn -replace '(?:\s|\r|\n)','' #gpstype $linegpstype = select-string -path $location -pattern "gps receiver type:" | select-object -expand linenumber $gpstype = ($a)[$linegpstype-1] $gpstype -replace '(?:\s|\r|\n)','' #nav $linenav = select-string -path $location -pattern "nav firmware version:" | select-object -expand linenumber $nav = ($a)[$linenav-1] $nav -replace '(?:\s|\r|\n)','' #sig $linesig = select-string -path $location -pattern "sig firmware version:" | select-object -expand linenumber $sig = ($a)[$linesig-1] $sig -replace '(?:\s|\r|\n)','' #rom $linerom = select-string -path $location -pattern "rom firmware version:" | select-object -expand linenumber $rom = ($a)[$linerom-1] $rom -replace '(?:\s|\r|\n)','' i'm getting output:
machineid:6090 machinename:wl6090 displays/n:0253g020tg terrainversion:5.3.843.843 totalramusedbyactivefiles:339.72kb gpsreceiversn:3351j508sp gpsreceivertype:ms992 navfirmwareversion:00506 sigfirmwareversion:00506 romfirmwareversion:00425 freediskspace:6758mb totaldiskspace:7076mb so have fixed delimiter need is:
machineid machinename displays/n 6090 wl6090 0253g020tg etc.
so need format output table within txt file , i'm going put te result ms sql db..
please advise how can format these lines powershell?
any useful.
thank you
this 1 liner process *.dia files in current folder , use regex filter lines containing colon , build condensed name value pairs , display them formatted:
select-string .\*.dia -pattern '^([^:]+):\s+(.*)$' | foreach-object { "{0,-20} {1,-25}: {2}" -f $_.filename,$($_.matches.groups[1].value -replace ' '), ($_.matches.groups[2].value) } sample output:
sample1.dia currentlocaltime : fri jul 01 04:54:27 2016 sample1.dia currentgmttime : thu jun 30 20:54:27 2016 sample1.dia machineid : 6090 sample1.dia machinename : wl6090 sample1.dia displays/n : 0253g020tg sample1.dia terrainversion : 5.3.843.843 sample1.dia caestasklistdllversion : 5.3.843.843 sample1.dia applicationtype : terrain / caes ultra the following powershell script uses same regex , build [pscustomobject] , append properties (at moment error currentlocaltime occurs twice in files , totaldiskspace on same line freediskspace)
otherwise cumulates data finds in file cumulateddia.csv saves , reimports show content via out-gridview leave 2 flaws fixing you/others.
$cumdia = ".\cumulateddia.csv" remove-item $cumdia -erroraction ignore foreach($file in (get-childitem *.dia)){ $diafile = $null $diafile = [pscustomobject]@{'fileorigin' = $file} select-string -path $file -pattern '^([^:]+):\s+(.*)$' | foreach-object { $diafile | add-member -ea silentlycontinue ` -notepropertyname ($_.matches.groups[1].value -replace ' ')` -notepropertyvalue $_.matches.groups[2].value } $diafile | fl $diafile | export-csv -path $cumdia -append -notypeinformation } import-csv $cumdia|out-gridview unwanted properties/columns may removed select-object. format-list output of 1 row table
fileorigin : q:\test\2017\07\25\sample1.dia currentlocaltime : fri jul 01 04:54:27 2016 currentgmttime : thu jun 30 20:54:27 2016 machineid : 6090 machinename : wl6090 displays/n : 0253g020tg terrainversion : 5.3.843.843 caestasklistdllversion : 5.3.843.843 applicationtype : terrain / caes ultra operatingsystem : 1.04 totalramusedbyactivefiles : 339.72 kb freediskspace : 6758 mb total disk space: 7076 mb currentservicehours : 314.41 gpsreceiversn : 3351j508sp gpsreceivertype : ms992 navfirmwareversion : 00506 sigfirmwareversion : 00506 romfirmwareversion : 00425 sample out-gridview pciture
Comments
Post a Comment