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

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 https://www.experts-exchange.com/questions/28316051/Power-shell-script-to-read-text-file-and-populate-data-to-SQL-database-tables.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

Select allOpen 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

Select allOpen in new window

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 ($_)"
        }
    }
}

Select allOpen in new window

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.

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 ($_)"
        }
    }
}

Select allOpen in new window

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

I cannot see any reflection of the code we provided. Why?

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