Convert text file data to csv powershell-CodePudding

Below is one of the file data I have in text file

B97SW | CHANGED | rc=0 >>
Server Name";"SystemFolderPath";"IdenityReference";"FileSystemRights";"Vulnerable
B97SW;C:\Windows\system32;CREATOR OWNER;268435456;No
B97SW;C:\Windows\system32;NT AUTHORITY\SYSTEM;268435456;No
B97SW;C:\Windows\system32;NT AUTHORITY\SYSTEM;Modify, Synchronize;No
........

I am trying to replace ";" with "," and write to csv.

Below is the code I wrote but it is not writing the data in csv.

$FileList = Get-ChildItem -Path "C:\Files"

$props=[ordered]@{
     ServerName=''
     SystemFolderPath=''
     IdenityReference=''
     FileSystemRights=''
     Vulnerable=''
}
New-Object PsObject -Property $props | 
     Export-Csv C:\2021.csv -NoTypeInformation

$FinalData = @()

foreach($n_file in $FileList)
{
    $FileName = $n_file.FullName
    $FileContent = Get-Content -Path $FileName | Select-Object -Skip 2


    foreach($line in $FileContent)
    {
        $line = $line -replace(";",",")
        $line | Export-Csv -Path C:\2021.csv -Append -NoTypeInformation -Force
    }    
}

output I am getting

"ServerName","SystemFolderPath","IdenityReference","FileSystemRights","Vulnerable"
"","","","",""
,,,,
,,,,

Please let me know what is wrong I am doing here.

CodePudding user response：

$line | Export-Csv -Path C:\2021.csv -Append -NoTypeInformation -Force

This doesn't work because Export-Csv expects object(s) with properties, but $line is just a string. You need to parse it into an object first, using ConvertFrom-Csv.

Try this:

$FileList = Get-ChildItem -Path "C:\Files"

foreach($n_file in $FileList)
{
    $FileName = $n_file.FullName

    Get-Content -Path $FileName | 
        Select-Object -Skip 2 |
        ConvertFrom-Csv -Delimiter ';' -Header ServerName, SystemFolderPath, IdenityReference, FileSystemRights, Vulnerable |
        Export-Csv -Path C:\2021.csv -Append -NoTypeInformation -Force
}

As we have skipped the original headers, we have to supply these through the -Header parameter of ConvertFrom-Csv.

CodePudding user response：

Your CSV file is goofed up in two ways. First, there is a line of garbage before the header line. Second, in the header line the semi-colons are surrounded by double quotes. The correct form would be to surround the header names with quotes instead.

Once these format errors are fixed, you can read the csv file with this:

Import-Csv myfile.csv -delimiter ";"

Or if you want to produce a comma delimited csv file, try this:

Import-Csv myfile.csv -delimiter ";" | Export-Csv newfile.csv

The result will be correct but it will have a lot of unnecessary double quotes.