locked
Powershell, .csv to .txt with custom format RRS feed

  • Question

  • Hello, 

    I am working on a short snippet for converting a .csv to .txt (flat file) with a custom width on the individual columns.
    I need the output to be 1 string on each line, so column 1 = char0 - char4, column2 = char4 - char6 etc: 
    example: 111122222233
                   444455555566

    I currently got this:

    $csvPath = '..\test.csv'
    $outputPath = '..\test11.txt'
    
    # Import Csv $Content = Import-Csv -Path $csvPath # Format for the new table-file $a = @{Expression={$_.heading1};Label="Name";width=25}, ` @{Expression={$_.heading2};Label="ID";width=15}, ` @{Expression={$_.heading3};Label="Title";width=40}
    # Clear array_list $array_list = @() # Loop through rows in $Content and add each column to $array_list foreach($row in $Content){ $array_list += -join($row.heading1, $row.heading2, $row.heading3) } $array_list | Format-Table $a -Wrap -HideTableHeaders | Out-File -FilePath $outputPath -Width ([System.Int32]::MaxValue)


    My output in the test.txt is unfortunately not correct: 

    rad1-111111111111111111111111rad1-2rad1-3
    rad2-1rad2-2rad2-3

    I see that my issue is that when fetching $row.headin2 powershell automatically trims the string for the end-characters, which i need to keep (as whatever comes from the table column should be kept).

    Is there any way to prevent this auto-trim of my string? 

    I also see that when fetching $row.heading1 I do not validate the string-length properly, and it looks like i need to validate each field invidually and trim all characters after X (?). 

    PS:
    I also tried this, which i initially thought would be an easier approach: 

    Import-Csv -Path $csvPath | Format-Table $a -HideTableHeaders | Out-File -FilePath $outputPath -NoNewline -Width ([System.Int32]::MaxValue) 
    This imports the csv into a table with the correct columns etc, but there it looks like there is no parameter in Out-File for -NoSpace (it only has -NoNewLine), which gives me a space between the column-values (and i need them to be 1 long string). 
    Since i have 'lots' of linespace all over the place i cant just do a simple replace, but shouldn't it be possible to not have this get auto-added?

    Appreciate any feedback on this :)

    Monday, November 5, 2018 3:16 PM

