Solved

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

Posted on 2013-12-14
8
325 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 69

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 40

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

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 69

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 40

Accepted Solution

by:
footech earned 500 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Synchronize a new Active Directory domain with an existing Office 365 tenant
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…
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…

828 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