josyp
asked on
How to skip the first two fields if the text file is pipe delimited?
I have written a power shell script which checks for the control file and if found reads the file names and opens it to read line by line.
What I am not able to do is to read the line and skip the first two fields. The file is delimited by "|".
Powershell code:
cls
$DataFolder = "T:\ApplicationSupport\App lication Development\OPICS\EQUATION to OPICS Data loading\99 Working Files\DataLoad\data"
$ControlFileName = "*OPEQCNTRL"
$ControlFile = ("{0}\{1}" -f $DataFolder, $ControlFileName)
If (Test-Path $ControlFile){
$text = Get-Content $ControlFile | select-string '02' -simplematch
$text| ForEach-Object {
if ($_.Line.Length -gt 0) {
$var = $_.Line.Split('|')
If(Test-Path ("{0}\{1}" -f $DataFolder, $var[2].Trim()) )
{
echo "File present for upload" ($var[2].Trim())
$TextInFiles = Get-Content ("{0}\{1}" -f $DataFolder, $var[2].Trim()) | select-string '02' -simplematch
echo $TextInFiles
}
else
{
Echo "File not present for upload" ($var[2].Trim())
}
}
}
}Else{
ECHO "File does not exist"
}
20130930_OPEQBAL:
In the file
========
00|ROWCOUNT|GFCUS|GFCLC|SC AB|SCAN|SC AS|SCCTP|S CACTP|SCCC Y|GFOCID|S TS_BAL|RUN DATE
01|20130930_Opeqbal|201312 10153005
02|1|111111|111|9090|11111 1|726|EE|E E|EUR|ABBB BBB|-40000 00.00|2013 -09-30
02|2|222222|111|9090|22222 2|727|EE|E E|EUR|ABBB BBB|.00|20 13-09-30
03|10|20131210153005
Required
=======
111111|111|9090|111111|726 |EE|EE|EUR |ABBBBBB|- 4000000.00 |2013-09-3 0
222222|111|9090|222222|727 |EE|EE|EUR |ABBBBBB|. 00|2013-09 -30
20130930_OPEQINT:
In the file
========
00|ROWCOUNT|SCAB|SCAN|SCAS |SCCTP|SCA CTP|SCCCY| STS_BAL|DH ANMD|RUNDA TE
01|20130930_Opeqint|201312 10153005
02|1|9999|888888|036|IB|IB |USD|-1000 00.00|SP10 9|2013-09- 30
02|2|9999|888888|048|IB|IB |USD|-7000 0.00|SP109 |2013-09-3 0
03|10|20131210153005
Required
=======
9999|888888|036|IB|IB|USD| -100000.00 |SP109|201 3-09-30
9999|888888|048|IB|IB|USD| -70000.00| SP109|2013 -09-30
20130930_OPEQLMT
In the file
========
00|ROWCOUNT|GFOCID|GFCUS|G FCLC|GFGRP |HPLSTR|HH LC|HHCCY|H HLED|LMTAM T|RSKAMT|D HANMD|RUND ATE
01|20130930_Opeqlmt|201312 10153010
02|1|PPPPPPP|000000|150|AA AAAA|CORFS |LG999|QAR |1140630|2 2100000.00 |20510589. 17|2013-09 -30
02|2|IIIIIII|111111|150|II IIII|CORFS |LG999|QAR |1131130|5 4525000.00 |24042559. 60|2013-09 -30
Required
=======
PPPPPPP|000000|150|AAAAAA| CORFS|LG99 9|QAR|1140 630|221000 00.00|2051 0589.17|20 13-09-30
IIIIIII|111111|150|IIIIII| CORFS|LG99 9|QAR|1131 130|545250 00.00|2404 2559.60
20130930-OPEQINT
20130930-OPEQLMT
20130930-OPEQCNTRL
20130930-OPEQBAL
What I am not able to do is to read the line and skip the first two fields. The file is delimited by "|".
Powershell code:
cls
$DataFolder = "T:\ApplicationSupport\App
$ControlFileName = "*OPEQCNTRL"
$ControlFile = ("{0}\{1}" -f $DataFolder, $ControlFileName)
If (Test-Path $ControlFile){
$text = Get-Content $ControlFile | select-string '02' -simplematch
$text| ForEach-Object {
if ($_.Line.Length -gt 0) {
$var = $_.Line.Split('|')
If(Test-Path ("{0}\{1}" -f $DataFolder, $var[2].Trim()) )
{
echo "File present for upload" ($var[2].Trim())
$TextInFiles = Get-Content ("{0}\{1}" -f $DataFolder, $var[2].Trim()) | select-string '02' -simplematch
echo $TextInFiles
}
else
{
Echo "File not present for upload" ($var[2].Trim())
}
}
}
}Else{
ECHO "File does not exist"
}
20130930_OPEQBAL:
In the file
========
00|ROWCOUNT|GFCUS|GFCLC|SC
01|20130930_Opeqbal|201312
02|1|111111|111|9090|11111
02|2|222222|111|9090|22222
03|10|20131210153005
Required
=======
111111|111|9090|111111|726
222222|111|9090|222222|727
20130930_OPEQINT:
In the file
========
00|ROWCOUNT|SCAB|SCAN|SCAS
01|20130930_Opeqint|201312
02|1|9999|888888|036|IB|IB
02|2|9999|888888|048|IB|IB
03|10|20131210153005
Required
=======
9999|888888|036|IB|IB|USD|
9999|888888|048|IB|IB|USD|
20130930_OPEQLMT
In the file
========
00|ROWCOUNT|GFOCID|GFCUS|G
01|20130930_Opeqlmt|201312
02|1|PPPPPPP|000000|150|AA
02|2|IIIIIII|111111|150|II
Required
=======
PPPPPPP|000000|150|AAAAAA|
IIIIIII|111111|150|IIIIII|
20130930-OPEQINT
20130930-OPEQLMT
20130930-OPEQCNTRL
20130930-OPEQBAL
I would apply the same logic that Qlemo first suggests to reading the control file as well.
$DataFolder = "T:\ApplicationSupport\Application Development\OPICS\EQUATION to OPICS Data loading\99 Working Files\DataLoad\data"
$ControlFileName = "*OPEQCNTRL"
$ControlFile = ("{0}\{1}" -f $DataFolder, $ControlFileName)
Push-Location $DataFolder
If (Test-Path $ControlFile){
$text = Import-Csv $ControlFile -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() }
# The Where-Object filtering already skips the first line, but in
# situations where that might not be the case you could use the below
#$text = Get-Content $ControlFile | Select -Skip 1 | ConvertFrom-Csv -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() }
$text | ForEach-Object {
If(Test-Path $_ )
{
echo "File present for upload ($_)"
$TextInFiles = Import-Csv -delimiter '|' $_ |
? { $_.'00' -eq '02' } |
Select * -excludeProperty "00", "rowcount"
$TextInFiles | Format-Table -auto
}
else
{
Echo "File not present for upload ($_)"
}
}
}
ASKER
I would love to take your advice not to use BCP but the request that I got, specifically tells to use BCP, that is why.
Once I get the file in a specific layout then I can pass the format file and data file to BCP for bulk upload.
Once I get the file in a specific layout then I can pass the format file and data file to BCP for bulk upload.
A small adjustment to footech's code should do to create files with ".bcp" extension added, and without the first two columns:
$DataFolder = "T:\ApplicationSupport\Application Development\OPICS\EQUATION to OPICS Data loading\99 Working Files\DataLoad\data"
$ControlFileName = "*OPEQCNTRL"
$ControlFile = ("{0}\{1}" -f $DataFolder, $ControlFileName)
Push-Location $DataFolder
If (Test-Path $ControlFile){
$text = Import-Csv $ControlFile -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() } | select -Expand File
$text | ForEach-Object {
If(Test-Path $_)
{
echo "File present for upload ($_)"
$TextInFiles = Import-Csv -delimiter '|' $_ |
? { $_.'00' -eq '02' } |
Select * -excludeProperty "00", "rowcount"
# $TextInFiles | Format-Table -auto # only used for seeing progress
$TextInFiles | Export-CSV -NoType "$_.bcp"
}
else
{
Echo "File not present for upload ($_)"
}
}
}
ASKER
Well this is how my final code looks like but is there a better way to write it?
I would like to incorporate the output file generation ,rename, truncate and load in a for loop. Where ever I have hardcoded the database details need to pass variables.
cls
add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100
#PARAMETERS
$DataFolder = "C:\Data"
$OutputFolder = "C:\Output"
$ArchiveFolder = "C:\Archive"
$FileSuffix = "*_OPEQCNTRL"
$FormatfilePrefix = "OP_EQ_*.FMT"
$DataFiles = "OP_EQ_*"
$FormatFileFolder = "C:\Format"
$dbserver = "servername"
$dbname = "OPICS"
$dbuser = "USERID"
$dbpassword = "PASSWORD"
$Query1 = "TRUNCATE TABLE OPICS.dbo.OP_EQ_BAL"
$Query2 = "TRUNCATE TABLE OPICS.dbo.OP_EQ_INT"
$Query3 = "TRUNCATE TABLE OPICS.dbo.OP_EQ_LMT"
$ControlFileCheck = ("{0}\{1}" -f $DataFolder,$FileSuffix)
#Checking if the control file exists
IF (test-path $ControlFileCheck -include $FileSuffix)
{
$ControlFileContent = Get-Content $ControlFileCheck | select-string '02' -simplematch
$ControlFileContent| ForEach-Object {
if ($_.Line.Length -gt 0) {
$var = $_.Line.Split('|')
#$text2 = Get-Content ("{0}\{1}" -f $DataFolder, $var[2].Trim()) | select-string '02' -simplematch
$UploadFiles = ("{0}\{1}" -f $DataFolder,$var[2].Trim() )
$OutputFile = ("{0}\{1}" -f $OutputFolder,$var[2].Trim ())
#Reading specific lines and outputing
$BCPReady = Get-Content $UploadFiles | select-string '02' -simplematch | out-file $OutputFile -encoding "ASCII"
#Renameing the output file
set-location $OutputFolder
Dir | rename-item -newname { $_.name -replace '20[^_]+_','' }
DIR | rename-item –NewName { $_.name –replace "OPEQ","OP_EQ_" }
echo $BCPReady
}
}
}
ELSE
{
ECHO "FILE NOT PRESENT"
}
$FF = ("{0}\{1}" -f $FormatFileFolder,$Formatf ilePrefix)
$DF = ("{0}\{1}" -f $OutputFolder,$DataFiles)
$A = Get-ChildItem $DF | select -expand name # to capture the data file name from output folder
$B = Get-ChildItem $ff | select -expand name # to capture the bcp format file name
#BCP procedure
$BCPText1 = ("bcp {1} in {0}\{1} -f {2}\{3} -S $dbserver -U $dbuser -P $dbpassword -d $dbname" -f $OutputFolder,$A[0], $FormatFileFolder, $B[0])
$BCPText2 = ("bcp {1} in {0}\{1} -f {2}\{3} -S $dbserver -U $dbuser -P $dbpassword -d $dbname" -f $OutputFolder,$A[1], $FormatFileFolder, $B[1])
$BCPText3 = ("bcp {1} in {0}\{1} -f {2}\{3} -S $dbserver -U $dbuser -P $dbpassword -d $dbname" -f $OutputFolder,$A[2], $FormatFileFolder, $B[2])
#TRUNCATING AND LOADING TABLES
Invoke-sqlcmd -ServerInstance $dbserver -User $dbuser -Password $dbpassword -Database $dbname -Query $query1
Invoke-expression $BCPText1
Invoke-sqlcmd -ServerInstance $dbserver -User $dbuser -Password $dbpassword -Database $dbname -Query $query2
Invoke-expression $BCPText2
Invoke-sqlcmd -ServerInstance $dbserver -User $dbuser -Password $dbpassword -Database $dbname -Query $query3
Invoke-expression $BCPText3
remove-pssnapin sqlserverprovidersnapin100
remove-pssnapin sqlservercmdletsnapin100
I would like to incorporate the output file generation ,rename, truncate and load in a for loop. Where ever I have hardcoded the database details need to pass variables.
cls
add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100
#PARAMETERS
$DataFolder = "C:\Data"
$OutputFolder = "C:\Output"
$ArchiveFolder = "C:\Archive"
$FileSuffix = "*_OPEQCNTRL"
$FormatfilePrefix = "OP_EQ_*.FMT"
$DataFiles = "OP_EQ_*"
$FormatFileFolder = "C:\Format"
$dbserver = "servername"
$dbname = "OPICS"
$dbuser = "USERID"
$dbpassword = "PASSWORD"
$Query1 = "TRUNCATE TABLE OPICS.dbo.OP_EQ_BAL"
$Query2 = "TRUNCATE TABLE OPICS.dbo.OP_EQ_INT"
$Query3 = "TRUNCATE TABLE OPICS.dbo.OP_EQ_LMT"
$ControlFileCheck = ("{0}\{1}" -f $DataFolder,$FileSuffix)
#Checking if the control file exists
IF (test-path $ControlFileCheck -include $FileSuffix)
{
$ControlFileContent = Get-Content $ControlFileCheck | select-string '02' -simplematch
$ControlFileContent| ForEach-Object {
if ($_.Line.Length -gt 0) {
$var = $_.Line.Split('|')
#$text2 = Get-Content ("{0}\{1}" -f $DataFolder, $var[2].Trim()) | select-string '02' -simplematch
$UploadFiles = ("{0}\{1}" -f $DataFolder,$var[2].Trim()
$OutputFile = ("{0}\{1}" -f $OutputFolder,$var[2].Trim
#Reading specific lines and outputing
$BCPReady = Get-Content $UploadFiles | select-string '02' -simplematch | out-file $OutputFile -encoding "ASCII"
#Renameing the output file
set-location $OutputFolder
Dir | rename-item -newname { $_.name -replace '20[^_]+_','' }
DIR | rename-item –NewName { $_.name –replace "OPEQ","OP_EQ_" }
echo $BCPReady
}
}
}
ELSE
{
ECHO "FILE NOT PRESENT"
}
$FF = ("{0}\{1}" -f $FormatFileFolder,$Formatf
$DF = ("{0}\{1}" -f $OutputFolder,$DataFiles)
$A = Get-ChildItem $DF | select -expand name # to capture the data file name from output folder
$B = Get-ChildItem $ff | select -expand name # to capture the bcp format file name
#BCP procedure
$BCPText1 = ("bcp {1} in {0}\{1} -f {2}\{3} -S $dbserver -U $dbuser -P $dbpassword -d $dbname" -f $OutputFolder,$A[0], $FormatFileFolder, $B[0])
$BCPText2 = ("bcp {1} in {0}\{1} -f {2}\{3} -S $dbserver -U $dbuser -P $dbpassword -d $dbname" -f $OutputFolder,$A[1], $FormatFileFolder, $B[1])
$BCPText3 = ("bcp {1} in {0}\{1} -f {2}\{3} -S $dbserver -U $dbuser -P $dbpassword -d $dbname" -f $OutputFolder,$A[2], $FormatFileFolder, $B[2])
#TRUNCATING AND LOADING TABLES
Invoke-sqlcmd -ServerInstance $dbserver -User $dbuser -Password $dbpassword -Database $dbname -Query $query1
Invoke-expression $BCPText1
Invoke-sqlcmd -ServerInstance $dbserver -User $dbuser -Password $dbpassword -Database $dbname -Query $query2
Invoke-expression $BCPText2
Invoke-sqlcmd -ServerInstance $dbserver -User $dbuser -Password $dbpassword -Database $dbname -Query $query3
Invoke-expression $BCPText3
remove-pssnapin sqlserverprovidersnapin100
remove-pssnapin sqlservercmdletsnapin100
I cannot see any reflection of the code we provided. Why?
ASKER
The code that you provide did not work, I got this error message message
I am using power shell version 2.
Select-Object : Property "File" cannot be found.
At T:\ApplicationSupport\Appl ication Development\OPICS\EQUATION to OPICS Data loading\99 Working Files\DataLoad\Quloom.ps1: 11 char:139
+ $text = Import-Csv $ControlFile -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() } | select <<<< -Expand File
+ CategoryInfo : InvalidArgument: (20130930_OPEQBAL:PSObject ) [Select-Object], PSArgumentException
+ FullyQualifiedErrorId : ExpandPropertyNotFound,Mic rosoft.Pow erShell.Co mmands.Sel ectObjectC ommand
Select-Object : Property "File" cannot be found.
At T:\ApplicationSupport\Appl ication Development\OPICS\EQUATION to OPICS Data loading\99 Working Files\DataLoad\Quloom.ps1: 11 char:139
+ $text = Import-Csv $ControlFile -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() } | select <<<< -Expand File
+ CategoryInfo : InvalidArgument: (20130930_OPEQLMT:PSObject ) [Select-Object], PSArgumentException
+ FullyQualifiedErrorId : ExpandPropertyNotFound,Mic rosoft.Pow erShell.Co mmands.Sel ectObjectC ommand
Select-Object : Property "File" cannot be found.
At T:\ApplicationSupport\Appl ication Development\OPICS\EQUATION to OPICS Data loading\99 Working Files\DataLoad\Quloom.ps1: 11 char:139
+ $text = Import-Csv $ControlFile -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() } | select <<<< -Expand File
+ CategoryInfo : InvalidArgument: (20130930_OPEQINT:PSObject ) [Select-Object], PSArgumentException
+ FullyQualifiedErrorId : ExpandPropertyNotFound,Mic rosoft.Pow erShell.Co mmands.Sel ectObjectC ommand
Test-Path : Cannot bind argument to parameter 'Path' because it is null.
At T:\ApplicationSupport\Appl ication Development\OPICS\EQUATION to OPICS Data loading\99 Working Files\DataLoad\Quloom.ps1: 14 char:21
+ If(Test-Path <<<< $_)
+ CategoryInfo : InvalidData: (:) [Test-Path], ParameterBindingValidation Exception
+ FullyQualifiedErrorId : ParameterArgumentValidatio nErrorNull NotAllowed ,Microsoft .PowerShel l.Commands .TestPathC ommand
I am using power shell version 2.
Select-Object : Property "File" cannot be found.
At T:\ApplicationSupport\Appl
+ $text = Import-Csv $ControlFile -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() } | select <<<< -Expand File
+ CategoryInfo : InvalidArgument: (20130930_OPEQBAL:PSObject
+ FullyQualifiedErrorId : ExpandPropertyNotFound,Mic
Select-Object : Property "File" cannot be found.
At T:\ApplicationSupport\Appl
+ $text = Import-Csv $ControlFile -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() } | select <<<< -Expand File
+ CategoryInfo : InvalidArgument: (20130930_OPEQLMT:PSObject
+ FullyQualifiedErrorId : ExpandPropertyNotFound,Mic
Select-Object : Property "File" cannot be found.
At T:\ApplicationSupport\Appl
+ $text = Import-Csv $ControlFile -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() } | select <<<< -Expand File
+ CategoryInfo : InvalidArgument: (20130930_OPEQINT:PSObject
+ FullyQualifiedErrorId : ExpandPropertyNotFound,Mic
Test-Path : Cannot bind argument to parameter 'Path' because it is null.
At T:\ApplicationSupport\Appl
+ If(Test-Path <<<< $_)
+ CategoryInfo : InvalidData: (:) [Test-Path], ParameterBindingValidation
+ FullyQualifiedErrorId : ParameterArgumentValidatio
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
$TextInFiles will have columns named after the first line.If you insist in text processing:
Open in new window