Powershell - Reconstruct csv import into new data structure

Hello EE,

I have a data file in tab delimited format , I wish to create a new custom object of specific targeted data fields , then export each object as a individual text document . I scripted a simple import of csv with applicable headers  as per below  , with an rloc grouping . ' rloc' is the record locator . powershell group "values" output below.

How would I go about selecting specific 'segment types' example , NAM, FLT etc to reconstruct this into a specific data format as show immediately below this line . Each one will be outputted into an individual txt file for further processing. I have also attached sample data file .

QK YQMTTXH
.HDQRM1Z 131453
HDQ1Z *RLOC*/EQ43/67522766
*NAM*
*FLT*
*FLT*


Open in new window


$datafile = gci 'C:\temp\EE_sample.txt'
$import = import-csv -Header "Rloc","Pnrline","SegmentType","PNRData" -Delimiter "`t" $datafile 
$groupedrloc = $import | group-object rloc -AsHashTable

Open in new window


Rloc                               Pnrline                            SegmentType                        PNRData                           
----                               -------                            -----------                        -------                           
Rloc                               Pnrline                            SegmentType                        PNRData                           
AAAWMG                             1                                  NAM                                1.1TEST/MAHMOUD                   
AAAWMG                             2                                  FLT                                1  F9 0310 G  SU 03JUN18YEG YYZ...
AAAWMG                             3                                  FLT                                2  F9 0311 P  SU 17JUN18YYZ YEG...
AAAWMG                             4                                  CTC                                01 CTCA   MIGRATION PNR[F9]       
AAAWMG                             5                                  CTC                                02 CTCB   58799999[F9]            
AAAWMG                             6                                  CTC                                03 CTCM   58799999[F9]            
AAAWMG                             7                                  CTC                                04 CTCE   TEST@YAHOO.COM[F9]      
AAAWMG                             8                                  FQT                                -MANUAL- *CAD595.19 Y/315.36+.0...
AAAWMG                             9                                  FQB                                MANUAL-                           
AAAWMG                             10                                 FQS                                01 FQ01   *CAD315.36/315.36+.00...
AAAWMG                             11                                 FQS                                02 FQ02   *CAD279.83/279.83+.00...
AAAWMG                             12                                 FST                                FQC 1 CAD     595.19 /315.36+0....
AAAWMG                             13                                 FST                                Total CAD     595.19              
AAAWMG                             15                                 RMK                                01 RMKS   REF NO:249999           
AAAWMG                             16                                 RMK                                02 RMKS   AGENT::                 
AAAWMG                             17                                 RMK                                03 RMKS   FINAL PRICE:            
AAAWMG                             18                                 RMK                                04 RMKS   FARECLASS:G             
AAAWMG                             19                                 RMK                                05 RMKS   NOTES:NOIN              
AAAWMG                             20                                 RMK                                06 RMKS   COMMISSIONID:PROVINCE   
AAAWMG                             21                                 RMK                                07 RMKS   TKTRES:OPEN             
AAAWMG                             22                                 RMK                                08 RMKS   AGENCY:TEST             
AAAWMG                             23                                 RLE                                YLWF9 AAAWMG/MIGRATION/YLW/F9//...
AAAWMG                             2000                               FOP                                III CAD 595.19                    
AAAWMG                             3289                               TKT                                E 418 23999999/1=O YEG-YYZ F903...
AAAWMG                             3289                               TKT                                E 418 23999999/2=O YYZ-YEG F903...
AAAWMU                             1                                  NAM                                1.1TEST/TANYA                     
AAAWMU                             2                                  NAM                                2.1TEST/PAUL                      
AAAWMU                             3                                  NAM                                3.1TEST/SPENCER                   
AAAWMU                             4                                  NAM                                4.1TEST/FRANCESCAMS.CH02          
AAAWMU                             5                                  NAM                                5.1TEST/EMMERSONMS.IN07           
AAAWMU                             6                                  FLT                                1  F9 0111 X  FR 20APR18YEG YXX...
AAAWMU                             7                                  FLT                                2  F9 0110 G  MO 23APR18YXX YEG...
AAAWMU                             8                                  MPS                                01 MP 1 - 1EX01 CAD     25.00 1...
AAAWMU                             9                                  MPS                                02 MP 1 - 1CB01 CAD     30.00 C...
AAAWMU                             10                                 MPS                                03 MP 2 - 1EX01 CAD     25.00 1...
AAAWMU                             11                                 MPS                                04 MP 2 - 1CB01 CAD     30.00 C...
AAAWMU                             12                                 CTC                                01 CTCA   MIGRATION PNR[F9]       
AAAWMU                             13                                 CTC                                02 CTCB   555 555 8019[F9]        
AAAWMU                             14                                 CTC                                03 CTCM   555 555 8019[F9]        
AAAWMU                             15                                 CTC                                04 CTCE   TEST@GMAIL.COM[F9]      
AAAWMU                             16                                 AFX                                01 AFXS-4 CHD 02 YRS[F9-HK]~N     
AAAWMU                             17                                 AFX                                02 AFXS-5 INF 07 MTHS*P1[F9]~N    
AAAWMU                             18                                 AFX                                03 AFX1-5 INF 07 MTHS*P1[F9-HK]~N 
AAAWMU                             19                                 AFX                                04 AFX2-5 INF 07 MTHS*P1[F9-HK]~N 
AAAWMU                             20                                 FQT                                -Auto *CAD188.00 ?/28.59+40.41/...
AAAWMU                             21                                 FQB                                #XXCLNRT,#GGCLNRT                 
AAAWMU                             22                                 FQI                                [SITI 1877,166]                   
AAAWMU                             23                                 FQS                                01 FQ01   *CAD69/28.59+40.41#XX...
AAAWMU                             24                                 FQS                                02 FQ02   *CAD119/106.21+12.79#...
AAAWMU                             25                                 FST                                FQC 1 CAD     188.00 /28.59+40....
AAAWMU                             26                                 FST                                FQC 1 CAD      26.25 /25.00+1.2...
AAAWMU                             27                                 FST                                FQC 1 CAD      31.50 /30.00+1.5...
AAAWMU                             28                                 FST                                FQC 1 CAD      26.25 /25.00+1.2...
AAAWMU                             29                                 FST                                FQC 1 CAD      31.50 /30.00+1.5...
AAAWMU                             30                                 FST                                FQC 2 CAD     188.00 /28.59+40....
AAAWMU                             31                                 FST                                FQC 3 CAD     188.00 /28.59+40....
AAAWMU                             32                                 FST                                FQC 4 CAD     188.00 /28.59+40....
AAAWMU                             33                                 FST                                FQC 5 CAD       0.00 /0.00+0.00...
AAAWMU                             34                                 FST                                Total CAD     867.50              
AAAWMU                             53                                 RMK                                01 RMKS   REF NO:299999           
AAAWMU                             54                                 RMK                                02 RMKS   AGENT::                 
AAAWMU                             55                                 RMK                                03 RMKS   FINAL PRICE:            
AAAWMU                             56                                 RMK                                04 RMKS   FARECLASS:O             
AAAWMU                             57                                 RMK                                05 RMKS   NOTES:NOIN              
AAAWMU                             58                                 RMK                                06 RMKS   COMMISSIONID:MANITOBA   
AAAWMU                             59                                 RMK                                07 RMKS   TKTRES:OPEN             
AAAWMU                             60                                 RMK                                08 RMKS   AGENCY:NEWLEAF  WEB A...
AAAWMU                             61                                 RMK                                09 RMKS   PAX MISSED 1ST LEG YL...
AAAWMU                             62                                 RMK                                10 RMKS   PAX COULD NOT REACH A...
AAAWMU                             63                                 RMK                                11 RMKS   PAX UPSET OVER THE LO...
AAAWMU                             64                                 RMK                                12 RMKS   PAX WILL CALL BACK TO...
AAAWMU                             65                                 RMK                                13 RMKS   CHANGE FEE PLUS FARE ...
AAAWMU                             66                                 RLE                                YLWF9 AAAWMU/MIGRATION/YLW/F9//...
AAAWMU                             2000                               FOP                                III CAD 57.75                     
AAAWMU                             2000                               FOP                                CCC CAD 398.00                    
AAAWMU                             2000                               FOP                                III CAD 354.00                    
AAAWMU                             3194                               TKT                                M 418 7006666666/1=F YXX-YEG F9...
AAAWMU                             3195                               TKT                                M 418 7006666666/1=F YXX-YEG F9...
AAAWMU                             3305                               TKT                                E 418 7006666666/2=V YXX-YEG F9...
AAAWMU                             3305                               TKT                                E 418 7006666666/1=V YEG-YXX F9...
AAAWMU                             3306                               TKT                                E 418 7006666666/1=V YEG-YXX F9...
AAAWMU                             3306                               TKT                                E 418 7006666666/2=V YXX-YEG F9...
AAAWMU                             3307                               TKT                                E 418 7006666666/1=V YEG-YXX F9...
AAAWMU                             3307                               TKT                                E 418 7006666666/2=V YXX-YEG F9...
AAAWMU                             3594                               TKT                                M 418 7006666666/1=F YEG-YXX F9...
AAAWMU                             3595                               TKT                                M 418 7006666666/1=F YEG-YXX F9...
AAAWMU                             3640                               TKT                                E 418 7006666666/2=O YXX-YEG F9...
AAAWMU                             3640                               TKT                                E 418 7006666666/1=O YEG-YXX F9...
AAAWMU                             3641                               TKT                                E 418 7006666666/1=O YEG-YXX F9...
AAAWMU                             3641                               TKT                                E 418 7006666666/2=O YXX-YEG F9...
AAAWMU                             3642                               TKT                                E 418 7006666666/1=O YEG-YXX F9...
AAAWMU                             3642                               TKT                                E 418 7006666666/2=O YXX-YEG F9...
AAAWMU                             3643                               TKT                                E 418 7006666666/1=O YEG-YXX F9...
AAAWMU                             3643                               TKT                                E 418 7006666666/2=O YXX-YEG F9...
AAAWMU                             3644                               TKT                                E 418 7006666666/2=O YXX-YEG F9...
AAAWMU                             3644                               TKT                                E 418 7006666666/1=O YEG-YXX F9...
AAAWNW                             1                                  NAM                                1.1TEST/KEVIN                     
AAAWNW                             2                                  NAM                                2.1TEST/DEIRDRE                   
AAAWNW                             3                                  FLT                                1  F9 0210 T  FR 18MAY18YEG YHM...
AAAWNW                             4                                  FLT                                2  F9 0211 P  SU 27MAY18YHM YEG...
AAAWNW                             5                                  MPS                                01 MP 1 - 1EX01 CAD     25.00 1...
AAAWNW                             6                                  MPS                                02 MP 2 - 1EX01 CAD     25.00 1...
AAAWNW                             7                                  CTC                                01 CTCA   MIGRATION PNR[F9]       
AAAWNW                             8                                  CTC                                02 CTCB   78999991[F9]            
AAAWNW                             9                                  CTC                                03 CTCM   78999991[F9]            
AAAWNW                             10                                 CTC                                04 CTCE   TESTY1@HOTMAIL.COM[F9]  
AAAWNW                             11                                 FQT                                -MANUAL- *CAD478.83 T/199.00+.0...
AAAWNW                             12                                 FQB                                MANUAL-                           
AAAWNW                             13                                 FQS                                01 FQ01   *CAD199/199.00+.00 [ ]  
AAAWNW                             14                                 FQS                                02 FQ02   *CAD279.83/279.83+.00...
AAAWNW                             15                                 FST                                FQC 1 CAD     478.83 /199.00+0....
AAAWNW                             16                                 FST                                FQC 2 CAD     478.83 /199.00+0....
AAAWNW                             17                                 FST                                FQC 1 CAD      26.25 /25.00+1.2...
AAAWNW                             18                                 FST                                FQC 1 CAD      28.25 /25.00+3.2...
AAAWNW                             19                                 FST                                Total CAD    1012.16              
AAAWNW                             28                                 RMK                                01 RMKS   REF NO:39999            
AAAWNW                             29                                 RMK                                02 RMKS   AGENT::                 
AAAWNW                             30                                 RMK                                03 RMKS   FINAL PRICE:            
AAAWNW                             31                                 RMK                                04 RMKS   FARECLASS:T             
AAAWNW                             32                                 RMK                                05 RMKS   NOTES:NOIN              
AAAWNW                             33                                 RMK                                06 RMKS   COMMISSIONID:ONTARIO    
AAAWNW                             34                                 RMK                                07 RMKS   TKTRES:OPEN             
AAAWNW                             35                                 RMK                                08 RMKS   AGENCY:API - ACME       
AAAWNW                             36                                 RLE                                YLWF9 AAAWNW/MIGRATION/YLW/F9//...
AAAWNW                             2000                               FOP                                III CAD 26.25                     
AAAWNW                             2000                               FOP                                III CAD 957.66                    
AAAWNW                             2000                               FOP                                III CAD 28.25                     
AAAWNW                             3364                               TKT                                E 418 239999996/1=O YEG-YHM F90...
AAAWNW                             3364                               TKT                                E 418 239999996/2=O YHM-YEG F90...
AAAWNW                             3365                               TKT                                E 418 239999996/2=O YHM-YEG F90...
AAAWNW                             3365                               TKT                                E 418 239999996/1=O YEG-YHM F90...
AAAWNW                             3623                               TKT                                M 418 709999996/1=F YEG-YHM F90...
AAAWNW                             3644                               TKT                                M 418 709999996/1=F YHM-YEG F90211

