Dynamic Output File

Article
2013-11-14

Question

_{Thursday, November 14, 2013 6:58 PM}

Hi,

I am working on a powershell scipt which does the following. It will read a csv file where the PollDevice is one of the columns and create a different csv file for each of the PollDevice. For some reason this script is not working. I appreciate any response on this

$MOMSFile = Import-Csv 'C:\CADATA\MOMS\BOM.csv'
foreach ($Name in $MOMSFile)
{
$BOMName=$Name.PollDevice;
out-file -FilePath C:\CADATA\MOMS\BOMName.csv -Force
}

Thanks

All replies (45)

_{Thursday, November 14, 2013 9:22 PM ✅Answered}

Hi Mike,

That worked like a charm. I have the various files created based on my device name. However when I open the files it is empty with only these characters. Is there a way, I can upload the csv file (BOM.csv'
) for review, I don't see an option to attach it.

The following shows up

ÿþ

The code as written just creates the files, it doesn't try to put any data in them.

As for the odd characters, add -Encoding ascii to the Out-File command.

EDIT: Chen and I are definitely on the same wavelength today. =]

Don't retire TechNet! - (Maybe there's still a chance for hope, over 12,300+ strong and growing)

_{Thursday, November 14, 2013 10:13 PM ✅Answered}

Please use this

Import-Csv 'C:\CADATA\MOMS\BOM1.csv' |
Group-Object -Property PollDevice, ZOID, Monitored Resource, Value, Status, Poll Date, Message.ZPOLLING_HOST, Value_1, Status_1, Poll Date_1, Message_1 |
ForEach-Object {
    $groupInfo = $_
    $groupInfo.Group |
    Export-Csv -Path "C:\CADATA\MOMS\$($groupInfo.Name).csv" -NoTypeInformation
}

This has no meaning

$MOMSFile = Import-Csv 'C:\CADATA\MOMS\BOM1.csv'

Regards Chen V [MCTS SharePoint 2010]

_{Friday, November 15, 2013 1:05 AM ✅Answered}

Try it this way:

$invalid = New-Object System.Text.StringBuilder

[System.IO.Path]::GetInvalidFileNameChars() |
ForEach-Object {
    $null = $invalid.Append('\x{0:X2}' -f [int]$_)
}

$invalid = "[$invalid]"

Import-Csv C:\CADATA\MOMS\BOM.csv |
Group-Object -Property PollDevice |
ForEach-Object {
    $groupInfo = $_
    
    $fileName = $groupInfo.Name -replace $invalid, '-'

    $groupInfo.Group |
    Export-Csv -Path "C:\CADATA\MOMS\$fileName.csv" -NoTypeInformation
}

This is the same thing I posted earlier, with the addition of code to replace any invalid characters with hyphens. You shouldn't need to change the Group-Object parameters at all.

_{Friday, November 15, 2013 2:54 PM ✅Answered}

Thank you David, I appreciate your help and support. This worked great!

_{Tuesday, November 19, 2013 7:23 PM ✅Answered}

On a side note, you can work with a tab-delimited file without having to convert them to commas first. Just use the -Delimiter parameter of Import-Csv (or ConvertFrom-Csv), passing it the tab character:

Import-Csv '.\somefile.csv' -Delimiter "`t"

However, that's not going to be enough to get your code working. The headers must be unique for the Import-Csv cmdlet to work. You can either correct that problem in the source file, or you can write some code based around the -Header parameter of Import-Csv / ConvertFrom-Csv. Here's an example of some code to do this:

$file = '.\test.txt'

# Fetch the names of the headers from the actual file.
$headerLine = Get-Content -Path $file | Select-Object -First 1
$tokens = Get-StringToken -String $headerLine -Delimiter "`t"

# Build an array of header names, with a unique suffix added for any duplicates.
$headers = @()

foreach ($token in $tokens)
{
    $suffix = 1
    while ($headers -contains $token)
    {
        $token = "${token}_$suffix"
        $suffix++
    }

    $headers += $token
}

# Use the headers array to import the actual data

Get-Content -Path $file |
Select-Object -Skip 1 |
ConvertFrom-Csv -Header $headers -Delimiter "`t"

Note: This code uses the Get-StringToken function that I wrote for ease of parsing this type of data. I didn't inclue the source code of the function, to keep the size of this post down, but you can download its code here: http://gallery.technet.microsoft.com/Generic-PowerShell-string-e9ccfe73/view/Reviews

_{Thursday, November 14, 2013 7:13 PM}

This should do your task

$MOMSFile = Import-Csv C:\Temp\Book1.csv
foreach ($Name in $MOMSFile)
{
$_.PollDevice | out-file -FilePath "C:\Temp\$Name.csv" -Force
}

Change the path as required

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 7:25 PM}

