Change CSV headers in a directory

Question

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

Answer 1

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!

Answer 2

@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  
          
    }