Can anybody help me please!!!

Alex Brown 96 Reputation points
2021-03-25T21:36:58.18+00:00

I have a project via my university and I really don't know where to start.... I need to amend a batch of .csv files within a folder using a powershell command. So I have the format below in which the csv file is in.

H;GBA/OUT/00029;20210218;Alex Brown;Old Doncaster Road;Rotherham;S63 7EE;GB;07738055103;******@gba-fm.com;GBP;2975.26;0;Sale;1;3 x (1.0 x Chair KJ-2-EMMA-SA-AB [PACK0000013];Pallet;KN;KN;GBA1:KN:KN::;
P;GBA/OUT/00029;20210218;1;12;KJ-2-EMMA-SA-AB;399.48;0.189888;13.2;

I need to change the file, where it extracts only the H line files, but only keeps the following information:

GBA/OUT/00029;20210218;

There is usually between 50-100 csvs per day passing through this folder, so I am really struggling on an outcome in powershell. Can anybody please help me!!

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,623 questions
0 comments No comments
{count} votes

Accepted answer
  1. Alex Brown 96 Reputation points
    2021-03-26T22:08:19.057+00:00

    Guys,

    I could not thank you enough!
    Thanks that works perfect.


4 additional answers

Sort by: Most helpful
  1. Rich Matheisen 47,596 Reputation points
    2021-03-26T02:24:42.35+00:00

    The previous code sample will not overwrite the original file, but this code will. Adjust the Get-ChildItem to use the directory you want and the file extension you want.

    $Script:FilePath = ""
    $Script:OnlyH = @()
    $NumberOfColumns = 30   # All files must have 30 columns!
    $Headers = @()          # some bogus column headers -- names aren't important
    1..$NumberOfColumns | ForEach-Object{ $Headers += "H$_" }   # create the bogus column names
    
    $allfiles = Get-ChildItem -Path c:\junk\*.csv # get all the CSV files in the directory
                                                    # the files will be overwritten so the Import-Csv
                                                    # and Out-File cannot use pipes!
    $allfiles |
        ForEach-Object{
            $Script:OnlyH = @()                     # will contain the new file contents -- start with empty array
            $Script:FilePath = $_.FullName          # Get the complete file path
            $rows = Import-Csv $_.FullName -Delimiter ';' -Header $Headers
            ForEach ($row in $rows){
                if ($row.H1 -ceq 'H'){              # only capital letter "H" (case-sensitive comparison)
                    $Script:OnlyH += "{0};{1};" -f $row.H2,$row.H3  # create the new record
                }
            }
            $Script:OnlyH | Out-File -FilePath $Script:FilePath     #Overwrite old file with new data
        }
    

    EDIT: Corrected Out-File parameter

    1 person found this answer helpful.
    0 comments No comments

  2. Rich Matheisen 47,596 Reputation points
    2021-03-25T23:06:43.117+00:00

    This ought to do it:

    $headers = @()
    1..30 | ForEach-Object{ $headers += "H$_"}  # create the headers (if the file has none -- 
                                                # otherwise just ignore this line, the line above, and
                                                # remove the '-Header $headers"' from the Import-CSV
                                                # and adjust the formatting to use the names of the columns
                                                # from the file's headers)
    
    Import-Csv x:\file.csv -Delimiter ';' -Header $headers | 
        ForEach-Object{
            if ($_.H1 -ceq "H"){            # just grab the stuff the has "H" in ht 1st column
                "{0};{1};" -f $_.H2, $_.H3  # format it as a string
            }
        } | Out-File X:\Stuff-I-Want.txt
    

  3. Alex Brown 96 Reputation points
    2021-03-26T14:38:25.217+00:00

    Hi,
    I have tried the above, but keep getting the below error. The Get-Childitem folder is called c:\ls\rec, however it doesn't recognise the path:

    Any ideas please?

    81917-image.png


  4. Andreas Baumgarten 117.5K Reputation points MVP
    2021-03-26T16:48:57.047+00:00

    Please try -Filepath instead of -Path:

    Out-File -FilePath
    https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/out-file?view=powershell-7.1

    ----------

    (If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

    Regards
    Andreas Baumgarten

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.