none
Including blank field during Csv Export from text file RRS feed

  • Question

  • Hi,

    I'm currently working on a script to export the content of a .txt file to Csv format but the issue I am having is that, the empty field in the txt file is being ignored in Csv file. Hence, the data is sort of a mess. I would like to retain the blank cell in my output csv file. Is there a way I could twerk my code to do that? Below is how the content of the csv looks like.      

    Input in text file($path_out):     

                      Number      Debit Amount        Number   Credit Amount
     Amount                        1,816,208.71                        1,816,208.71
     Accum          874          1,816,208.71              23       1,816,208.71
     Difference                                   .00                                       .00

    Output in CSV file:

                      Number      Debit Amount        Number   Credit Amount
     Amount     1,816,208.71  1,816,208.71
     Accum       874             1,816,208.71              23       1,816,208.71
     Difference   .00                      .00

    My powershell code structure:

    $input = Get-Content $path_out
                   
     $data = $input[1..($input.Length - 1)]
     
    $maxLength = 0

    $objects = ForEach($record in $data) {
    $split = $record -split "\s{2,}|\t+"
    If($split.Length -gt $maxLength){
    $maxLength = $split.Length
                   }

    $props = @{}

    For($i=0; $i -lt $split.Length; $i++) {
     $props.Add([String]($i+1),$split[$i])
     }
      New-Object -TypeName PSObject -Property $props
                   }

     $headers = [String[]](1..$maxLength)

     $objects | 
     Select-Object $headers | 
     Export-Csv -NoTypeInformation -Path "X:\project\Monarch\out.csv"

    

     
    • Moved by Bill_Stewart Tuesday, November 7, 2017 9:39 PM Not answerable as written
    Tuesday, September 12, 2017 9:03 PM

All replies

  • You will have to write a custom parser for the text file.  HINT:  Use SubString to grab the fields.

    If the file is truly tab separated then your "Split" is wrong.

    $split = $record -split '\t'

    This will give an array element for each column even if the column is blank.

    Your object needs to be built correctly.

    $split = $record -split '\t'
    [pscustomobject]@{
    	Type  = $split[0]
    	Number1 = $split[1]
    	'Debt Amount' = $split[2]
    	Number2 = $split[3]
    	'Credit Amount' = $split[4]
    }

    Don't use a "for" loop.  Use a "ForEach-Object" on the file contents.


    \_(ツ)_/

    Tuesday, September 12, 2017 9:28 PM
  • Thanks Jrv, the text file is separated by space & tab, that was why I split the file with space and tab. Also, the text file contains large data with different data structures in the csv file, hence why I'm curious if there is a way to include a line of code in my script to align the data properly instead of writing a custom parser for each column.
    Tuesday, September 12, 2017 11:32 PM
  • If the file is not predictable then you will not be able to parse it.  If it is tab separated then my method works.  Only two possibilities.

    I have been converting and loading data from text files and text reports for over 20 years.  Without more and more accurate info there is no way to answer your question


    \_(ツ)_/

    Wednesday, September 13, 2017 12:18 AM