Hi,

Add in a few quotes and your code will work just fine:

$MOMSFile = Import-Csv 'C:\CADATA\MOMS\BOM.csv'

foreach ($Name in $MOMSFile) {

    $BOMName=$Name.PollDevice;
    out-file -FilePath "C:\CADATA\MOMS\$BOMName.csv" -Force

}

Don't retire TechNet! - (Maybe there's still a chance for hope, over 12,300+ strong and growing)

_{Thursday, November 14, 2013 7:27 PM}

I think your CSV is not in correct format

your code worked in my system - Just Double Quotes

$MOMSFile = Import-Csv "C:\Temp\Book1.csv"
foreach ($Name in $MOMSFile)
{
$BOMName=$Name.PollDevice;
out-file -FilePath "C:\Temp\$BOMName.csv" -Force
}

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 7:29 PM}

Hi,

Add in a few quotes and your code will work just fine:
$MOMSFile = Import-Csv 'C:\CADATA\MOMS\BOM.csv'

foreach ($Name in $MOMSFile) {

    $BOMName=$Name.PollDevice;
    out-file -FilePath "C:\CADATA\MOMS\$BOMName.csv" -Force

}
Don't retire TechNet! - (Maybe there's still a chance for hope, over 12,300+ strong and growing)

Thanks Mike !!! this time I didn't refreshed IE :)

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 7:32 PM}

Thanks Mike !!! this time I didn't refreshed IE :)

Regards Chen V [MCTS SharePoint 2010]

Gotta love it when that happens.

Don't retire TechNet! - (Maybe there's still a chance for hope, over 12,300+ strong and growing)

_{Thursday, November 14, 2013 7:33 PM}

I'd probably do it like this:

Import-Csv C:\Temp\Book1.csv |
Group-Object -Property PollDevice |
ForEach-Object {
    $groupInfo = $_
    
    $groupInfo.Group |
    Export-Csv -Path "C:\Temp\$($groupInfo.Name).csv" -NoTypeInformation
}

_{Thursday, November 14, 2013 7:34 PM}

The output file has no information i see some weird characters !!! if OP needs some readable content then I should try other way :)

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 7:35 PM}

I'd probably do it like this:

Import-Csv C:\Temp\Book1.csv |
Group-Object -Property PollDevice |
ForEach-Object {
    $groupInfo = $_
    
    $groupInfo.Group |
    Export-Csv -Path "C:\Temp\$($groupInfo.Name).csv" -NoTypeInformation
}

You did it !!! :)

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 7:56 PM}

Hi Chen,

I am getting the following message when I run this

Out-File : The specified path, file name, or both are too long. The fully qualified file
name must be less than 260 characters, and the directory name must be less than 248 cha
racters.
At line:4 char:25

$_.PollDevice | out-file <<<< -FilePath "C:\MOMS\Name" -Force
    + CategoryInfo          : OpenError: (:) [Out-File], PathTooLongException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileComm
   and

I checked the PollDevice column and the largest character length is 60. I have inlcuded one such value.

Orlando.US 192 - Western Beltway.00.Storage Array 2 Assy

Any thought?

Thanks for your help

_{Thursday, November 14, 2013 7:57 PM}

What is the format of your PollDevice content? It's a string issue we can fix it quickly just copy paste your polldevice value

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 8:00 PM}

I tested this and it worked Orlando.US 192 - Western Beltway.00.Storage Array 2 Assy.csv

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 8:02 PM}

I tested this and it worked Orlando.US 192 - Western Beltway.00.Storage Array 2 Assy.csv

Regards Chen V [MCTS SharePoint 2010]

This is the Output I got

@{Device=10; PollDevice=Orlando.US 192 - Western Beltway.00.Storage Array 2 Assy}.csv

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 8:03 PM}

David's code is pretty neat and clear

I got the file name as

Orlando.US 192 - Western Beltway.00.Storage Array 2 Assy.csv

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 8:07 PM}

I used this as test input:

PollDevice,Device
SomeWhereElse.US 192 - Western Beltway.00.Storage Array 2 Assy,1
Orlando.US 192 - Western Beltway.00.Storage Array 2 Assy,2
MIL.US 192 - Western Beltway.00.Storage Array 2 Assy,3

The code I posted above created the files with no issues.

Don't retire TechNet! - (Maybe there's still a chance for hope, over 12,300+ strong and growing)

_{Thursday, November 14, 2013 8:38 PM}

I have a few device names which have / in the Poll Device Column.

Orlando.US 17/92 Off.01.VES

Out-File : Could not find a part of the path 'C:\CADATA\MOMS\Orlando.US 17\92 Off.01.VES J-Box Assy.csv'.
At line:6 char:13
+     out-file <<<< -FilePath "C:\CADATA\MOMS\BOMName.csv" -Force
    + CategoryInfo          : OpenError: (:) [Out-File], DirectoryNotFoundException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommandI get the following message, Can we fix this.

