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++) {
      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'
    	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