Open in new window

EE_sample.txt
davesnbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jose Gabriel Ortega CastroEE Solution Guide - CEO Faru Bonon ITCommented:
The format you want is unclear. If I have the 1st line,
AAAWMG                             1                                  NAM                                1.1TEST/MAHMOUD          

how would that in the new format look like?
0
davesnbAuthor Commented:
Hello and thanks for your reply.

I wish the data format outputted to individual txt file to end up like the following for all grouped data, replacing the segment types (between the **) . So in example AAAWMG it would be

QK YQMTTXH
.HDQRM1Z 131453
HDQ1Z AAAWMG/EQ43/67522766
1TEST/MAHMOUD
F9 0310 G  SU 03JUN18YEG YYZ HK1   1815 2353   CAB Y
F9 0311 P  SU 17JUN18YYZ YEG HK1   0730 0934   CAB Y


Open in new window


As you can see above, I am using the - rloc, NAM , FLT , FLT lines and replacing them into the text template. And then I will need to save this as an individual txt file  AAAWMG.txt , then I go onto the next rloc number..
0
oBdACommented:
Note that since your csv already has a header line, you don't need to specify the column names; that's where line 3 was coming from. The Header argument is for csvs without header line.
Anyway, that should do the trick:
$File = 'C:\Temp\EE_sample.txt'
$OutFolder = 'C:\Temp'
$SegmentTypes = 'NAM', 'FLT'