_{Thursday, November 14, 2013 8:49 PM}

In that case, try this:

$MOMSFile = Import-Csv '.\BOM.csv'

foreach ($Name in $MOMSFile) {

    $BOMName=$Name.PollDevice.Replace('/','-')
    out-file -FilePath "$BOMName.csv" -Force

}

This will replace any / characters with - instead.

Don't retire TechNet! - (Maybe there's still a chance for hope, over 12,300+ strong and growing)

_{Thursday, November 14, 2013 9:03 PM}

This worked for me

Alternatively if I have / it considered as white space and worked.

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 9:11 PM}

Hi Mike,

That worked like a charm. I have the various files created based on my device name. However when I open the files it is empty with only these characters. Is there a way, I can upload the csv file (BOM.csv'
) for review, I don't see an option to attach it.

The following shows up

ÿþ

_{Thursday, November 14, 2013 9:16 PM}

$MOMSFile = Import-Csv '.\BOM.csv'

foreach ($Name in $MOMSFile) {

    $BOMName=$Name.PollDevice.Replace('/','-')
    out-file -FilePath "$BOMName.csv" -Force -Encoding ascii

}

-Encoding ascii

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 9:18 PM}

Try this

Import-Csv C:\Temp\Book1.csv |
Group-Object -Property PollDevice |
ForEach-Object {
    $groupInfo = $_
    
    $groupInfo.Group |
    Export-Csv -Path "C:\Temp\$($groupInfo.Name).csv" -NoTypeInformation
}

Change the path as required please

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 9:24 PM}

Mike, Thanks :)

@PowerShellNoVice , Please use Export-CSV not Out-File it will throw weird character as Output, I already mentioned it in this thread!!!

Read this!!!

The output file has no information i see some weird characters !!! if OP needs some readable content then I should try other way :)

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 9:34 PM}

Hi Mike,

How can I add data to the files which are created. I need to have all the columns with data for each of the files which are created.

Thanks

_{Thursday, November 14, 2013 9:35 PM}

I expected this and working on it now !!!

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 9:36 PM}

Thank Chen, I appreciate your help and time,

Regards,

_{Thursday, November 14, 2013 9:37 PM}

Try this please

Import-Csv C:\Temp\Book1.csv |
Group-Object -Property PollDevice , Device |
ForEach-Object {
    $groupInfo = $_
    
    $groupInfo.Group |
    Export-Csv -Path "C:\Temp\$($groupInfo.Name).csv" -NoTypeInformation
}

Instead of device add your column names please

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 9:38 PM}

Group-Object -Property PollDevice , Device

Here you add your required column names from your CSV

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 9:51 PM}

Hi Chen,

I updated the script as below. I need the csv's created based on the PollDevice

Group-Object -Property PollDevice, ZOID, Monitored Resource, Value, Status, Poll Date, Message.ZPOLLING_HOST, Value_1, Status_1, Poll Date_1, Message_1 |

ForEach-Object {
    $groupInfo = $_
    $groupInfo.Group |
    Export-Csv -Path "C:\CADATA\MOMS\($groupInfo.Name).csv" -NoTypeInformation

However I am seeing this error.

roup-Object : A positional parameter cannot be found that accepts argument 'System.Object[]'.
At line:11 char:13

Group-Object <<<< -Property PollDevice, ZOID, Monitored Resource, Value, Status, Poll Date, Message.ZPOLLING_HOST, Value_1, Status_1, Poll Date_1, Message_1 |
+ CategoryInfo : InvalidArgument: (:) [Group-Object], ParameterBindingException
+ FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.GroupObjectCommand

_{Thursday, November 14, 2013 9:58 PM}

Okay, no problem. Please copy paste your code I will try to fix

Mike, as jrv said no bull whip for playing with Strings !!! I will try with object this time :)

Regards Chen V [MCTS SharePoint 2010]

_{Thursday, November 14, 2013 10:00 PM}

$MOMSFile = Import-Csv 'C:\CADATA\MOMS\BOM1.csv'Group-Object -Property PollDevice, ZOID, Monitored Resource, Value, Status, Poll Date, Message.ZPOLLING_HOST, Value_1, Status_1, Poll Date_1, Message_1 |ForEach-Object {    $groupInfo = $_    $groupInfo.Group |    Export-Csv -Path "C:\CADATA\MOMS\$($groupInfo.Name).csv" -NoTypeInformation}

_{Friday, November 15, 2013 12:14 AM}

Mike, as jrv said no bull whip for playing with Strings !!! I will try with object this time :)

