question

AlexBrown-0163 avatar image
0 Votes"
AlexBrown-0163 asked AndreasBaumgarten commented

Can anybody help me please!!!

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;alex.brown@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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexBrown-0163 avatar image
0 Votes"
AlexBrown-0163 answered AndreasBaumgarten commented

Guys,

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You should mark the answer to your problem, not your "thank you". There were two code submissions. One of them solved your problem. That's the one that should be marked as the answer. :-)

1 Vote 1 ·

@RichMatheisen-8856 ,

good point :-)


Kind regards
Andreas Baumgarten

0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered RichMatheisen-8856 commented

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


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for this.
However I want each seperate CSV file to keep the original file name ie REC_78329929.

Also will this auto pick all CSV files individually without me specifying the CSV I wish to change? Ie not specifying the CSV file name? As I want this to do all of the CSV files in that folder, but keep the automation seperate.

Thanks

0 Votes 0 ·

So you want to overwrite each CSV file? That destroys the original file.

0 Votes 0 ·
RichMatheisen-8856 avatar image
1 Vote"
RichMatheisen-8856 answered RichMatheisen-8856 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexBrown-0163 avatar image
0 Votes"
AlexBrown-0163 answered RichMatheisen-8856 commented

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



image.png (162.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

As AndreasBaumgarten posted, use "-FilePath" instead of "-Path" in the Out-File cmdlet.

I corrected the error in the code I posted.

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered AndreasBaumgarten edited

Please try -Filepath instead of -Path:

Out-File -FilePath
https://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.