$SOH = [char]1
$STX = [char]2
$ETX = [char]3

$Template = @"
$($SOH)QK YQMTTXH
.HDQRM1Z 131453
$($STX)HDQ1Z *RLOC*/EQ43/67522766
*NAM*
*FLT*
$($ETX)
"@

Import-Csv -Path $File -Delimiter "`t" | Group-Object rloc | ForEach-Object {
	$RLoc = $_
	"Processing rloc '$($RLoc.Name)' ..." | Write-Host
	$Content = $Template -replace [regex]::Escape("*RLOC*"), $RLoc.Name
	$SegmentTypes | ForEach-Object {
		$SegmentType = $_
		$PNRData = $RLoc.Group | Where-Object {$_.SegmentType -eq $SegmentType} | Select-Object -ExpandProperty PNRData
		$Content = $Content -replace [regex]::Escape("*$($SegmentType)*"), ($PNRData -join "`r`n")
	}
	$OutFile = "$($OutFolder)\$($RLoc.Name).txt"
	$Content | Set-Content -Path $OutFile
	"Record written to '$($OutFile)'" | Write-Host
}

Open in new window

0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

davesnbAuthor Commented:
Wow this is great , I see how you are constructing this now . Just need to add a few more things , here is the updated template .


$Template = @"
$($SOH)QK YQMTTXH
.HDQRM1Z 131453
$($STX)HDQ1Z *RLOC*/EQ43/67522766
*NAM*
*FLT*
OSI *CTC*
OSI *AFX*
OSI *TKT*
OSI *RMK*
$($ETX)
"@

