Home > database >  How can subtract a character from csv using PowerShell
How can subtract a character from csv using PowerShell

Time:05-26

I'm trying to insert my CSV into my SQL Server database but just wondering how can I subtract the last three character from CSV GID column and then assigned it to my $CSVHold1 variable.

My CSV file look like this

GID       Source               Type   Message   Time
KLEMOE    http://google.com    Od      Hello     12/22/2022
EEINGJ    http://facebook.com    Od     hey      12/22/2022

Basically I'm trying to get only the first three character from GID and pass that value to my $CSVHold1 variable.

       $CSVImport = Import-CSV $Global:ErrorReport
        ForEach ($CSVLine1 in $CSVImport) {
            $CSVHold1 = $CSVLine1.GID | ForEach-Object { $_.$GID = $_.$GID.subString(0, $_.$GID.Length - 3); $_ }
            $CSVGID1 = $CSVLine1.GID 
            $CSVSource1 = $CSVLine1.Source
            $CSVTYPE1 = $CSVLine1.TYPE
            $CSVMessage1 = $CSVLine1.Message
            }

I'm trying to do like above but some reason I'm getting an error.

You cannot call a method on a null-valued expression.

CodePudding user response:

Your original line 3 was/is not valid syntax as Santiago pointed out.

$CSVHold1 = $CSVLine1.GID | ForEach-Object { $_.$GID = $_.$GID.subString(0, $_.$GID.Length - 3); $_ }

You are calling $_.$GID but you're wanting $_.GID

You also don't need to pipe the object into a loop to achieve what it seems you are asking.

#!/usr/bin/env powershell

$csvimport = Import-Csv -Path $env:HOMEDRIVE\Powershell\TestCSVs\test1.csv

##$CSVImport = Import-CSV $Global:ErrorReport

ForEach ($CSVLine1 in $CSVImport) {
    $CSVHold1 = $CSVLine1.GID.SubString(0, $CSVLine1.GID.Length - 3)
    $CSVGID1 = $CSVLine1.GID 
    $CSVSource1 = $CSVLine1.Source
    $CSVTYPE1 = $CSVLine1.TYPE
    $CSVMessage1 = $CSVLine1.Message
    Write-Output -InputObject ('Changing {0} to {1}' -f $CSVLine1.gid, $CSVHold1)

}

Using your sample data, the above outputs:

C:> . 'C:\Powershell\Scripts\dchero.ps1'
Changing KLEMOE to KLE
Changing EEINGJ to EEI

Lastly, be aware that that the SubString method will fail if the length of $CSVLine1.GID is less than 3.

  • Related