Can I convert a row of comma delimited values to a column-CodePudding

I have one row of temperature data in a text file that I would like to convert to a single column and save as a CSV file using a PowerShell script. The temperatures are separated by commas and look like this:

21,22,22,22,22,22,22,20,19,18,17,16,15,14,13,12,11,10,9,9,9,8,8,9,8,8,8,9,9,8,8,8,9,9,9,8,8,8,8,8,9,10,12,14,15,17,19,20,21,21,21,21,21,21,21,21,21,21,21,20,20,20,20,20,22,24,25,26,27,27,27,28,28,28,29,29,29,28,28,28,28,28,28,27,27,27,27,27,29,30,32,32,32,32,33,34,35,35,34,33,32,32,31,31,30,30,29,29,28,28,27,28,29,31,33,34,35,35,35,36,36,36,36,36,36,36,36,36,37,37,37,37,37,37,38,39,40,42,43,43,43,43,43,42,42,42,41,41,41,41,40,39,37,36,35,34,33,32,31,31,31,31,31,31,31,31,31,31,

I have tried several methods based on searches in this forum I thought this might work but it returns an error: Transpose rows to columns in PowerShell

This is the modified code I tried that returns: Error: "Input string was not in a correct format."

$txt = Get-Content 'C:myfile.txt' | Out-String
$txt -split '(?m)^,\r?\n' | ForEach-Object {
    # create empty array
    $row = @()

    $arr = $_ -split '\r?\n'
    $k = 0
    for ($n = 0; $n -lt $arr.Count; $n  = 2) {
        $i = [int]$arr[$n]
        # if index from record ($i) is greater than current index ($k) append
        # required number of empty fields
        for ($j = $k; $j -lt $i-1; $j  ) { $row  = $null }
        $row  = $arr[$n 1]
        $k = $i
    }

    $row -join '|'
}

This seems like it should be simple to do with only one row of data. Are there any suggestions on how to convert this single row of numbers to one column?

CodePudding user response：

Assuming I'm understanding your intent correctly, based on your verbal description (not your own coding attempt):

# Create simplified sample input file
@'
21,22,23,
'@ > myfile.txt

# Read the line, split it into tokens by ",", filter out empty elements,
# and write to an output CSV file with a column name prepended.
(Get-Content myfile.txt) -split ',' -ne '' |
  ForEach-Object -Begin { 'Temperatures' } -Process { $_ } |
  Set-Content out.csv

out.csv then contains:

Temperatures
21
22
23

CodePudding user response：

Try this:

# convert row to column data
$header = 'TEMPERATURE'
$values = $(Get-Content input.dat) -split ','
$header, $values | Out-File result.csv

#now test the result
Import-Csv result.csv

The header is the first line (or record) in the CSV file. In this case it's a single word, because there is only one column.

The values are the items between commas in the input. I haven't weeded out empty entries. In this case, the -split on commas generates an array of strings.

Then, we just write the header and the array out to a file. But what happened to all the commas? It turns out that, for a single column CSV file, there are no commas separating fields. So the result is a simple CSV file.

Last, there is a test of the output using Import-csv to read the result and display it in table format.

This isn't necessarily the best way to code it, but it might help a beginner get used to powershell.