Update CSV files with the part of the file name itself.

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Daniel Krebs Daniel Krebs 1 month, 1 week ago.

  • Author
    Posts
  • #64285
    Profile photo of Barry Elkington
    Barry Elkington
    Participant

    Hi guys,

    first time poster, any help would be great.

    I'm looking for a script that can update a number of CSV files in a folder, inserting part of the name of the file itself (which is a date).

    For example the files are called:-
    Load-01-Feb-2017.csv
    Load-31-Jan-2017.csv
    Load-30-Jan-2017.csv

    The contents of the files contain:-
    Load1,Clientname,Servername
    Load2,ClientName,Servername

    I would like to be able to insert the date somehow into the current format so it looks like the following for the file created on the 01-Feb-2017:-
    Load1,Clientname,Servername,01-Feb-2017
    Load2,ClientName,Servername,01-Feb-2017

    I then plan to merge them which is easy enough I just need the dates added before I do.

    Thanks for any tips or pointers.

  • #64293
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    Hi Barry,

    I've created a quick example which could work for you.

    param (
        [System.String]
        $Path = 'C:\Input\Load-*',
    
        [System.String]
        $OutputFilePath = 'C:\Output\Combined.csv'
    )
    
    Get-ChildItem -Path $Path -File -PipelineVariable loadFile | 
        ForEach-Object {
            # Split the filename (basename) without the extension into two items
            $fileDate = ($loadFile.BaseName.Split('-', 2)[1])
            
            Get-Content -Path $loadFile.FullName | ForEach-Object { "$PSItem,$fileDate" }
        } | Out-File -FilePath $OutputFilePath
    

    It gets a list of files (Get-ChildItem), iterates over the list (ForEach-Object), splits the filename (basename) into two parts (Load and xx-xxx-xxxx), loads each file into an array lines (Get-Content), iterates over the array of lines (2nd ForEach-Object), appends the file date extracted earlier to each line and outputs everything into one merged/combined file (Out-File).

    • #64297
      Profile photo of Barry Elkington
      Barry Elkington
      Participant

      Legend, thanks Daniel. Works perfectly.

      Again thanks for the quick reply!

  • #64309
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    I'm glad my reply was helpful :-). Thank you.

You must be logged in to reply to this topic.