Open in new window

0
oBdACommented:
Just in case: when you update the template to include more segment types, you need to add them to the $SegmentTypes variable as well:
$SegmentTypes = 'NAM', 'FLT', 'CTC', 'AFX', 'TKT', 'RMK'

Open in new window

0
davesnbAuthor Commented:
OK one last rework to the data that I need a working example on . I need to further regex and replace the data . For example , on line FLT lines , I need to rework the data before sending out to file .

From :
F9 0310 G  SU 03JUN18YEG YYZ HK1   1815 2353   CAB Y
F9 0311 P  SU 17JUN18YYZ YEG HK1   0730 0934   CAB Y

Open in new window


TO :

F9310G03JUN18 YEGYYZ HK1   1815 2353   CAB Y
F9310P17JUN18 YYZYEG HK1   0730 0934   CAB Y

Open in new window


So in summary I need to merge airline code ( F9) while droping the leading 0 from flight number . Then move the booking code ( G & P) bfore the date , space before airportcodes  space action code flight times
0
oBdACommented:
You dropped the index (or whatever the first three chars are) as well; is that intentional?
1  F9 0310 G  SU 03JUN18YEG YYZ HK1   1815 2353   CAB Y
~~~

Open in new window

Anyway, reworking the data happens in a function that can be easily adjusted:
$File = 'C:\Temp\EE_sample.txt'
$OutFolder = 'C:\Temp'
$SegmentTypes = 'NAM', 'FLT', 'CTC', 'AFX', 'TKT', 'RMK'

