Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-12-14
8
Medium Priority
?
364 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 71

Expert Comment

by:Qlemo
ID: 39719929
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 41

Expert Comment

by:footech
ID: 39720300
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
ID: 39720714
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 71

Expert Comment

by:Qlemo
ID: 39723940
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
 

Author Comment

by:josyp
ID: 39734392
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 71

Expert Comment

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

Author Comment

by:josyp
ID: 39735498
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 41

Accepted Solution

by:
footech earned 1500 total points
ID: 39735885
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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
A walk-through example of how to obtain and apply new DID phone numbers to your cloud PBX enabled users that are configured in Office 365. Whether you have 1, 10 or 100+ users in your tenant, it's quite easy to get them phone-enabled and making/rece…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Loops Section Overview

926 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