CODEWITHSUNDEEP
powershellcsvrename
Sarvavyapi
Sarvavyapi

Reputation: 850

powershell - rename csv file based on a column value

My CSV file has contents like this: 

currentTime, SeqNum, Address
1381868225469, 0, 
1381868226491, 1, 38:1c:4a:0:8d:d 
1381868227493, 1, 
1381868228513, 2, 38:1c:4a:0:8d:d 
1381868312825, 43, 
1381868312916, 1694564736, 3a:1c:4a:1:a1:98 
1381868312920, 1694564736, 3a:1c:4a:1:a1:98 
1381868312921, 44,

Depending on whether the 3rd column is empty or not, I want to separate the file into 2 or more files (those with lines containing the 3rd column (fileName should contain the 3rd column) and one without the 3rd column.

Example output:

**File0.txt**
1381868225469, 0, 
1381868227493, 1, 
1381868312825, 43, 
1381868312921, 44, 

**File1-381c4a08dd.txt**
1381868226491, 1, 38:1c:4a:0:8d:d 
1381868228513, 2, 38:1c:4a:0:8d:d 

**File2-3a1c4a1a198.txt**
1381868312916, 1694564736, 3a:1c:4a:1:a1:98 
1381868312920, 1694564736, 3a:1c:4a:1:a1:98

I referred to the stackoverflow questions HERE and HERE to get most of my work done. However, I want to rename my file based on the 3rd column. Since, windows does not accept ":" in the file name, I want to remove the ":" before attaching the 3rd column to my file name. I want my file name to look like this:

FileName-381c4a08dd.txt

How do I go about this? This is my attempt at it so far:

import-csv File.txt | group-object Address | foreach-object {
$_.group | select-object currentTime, SeqNum, Address | convertto-csv -NoTypeInformation | %{$_ -replace '"', ""} | out-file File-$($_.Address.remove(':')).txt -fo -en ascii
}

Upvotes: 1

Views: 1169

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200213

Try something like this:

$csv = Import-Csv 'C:\path\to\file.txt'
$n = 0

# export rows w/o address
$outfile = 'File${n}.txt'
$csv | ? { $null, '' -contains $_.Address } |
  Export-Csv $outfile -NoTypeInformation

# export rows w/ address
$csv | ? { $null, '' -notcontains $_.Address } | Group-Object Address | % {
  $n++
  $outfile = "File${n}-" + $_.Name.Replace(':', '') + '.txt'
  $_.Group | Export-Csv $outfile -NoTypeInformation
}

The filter $null, '' -contains $_.Address is required, because the address record will be $null when you have an empty address and no trailing line break in the last line of the input file.

If you want the output files to be created without header line you need to replace

... | Export-Csv $outfile -NoTypeInformation

with

... | ConvertTo-Csv -NoTypeInformation | select -Skip 1 | Out-File $outfile

Upvotes: 1

Related Questions