Strip lines from Text File

PowerShell Help
1

Hello! First post here!

I’ve been self teaching myself PowerShell for a few months now. I’ve written a few scripts to manipulate some CSV data and format it different ways. This has to do with a HAM radio database for anyone interested. I treat the files as text files rather than Import-CSV and Export-CSV functions for the most part as I’m working with a 95,000+ line CSV file and that would take a bit. This moves much faster without treating it as a database.

My original CSV file “usersraw.csv” is formatted with 7 columns. Although the raw file is headerless, the columns are as follows: Radio ID,Callsign,Name,City,State,Country,Comment

In the output CSV, it needs to be 7 columns, however I need to remove the last columb and add a new column in the middle, copying the data of another column. I need to omit the “Comment” column, and add a new column called “NickNames” in after “Names”, copying over the “Name” column data to it. The final column, with header information, needs to be arranged like so: Radio ID,Callsign,Name,NickName,City,State,Country"

In my past scripts, I’ve had to add header information to the CSV, perfix the row with a number, and add two blank columns, using quotes for text qualifiers, which was fairly easy. The way I did this was:

(gc -Encoding UTF8 $MyPath\usersraw.csv) | %{$i++;“”“$($i-1)”“,$"} | foreach {$ + “,`"Private Call`”,`"None`”" } | Out-File -Encoding UTF8 “$MyPath\formatted.csv”
“`"No.`”,`"Radio ID`",`"Callsign`",`"Name`",`"City`",`"State`",`"Country`",`"Remarks`",`"Call Type`",`"Call Alert`"" | Out-File -Encoding UTF8 -FilePath “$OutPutFilePath”
Add-Content -Path “$OutPutFilePath” -Value (gc -Encoding UTF8 “$MyPath\formatted.csv”)
Basically I took the raw file and added the row number column up front, appended the two new blank columns, saved that to a file, then wrote a new file with the correct header info, then appended the formatted data to that new file with the header.

I tried using Import-CSV however I cannot see functions to add columns and order them the way I want, only add them to the end. How would you suggest I go about doing this? Ive been stuck on this for a few days and can’t quiet find my way around it. Thanks for any input!

2

Erik, I’m new to posting on here as well. I just posted for the first time last week. If I could get a small example of the original csv and desired csv, I could test some code on the data to be more specific. However, don’t post anything proprietary or personal data. With that being said, I would probably use import-csv on the original file and use the -header operator to specify the names you want. Once imported, it would be a relatively simple operation to manipulate as needed and then output a new csv with appropriate headers/order as desired. Look at example 4 in the import-csv help to get an idea of what I’m talking about. https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-6

3

Thank you for the reply!

Example 4 seems to show just changing heading info, where I need to not only change the header but the structured data order as well. The examples they provide don’t really show how to do that I think, or if Import-CSV can do that. I’m looking at using Import-CSV to get my data in there, then I have to somehow manipulate it and use Export-CSV to get just the columns I want in, and somehow append that extra info in there, but I’m not sure on how to modify the objects once I get them imported.

My import line would be as follows:

$ImportedCSV = Import-Csv -Delimiter "," -Header "Radio ID,Callsign,Name,City,State,Country,Comment" -Encoding "UTF8" -Path "C:\path\users.csv"

This is all data from a public database, so not much private info here. This is a small sample, there are normally 95,000+ lines. It’s also essential that it keeps UTF8 encoding.
This is the raw CSV input file. that does not have headers.

3109789,KC1IUC,Erik P Grudzien,Woodbury,Connecticut,United States,DMR
3109790,KC1IUC,Erik Grudzien,Woodbury,Connecticut,United States,DMR

The Header info for this would be:

Radio ID,Callsign,Name,City,State,Country,Comment

This is how I’d like the output to be, and the data needs headers. The Comment column is deleted, and a new NickName column is added, however it can remain blank. It just needs to be there for the program that imports the CSV file. Originally I thought of copying the “Name” data over to the new “NicKName” column but decided it can be left blank.

Radio ID,Callsign,Name,NickName,City,State,Country,
3109789,KC1IUC,Erik P Grudzien,,Woodbury,Connecticut,United States,
3109790,KC1IUC,Erik Grudzien,,Woodbury,Connecticut,United States,
4

Thanks for the replies! I ended up getting it.

First, I wrote a file with my header information for the raw input CSV, adding a extra column at the end for the new column I needed that’s blank. I then appended my raw CSV input to that file. Then I imported the entire CSV file, selected the columns I wanted to export in the order I wanted them, and export the CSV file. I took one extra step to have the Quotes taken out of the CSV file, as the software I use does not want standard quote text qualifiers, and removed the first line that had some PowerShell info in it. Problem solved! Thank you for pointing me in the right direction. This page was also helping in finding my solution. https://chris.koester.io/index.php/2015/12/08/use-powershell-select-columns-csv-files/

"Radio ID,Callsign,Name,City,State,Country,Comment,NickName" | Out-File -Encoding UTF8 -FilePath "datawithheader.csv"
Add-Content -Path "datawithheader.csv" -Value (gc -Encoding UTF8 "users.csv")
Import-Csv -Encoding UTF8 -Path "datawithheader.csv" | Select 'Radio ID','Callsign','Name','NickName','City','State','Country' | Export-Csv -Encoding UTF8 -Path "withquotes.csv" -Delimiter ","
gc -Encoding UTF8 -Path "withquotes.csv" | % {$_.Replace('"','')} | Select-Object -Skip 1 | Out-File -Encoding UTF8 -FilePath "final.csv"
5

Erik, Your import line -header switch should be an array not a single string. The code below illustrates how you could solve this by either using import/export csv cmdlets and how it could also be solved using just the files (no headers or csv info). I put both in so you can decide which is the most efficient for your data set.

$OriginalFile    = 'C:\Users\1105208606E\Documents\users.csv' #Change to the path of your file
$NewFile         = 'C:\Users\1105208606E\Documents\usersnew.csv' #Change as needed
$OriginalHeaders = 'Radio ID', 'Callsign','Name','City','State','Country','Comment','NickName' #added Nickname at the end so it will be empty
$NewHeaders      = 'Radio ID', 'Callsign','Name','NickName','City','State','Country','Comment' #put in the order desired for output

#Using Import-Csv and Export-Csv
Import-Csv -Delimiter "," -Header $originalHeaders -Path $OriginalFile -Encoding UTF8 | Select-Object $NewHeaders | Export-Csv -Path $NewFile -Encoding UTF8

#Using just string manipulation
Get-Content $OriginalFile | % {
    $info = $_ -split ","
    ($info[0], $info[1], $info[2], '', $info[3], $info[4], $info[5], $info[6]) -join ',' |
    Out-File $NewFile -Append utf8
}