He's right, objects are the best way to go. Sometimes string manipulation is necessary (and is always fun), but not very often.

I totally missed the requirement of putting data into the files, my bad.

Don't retire TechNet! - (Maybe there's still a chance for hope, over 12,300+ strong and growing)

_{Friday, November 15, 2013 2:55 PM}

Thanks Chen, I appreciate your help and support on this,

_{Friday, November 15, 2013 3:09 PM}

I am happy that it worked :)

Regards Chen V [MCTS SharePoint 2010]

_{Monday, November 18, 2013 8:31 PM}

Hi David,

I am running into a new issue when I run the code. It creates a single .csv file and not the individual files based on the group.

$invalid = New-Object System.Text.StringBuilder

[System.IO.Path]::GetInvalidFileNameChars() |
ForEach-Object {
$null = $invalid.Append('\x{0:X2}' -f [int]$_)
}

$invalid = "[$invalid]"
Import-Csv 'D:\CADATA\BOMItemStatusCheck\BOM-en-us.csv'| Group-Object -Property PollDevice|
ForEach-Object {
    $groupInfo = $_

    $fileName = $groupInfo.Name -replace $invalid, '-'

$groupInfo.Group |
Export-Csv "D:\CADATA\BOMItemStatusCheck\fileName.csv" -NoTypeInformation
}

$invalid = New-Object System.Text.StringBuilder[System.IO.Path]::GetInvalidFileNameChars() |ForEach-Object {    $null = $invalid.Append('\x{0:X2}' -f [int]$_)}$invalid = "[$invalid]"Import-Csv 'D:\CADATA\BOMItemStatusCheck\BOM-en-us.csv'| Group-Object -Property PollDevice|ForEach-Object {    $groupInfo = $_        $fileName = $groupInfo.Name -replace $invalid, '-'    $groupInfo.Group |     Export-Csv -Path "D:\CADATA\BOMItemStatusCheck\$fileName.csv" -NoTypeInformation}

_{Monday, November 18, 2013 8:44 PM}

The code still looks fine. I'd have to see the actual contents of the CSV file to troubleshoot any further.

_{Monday, November 18, 2013 8:48 PM}

Change this code and test

Export-Csv -Path "C:\CADATA\BOMItemStatusCheck\$($filename.csv).csv"

Regards Chen V [MCTS SharePoint 2010]

_{Monday, November 18, 2013 8:55 PM}

$filename is a string, and has no "csv" property. The original code is exactly what I intended; appending ".csv" to the $filename variable.

_{Monday, November 18, 2013 9:00 PM}

$filename is a string, and has no "csv" property. The original code is exactly what I intended; appending ".csv" to the $filename variable.

Indeed you are correct both the way it worked for me !!!

Regards Chen V [MCTS SharePoint 2010]

_{Monday, November 18, 2013 9:22 PM}

Hi David,

Thanks for your prompt response. I don't see an option to attach a csv file. This is how the file looks like.

PollDevice

Miami.Bird Road Mainline SB B.60.RCU Assy

Orlando.Kissimmee Park Rd East.00.DVN5000 1 Assy

Miami.Bird Road Mainline SB B.60.TIP Assy

_{Tuesday, November 19, 2013 12:35 AM}

That doesn't look like a CSV file... is it tab-delimited, fixed-width, or what?

_{Tuesday, November 19, 2013 6:19 PM}

Hi,

It is tab delimited

PollDevice Monitored Resource Value Status Poll Date Message Polling Host Value Status Poll Date Message
Miami.Bird Road Mainline SB B.60.RCU Assy Miami.Bird Road Mainline SB B.60.IRU Cabinet Assy 1 5 11/18/2013 08:40 T_S_R PC1400050 The IRU/Cabinet Rack is an OVERTEMPERATURE condition 10.113.160.101 1 5 11/18/2013 08:46 T_S_R PC1400050 The IRU/Cabinet Rack is an OVERTEMPERATURE condition

I am trying to convert this to csv using this piece of code

$path = "C:\CADATA\BOM-en-us.csv"
Get-Content -path $path |
ForEach-Object {$_ -replace " ","," } |
Out-File -filepath "C:\CADATA\BOM.csv"

The new csv file gets generated, however I get this error when I try to run the original script.

Import-Csv : The member "Value" is already present.
At line:2 char:11

Import-Csv <<<< 'D:\CADATA\BOMItemStatusCheck\BOM.csv'| Group-Object -Property PollDevice|
+ CategoryInfo : NotSpecified: (:) [Import-Csv], ExtendedTypeSystemException
+ FullyQualifiedErrorId : AlreadyPresentPSMemberInfoInternalCollectionAdd,Microsoft.PowerShell.Commands.ImportCsvCommand

Share via

Dynamic Output File

Question

All replies (45)

Additional resources