All replies

  • Actually Powershell does a lot of things implicitly for you. So the simplest way to bring a CSV file to a flat text file would be this:

    Import-Csv -Path $csvPath | Out-File -FilePath $outputPath
    If you like to have each single cell formated in a special way you could use the -f operator either in some calculated properties or in a custom object.


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Monday, November 5, 2018 4:01 PM
  • I'd agree with BOfH-666 except for that need to handle wider-than-expected data (although you did say that "whatever comes from the table column should be kept").

    You can use the substring method to keep the data in each column to a no-longer-than-length-X, and the -format operator to put the stuff into the column widths you need in the output file. Having a sample of your data would have helped.

    I didn't bother putting headers into the output file, but that would be trivial.

    Does this look like what you need?

    $csvPath = 'c:\test\test.csv'
    $outputPath = 'c:\test\test11.txt'
    
    Import-Csv -Path $csvPath | foreach {
        $f1 = if ($_.heading1.length -gt 4) {$_.heading1.Substring(0,4)} else {$_.heading1}
        $f2 = if ($_.heading2.length -gt 3) {$_.heading2.substring(0,3)} else {$_.heading2}
        $f3 = if ($_.heading3.length -gt 2) {$_.heading3.substring(0,2)} else {$_.heading3}
        "{0,25}{1,15}{2,40}" -f $f1,$f2,$f3   # adjust column widths and justification as needed
    
    } | Out-File $outputPath

    Here's a sample input file:

    heading1,heading2,heading3
    1111,222,33
    4444,555,66
    7777777,8888888,999

    And the output file:
                         1111            222                                      33
                         4444            555                                      66
                         7777            888                                      99


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Monday, November 5, 2018 7:50 PM
  • # Test Data
    $csv = @'
    Name,ID
    John Doe,12345678
    This name is too long,12345
    '@
    
    # Option 1
    $csv | ConvertFrom-Csv | foreach {
        $values = @(
            [string]::new($_.Name[0..14])
            [int]$_.ID
        )
        '{0,-15}{1:D8}' -f $values
    }
    
    # Option 2
    $properties = @{Expression={$_.Name};Label="Name";Width=15},
                  @{Expression={$_.ID.PadLeft(8)};Label="ID";Width=8}
    
    $csv | ConvertFrom-Csv | Format-Table $properties -HideTableHeaders
    

    Monday, November 5, 2018 8:25 PM
  • Just use the format operator with the column width you want,

    Import-Csv <file> |
        ForEach-Object{
            '{0,5}{1,10}{3,12}' -f $_.Col1,$_.Col2,$_.col3
        } |
       Out-File <file>

    Where:  {<colnum,width>}

    If width is negative then the column is right justified.


    \_(ツ)_/

    Monday, November 5, 2018 9:57 PM
  • Hey Guys,

    Thanks for all your comments/feedback! 
    This is the first time i' working with powershell so i had a lot to figure out. 

    This is what i ended up with: 
    # Fetch variables from app.config
    [xml] $xml = [xml] (Get-Content "C:\Users\Eivind\Desktop\Powershell test\App.config")
    [System.Xml.XmlElement] $root = $null
    [System.Xml.XmlElement] $cVariables = $null
    [System.Xml.XmlElement] $cVariable = $null
    [System.Xml.XmlElement] $appSettings = $null
    
    [System.Xml.XmlElement] $root = $xml.get_DocumentElement()
    [System.Xml.XmlElement] $appSettings = $root.appSettings
    [System.Xml.XmlElement] $cVariables = $root.columns
    
    # Get app-settings
    $aS = @{}
    foreach($appSetting in $appSettings.ChildNodes)
    {
        [string] $key = $appSetting.key
        [string] $value = $appSetting.value
        $aS.Add($key, $value)
    }
    
    # get column-settings
    $cArray = [ordered]@{}
    foreach($cVariable in $cVariables.ChildNodes)
    {
        [string] $key = $cVariable.key
        [string] $value = $cVariable.value
        $cArray.Add($key, $value)
    }
    
    # Get Input and Output-filename
    $InputFile = $aS.Item("InputFile")
    $OutputFile = $aS.Item("OutputFile")
    
    # Set InputPath and OutputPath
    $InputPath = Join-Path $PSScriptRoot $InputFile
    $outputPath = Join-Path $PSScriptRoot $OutputFile
    $Content = Import-Csv -Path $InputPath
    
    # Clear array_list
    $array_list = @() 
    
    #Loop through rows in $Content, get all values and insert it into $array_list with predefined column widths
    foreach($row in $Content){
        $column_list = @()
        foreach($key in $cArray.keys){
           if($row.$key.Length -gt ""){
                if($row.$key.Length -gt $cArray[$key]){
                    $column = -join($key + "=" + $row.$key.subString(0, $cArray[$key]) + ";")
                }
                elseif($row.$key.Length -lt $cArray[$key]) {
                    $columnW = $cArray[$key]
                    $column = -join($key + "=" + $row.$key.PadRight($columnW) + ";")
                }
                $column_list += -join($column)          
            }
            <#else
            {
                Write-Host $row.$key.Length
                $emptyColumn = ""
                $columnW = $cArray[$key]
                $column = -join($key + "=" + $emptyColumn.PadLeft($columnW )+ ";")
                $column_list += -join($column)            
            }#>
        }
        Write-Host $column_list
        $array_list += -join($column_list)
    }
    $array_list | Out-File -FilePath $outputPath -Width ([System.Int32]::MaxValue) 
    This is my InputFile.csv: 
    heading0,heading1,heading2,heading3,heading4,heading5
    rad1-000000000000000000000000000000000000000000000000000000000000,1,2,3,4,
    rad2-0000000000000000000000000000000000000000000000000000000,1,2,3,4,5

    This is my output.txt:
    heading0=rad1-00000000000000000000;heading1=1              ;heading2=2                                       ;heading3=3                                                 ;heading4=4     ;
    heading0=rad2-00000000000000000000;heading1=1              ;heading2=2                                       ;heading3=3                                                 ;heading4=4     ;heading5=5        ;

    This gives me the correct output, and i have more flexibility to edit the this as i wish. 
    Here i can also add more columns/fields in my app.config without having to edit the code (havent tested this that much, I just assume it works).

    This is almost the exact same output as I get with this: 
    Import-Csv -Path $csvPath | Format-Table $a -HideTableHeaders | Out-File -FilePath $outputPath -NoNewline -Width ([System.Int32]::MaxValue) 
    But without the extra " " between the columns/$keys . 

    /Eivind
    Wednesday, November 7, 2018 11:03 AM
  • That seems to have nothing to do with what you asked.

    You asked about converting a CSV and now it seems to have become an XML file.

    If this is an standard app.config file then what you are doing in 100+ lines can be done in about 10 or 15 lines.


    \_(ツ)_/

    Wednesday, November 7, 2018 11:21 AM
  • My output isn't XML, I just get the individual column-width-parameters from an app.config instead of having it inside the script. Is there any other/simpler/better way of keeping variables in an external file and load them dynamically? 
    The reason is that i eventually am going to have about 50 different columns with 50 individual widths, so it just seems messy to have everything inside my script. 

    I am 100% open to suggestions, I just had no luck using 'basic stuff' to get my wanted output. I am testing the suggestions you guys sent me now, and I agree, i can make some huge improvements.

    Appreciate the feedback! :)

    /E

    Wednesday, November 7, 2018 1:44 PM
  • Too many variables.  It is impossible to follow and read and most is unnecessary.

    Here is an example,

    # Fetch variables from app.config
    [xml]$xml = Get-Content C:\Users\Eivind\Desktop\Powershell test\App.config
    
    $aS = @{}
    foreach($appSetting in $appSettings.ChildNodes){
        $aS.Add($appSetting.key, $appSetting.value)
    }

    Don't write code tht does nothing just because you saw someone else do it.


    \_(ツ)_/


    • Edited by jrv Wednesday, November 7, 2018 1:49 PM
    Wednesday, November 7, 2018 1:49 PM
  • Why is this any better than the examples given to you.  It is just a lot of code that does very little.

    A CSV can be converted as many have shown you.  No need for all of this.  If you want to set widths then use variables in the format template.

    This can use variables for widths.

         '{0,5}{1,10}{3,12}' -f $_.Col1,$_.Col2,$_.col3

    Like this:

         "{0,$w1}{1,$w2}{3$w3}'"-f $_.Col1,$_.Col2,$_.col3

    where $w1..$w3 are integer variables.  Where you get these variables from is unimportant.  I recommend just using a text file with column name and width.

    Name,Width
    col1,10
    col2,15

    etc.




    \_(ツ)_/

    Wednesday, November 7, 2018 1:56 PM
  • True, I need to go through the code and clean it up. The reason i do it step-by-step is that i needed to learn/know each step is successful. I realize this makes it horrible to read/understand for anyone besides myself, ill keep that in mind.

    I get your point, i can skip putting appSettings.ChildNodes into an Array, and just get them with e.g $appSettings.FirstChild.Value directly instead. 

    Good feedback, thanks :)

    /E
     
    Wednesday, November 7, 2018 2:03 PM
  • Read the following.  It will give you some basic and standard parameters for coding that will help you to approach things les chaotically and make your coding easier.

    PowerShell Style Guidelines


    \_(ツ)_/

    Wednesday, November 7, 2018 2:08 PM
  • Also note that the formatter can crate any type of line you want.

    "Col1={0,$w1};Col2={1,$w2};Col3={3$w3}'"-f $_.Col1,$_.Col2,$_.col3

    Just embed the headers into the template.

    You can even read the properties of the CSV and generate the template from the properties.

    I have used this method for more than two decades to prepare odd formats for upload to a mainframe or to generate special records for EDT,


    \_(ツ)_/

    Wednesday, November 7, 2018 2:12 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Friday, November 9, 2018 9:18 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Tuesday, November 27, 2018 2:06 AM