Change CSV headers in a directory

Manny 151 Reputation points
2022-01-31T05:04:00.3+00:00

Hi

I have a directory full of CSV files that need to be imported into SQL Server. The problem is that there are 5 columns that do not match up with the destination table so I have to manually change the headers. It would be very helpful to have a PowerShell script that looks at each file in the directory and replaces the problem column headers with the correct one. How would I do this?

Thank you

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,355 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rich Matheisen 44,621 Reputation points
    2022-01-31T20:04:48.497+00:00

    Try something like this:

    Get-ChildItem c:\junk\*.csv -File|
        ForEach-Object{
            $x = Get-Content $_.FullName
            $x[0] = $x[0] -replace "badname1","goodname1"
            $x[0] = $x[0] -replace "badname2","goodname2"
            $x[0] = $x[0] -replace "badname3","goodname3"
            # etc.
            $x | Out-File $_.FullName
        }
    

    Try it on a test directory before letting it loose on production files!


1 additional answer

Sort by: Most helpful
  1. Rich Matheisen 44,621 Reputation points
    2022-02-01T22:38:24.69+00:00

    @Manny
    Try this for a much faster handling of large files:

    Get-ChildItem c:\junk\nsg.csv -File |  
        ForEach-Object {  
            $reader = New-Object IO.StreamReader($_.fullname)  
            $hdr = $reader.ReadLine()  
            # ASSUMES that the CSV delimiter is a comma!  
            $hdr = $hdr -replace ",root,", ",root_symbol,"  
            $hdr = $hdr -replace ",open,", ",open_price,"  
            $hdr = $hdr -replace ",high,", ",high_price,"  
            $hdr = $hdr -replace ",low,", ",low_price,"  
            $hdr = $hdr -replace ",close,", ",close_price,"  
            $rows = $reader.ReadToEnd()   
            $reader.Close()  
            $reader.Dispose()  
            Out-File -FilePath $_.FullName -InputObject $hdr -Encoding ASCII  
            Out-File -FilePath $_.FullName -InputObject $rows -Append -Encoding ASCII  
              
        }