$SOH = [char]1
$STX = [char]2
$ETX = [char]3

$Template = @"
$($SOH)QK YQMTTXH
.HDQRM1Z 131453
$($STX)HDQ1Z *RLOC*/EQ43/67522766
*NAM*
*FLT*
OSI *CTC*
OSI *AFX*
OSI *TKT*
OSI *RMK*
$($ETX)
"@

Function Convert-PNRData {
[CmdletBinding()]
Param(
	[Parameter(ValueFromPipeline=$true)]
	[string]$PNRData,
	[string]$SegmentType
)
	Process {
		Switch ($SegmentType) {
			'FLT' {
				$PNRData -match '(?<Index>..).(?<Airline>..).(?<Flight>....).(?<Booking>..).(?<Day>..).(?<Date>.......)(?<Airport>...).(?<Tail>.*)' | Out-Null
				$Matches.Airline + $Matches.Flight.TrimStart('0') + $Matches.Booking.Trim() + $Matches.Date + ' ' + $Matches.Airport + $Matches.Tail
			}
			default {
				$PNRData
			}
		}
	}
}

Import-Csv -Path $File -Delimiter "`t" | Group-Object rloc | ForEach-Object {
	$RLoc = $_
	"Processing rloc '$($RLoc.Name)' ..." | Write-Host
	$Content = $Template -replace [regex]::Escape("*RLOC*"), $RLoc.Name
	$SegmentTypes | ForEach-Object {
		$SegmentType = $_
		$PNRData = $RLoc.Group | Where-Object {$_.SegmentType -eq $SegmentType} | Select-Object -ExpandProperty PNRData | Convert-PNRData -SegmentType $SegmentType
		$Content = $Content -replace [regex]::Escape("*$($SegmentType)*"), ($PNRData -join "`r`n")
	}
	$OutFile = "$($OutFolder)\$($RLoc.Name).txt"
	$Content | Set-Content -Path $OutFile
	"Record written to '$($OutFile)'" | Write-Host
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davesnbAuthor Commented:
Thanks so much!
0
davesnbAuthor Commented:
One last question , been trying to rework the template when multiple segment types are present . For example , when there are multiple 'CTC' entries , currently it appends the first with 'OSI' as per template , bu the next CTC it just adds after with no ' OSI' . Not sure on the scripting solution for this .



QK TRNRMF8
.HDQRM1Z 131453
HDQ1Z AACF32/EQ43/67522766
1TEST/HITAKSHIMS
F9110S19APR18 YWGYHM HK1   2010 2327   CAB Y
F9211S23APR18 YHMYWG HK1   1730 1929   CAB Y
OSI 01 CTCC   555555555[F8]
02 CTCE   NC_TEST@YAHOO.CA[F8]
OSI 
OSI E 418 2300060427/2=O YHM-YWG F90211/23 Apr 18 TEST/HITAKSHIMS
E 418 2300060427/1=O YWG-YHM F90110/19 Apr 18 TEST/HITAKSHIMS
OSI 


Open in new window

0
oBdACommented:
That's what the Convert-PNRData is for. It's something that "belongs" to the record, so it has to be added dynamically, not in the static template.
$File = 'C:\Temp\EE_sample.txt'
$OutFolder = 'C:\Temp'
$SegmentTypes = 'NAM', 'FLT', 'CTC', 'AFX', 'TKT', 'RMK'

$SOH = [char]1
$STX = [char]2
$ETX = [char]3

$Template = @"
$($SOH)QK YQMTTXH
.HDQRM1Z 131453
$($STX)HDQ1Z *RLOC*/EQ43/67522766
*NAM*
*FLT*
*CTC*
*AFX*
*TKT*
*RMK*
$($ETX)
"@

Function Convert-PNRData {
[CmdletBinding()]
Param(
	[Parameter(ValueFromPipeline=$true)]
	[string]$PNRData,
	[string]$SegmentType
)
	Process {
		Switch ($SegmentType) {
			'FLT' {
				$PNRData -match '(?<Index>..).(?<Airline>..).(?<Flight>....).(?<Booking>..).(?<Day>..).(?<Date>.......)(?<Airport>...).(?<Tail>.*)' | Out-Null
				$Matches.Airline + $Matches.Flight.TrimStart('0') + $Matches.Booking.Trim() + $Matches.Date + ' ' + $Matches.Airport + $Matches.Tail
			}
			{'CTC', 'AFX', 'TKT', 'RMK' -contains $_} {
				'OSI ' + $PNRData
			}
			default {
				$PNRData
			}
		}
	}
}

Import-Csv -Path $File -Delimiter "`t" | Group-Object rloc | ForEach-Object {
	$RLoc = $_
	"Processing rloc '$($RLoc.Name)' ..." | Write-Host
	$Content = $Template -replace [regex]::Escape("*RLOC*"), $RLoc.Name
	$SegmentTypes | ForEach-Object {
		$SegmentType = $_
		$PNRData = $RLoc.Group | Where-Object {$_.SegmentType -eq $SegmentType} | Select-Object -ExpandProperty PNRData | Convert-PNRData -SegmentType $SegmentType
		$Content = $Content -replace [regex]::Escape("*$($SegmentType)*"), ($PNRData -join "`r`n")
	}
	$OutFile = "$($OutFolder)\$($RLoc.Name).txt"
	$Content | Set-Content -Path $OutFile
	"Record written to '$($OutFile)'" | Write-Host
} 

Open in new window

1
davesnbAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.