CSV file data manipulation

Hi everyone,
I need help to change data in CSV cells.
Like changing computer FQDN “NAME.example.com” to only Hostname “NAME”.

Any help will be appreciated.

Levio

Hi, welcome to the forum :wave:

It would be helpful to us if you could provide some sample CSV data, and the PowerShell code that you’ve tried so far. It would also be good to know what you want to do with the changed data: are you adding it as a new column to the same CSV, exporting it to another file, passing it to another command etc.

When posting code, sample data, or errors, please use the </> button to format your code.

Thank you Matt for your quick response.

Source CSV data:
Devices
CITY-50319.rochmn.us
CITY-30425
CITY-24815.rochmn.us
CITY-36540
CITY-50415

I would want the outcome to be like this:

Devices
CITY-50319
CITY-30425
CITY-24815
CITY-36540
CITY-50415

That’s not really a CSV file (even it it has a CSV extension) you can just treat it as a list in a text file.

I would use Get-Content to read the file, -replace to replace the domain name, and Set-Content to overwrite the file, assuming that’s what you want to do.

We do not provide scripts on request; this is a forum to help you write your own scripts, so please read the above help files and post your code if you have any problems.

1 Like

I am new and I do not know that I can download the csv file. So I just copy and paste.

Levio

[quote=“matt-bloomfield, post:4, topic:18654”]

Import your CSV data

$CSVData = Import-Csv -Path C:\Temp\Test3.csv

Loop through each row in your data set, and find xxxx.rochmn.us

foreach ($Row in ($CSVData | Where-Object { $_ -contains “.rochmn.us” } ) ) {

# replace with nothing
-replace ".rochmn.us" Set-Content ""

}

I have tried that but no change in the outcome.

If it’s just a list of names, you don’t need to use Import-CSV. Did you read the help files that I linked?

2 Likes

Yes, I read it. The file csv is imported from application. I have just copied a tiny part to show since I can note upload the csv file.

I have tried this and I am getting error.

$CSVData = Import-Csv -Path C:\Temp\Test3.csv

Loop through each row in your data set, and find xxxx.rochmn.us

foreach ($data in $CSVData.Hostname)
{ $_ -contains “.rochmn.us” | -Replace ‘.rochmn.us’, ‘’}

You could have included some columns with fake data to illustrate that it was a CSV file.

You seem to be confusing the foreach loop with ForEach-Object. They are different things.

The basic code is this:

$CSVData = Import-CSV -Path E:\Temp\Files\devices.csv

foreach ($row in $CSVData) {
    $row.Devices -replace '.rochmn.us'
}

That assumes that your column is called Devices as per your first example, not Hostname as in your second.

That works fantastic! Matt, you are a genius
Thank you so much.