Convert text file data to csv powershell

Question

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.

Does this answer your question? Powershell: Convert text files to csv files — zerocukor287
– zerocukor287, Commented Nov 9, 2021 at 10:23
@zerocukor287: I am following the same process of replace and write but still i am getting blank csv — Empty Coder
– Empty Coder, Commented Nov 9, 2021 at 10:35
Your input file does not look like a varlid almost-CSV file (or TSV, or SSV if that were a thing). In particular, the missing opening quote before Server Name will throw off any attempt to parse this without further modifications. Also, is B97SW | CHANGED | rc=0 >> part of the data, or just spurious junk? — tripleee
– tripleee, Commented Nov 9, 2021 at 11:13
@tripleee If you look at the Get-Content line, the code is skipping the first two lines, propably because of that. — zett42
– zett42, Commented Nov 9, 2021 at 12:05

zett42 · Accepted Answer · 2021-11-09 11:14:15Z

1

$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.

answered Nov 9, 2021 at 11:14

zett42

28.3k3 gold badges44 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Walter Mitty · Accepted Answer · 2021-11-09 12:18:11Z

0

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.

answered Nov 9, 2021 at 12:18

Walter Mitty

19k2 gold badges32 silver badges60 bronze badges

Collectives™ on Stack Overflow

Convert text file data to csv powershell

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related