Solved

How to skip the first two fields if the text file is pipe delimited?

Posted on 2013-12-14
8
314 Views
Last Modified: 2014-02-06
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\Application 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|SCAB|SCAN|SCAS|SCCTP|SCACTP|SCCCY|GFOCID|STS_BAL|RUNDATE
01|20130930_Opeqbal|20131210153005
02|1|111111|111|9090|111111|726|EE|EE|EUR|ABBBBBB|-4000000.00|2013-09-30
02|2|222222|111|9090|222222|727|EE|EE|EUR|ABBBBBB|.00|2013-09-30
03|10|20131210153005

Required
=======
111111|111|9090|111111|726|EE|EE|EUR|ABBBBBB|-4000000.00|2013-09-30
222222|111|9090|222222|727|EE|EE|EUR|ABBBBBB|.00|2013-09-30

20130930_OPEQINT:
In the file
========
00|ROWCOUNT|SCAB|SCAN|SCAS|SCCTP|SCACTP|SCCCY|STS_BAL|DHANMD|RUNDATE
01|20130930_Opeqint|20131210153005
02|1|9999|888888|036|IB|IB|USD|-100000.00|SP109|2013-09-30
02|2|9999|888888|048|IB|IB|USD|-70000.00|SP109|2013-09-30
03|10|20131210153005

Required
=======
9999|888888|036|IB|IB|USD|-100000.00|SP109|2013-09-30
9999|888888|048|IB|IB|USD|-70000.00|SP109|2013-09-30

20130930_OPEQLMT

In the file
========
00|ROWCOUNT|GFOCID|GFCUS|GFCLC|GFGRP|HPLSTR|HHLC|HHCCY|HHLED|LMTAMT|RSKAMT|DHANMD|RUNDATE
01|20130930_Opeqlmt|20131210153010
02|1|PPPPPPP|000000|150|AAAAAA|CORFS|LG999|QAR|1140630|22100000.00|20510589.17|2013-09-30
02|2|IIIIIII|111111|150|IIIIII|CORFS|LG999|QAR|1131130|54525000.00|24042559.60|2013-09-30

Required
=======
PPPPPPP|000000|150|AAAAAA|CORFS|LG999|QAR|1140630|22100000.00|20510589.17|2013-09-30
IIIIIII|111111|150|IIIIII|CORFS|LG999|QAR|1131130|54525000.00|24042559.60
20130930-OPEQINT
20130930-OPEQLMT
20130930-OPEQCNTRL
20130930-OPEQBAL
0
Comment
Question by:josyp
  • 3
  • 3
  • 2
8 Comments
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Instead of trying to do some parsing yourself, how about allowing PS to use properly formatted objects? However, that only makes sense if you take my advice given in http://www.experts-exchange.com/Q_28316051.html of not to use BCP:
$TextInFiles = import-csv -delimiter '|' ("{0}\{1}" -f $DataFolder, $var[2].Trim()) |
    ? { $_.'00' -eq '02' } |
    select -excludeProperty "00", "rowcount"
$TextInFiles | format-table -auto

Open in new window

$TextInFiles will have columns named after the first line.
If you insist in text processing:
$TextInFiles = Get-Content ("{0}\{1}" -f $DataFolder, $var[2].Trim()) |
    select-string '02' -simplematch |
    ? { $line = $_.Split('|')
        $line[2..($line.Count-1)] -join '|'
       }
$TextInFiles

Open in new window

0
 
LVL 39

Expert Comment

by:footech
Comment Utility
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 ($_)"
        }
    }
}

Open in new window

0
 

Author Comment

by:josyp
Comment Utility
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.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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 ($_)"
        }
    }
}

Open in new window

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:josyp
Comment Utility
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,$FormatfilePrefix)
        $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
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
I cannot see any reflection of the code we provided. Why?
0
 

Author Comment

by:josyp
Comment Utility
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\Application 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,Microsoft.PowerShell.Commands.SelectObjectCommand
 
Select-Object : Property "File" cannot be found.
At T:\ApplicationSupport\Application 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,Microsoft.PowerShell.Commands.SelectObjectCommand
 
Select-Object : Property "File" cannot be found.
At T:\ApplicationSupport\Application 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,Microsoft.PowerShell.Commands.SelectObjectCommand
 
Test-Path : Cannot bind argument to parameter 'Path' because it is null.
At T:\ApplicationSupport\Application Development\OPICS\EQUATION to OPICS Data loading\99 Working Files\DataLoad\Quloom.ps1:14 char:21
+         If(Test-Path <<<<  $_)
    + CategoryInfo          : InvalidData: (:) [Test-Path], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.TestPathCommand
0
 
LVL 39

Accepted Solution

by:
footech earned 500 total points
Comment Utility
Change line 8 from
$text = Import-Csv $ControlFile -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() } | select -Expand File

Open in new window

to
$text = Import-Csv $ControlFile -Delimiter "|" -Header "Code","Row","File" | ? { $_.Code -match "02" } | % { $_.File.Trim() }

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Set OWA language and time zone in Exchange for individuals, all users or per database.
A procedure for exporting installed hotfix details of remote computers using powershell
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now