Asked by:
Including blank field during Csv Export from text file

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 .00Output 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 .00My 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