So I have a CSV file with rows that I want to transpose (some of them) into columns using PowerShell The example is as follows
ALPHA
CD
CL
CM
-5
0.1
-0.2
0.05
0
0.4
0.4
-0.08
5
0.5
0.8
-0.1
What I want is something like this
Alpha CD CL CM
-5 0.1 -0.2 0.05
0 0.4 0.4 -0.08
5 0.5 0.8 -0.1
For reference I got these values from a .dat file output with over 400 rows worth of information, I turned it into a csv file using out-file, and I skipped all the rows I don't need. The information was split into rows but not columns, meaning 'ALPHA CD CL CM' were all in one cell with spaces in between, so I used split command, specifically
```
$ text .split () | where { $ _ }
```
to break them into rows now I want to transpose SOME of them back into columns.
Problem is it's not fixed amounts, meaning it's not always 4 rows into 4 columns, sometimes I would get 5 rows that I want to turn into 5 columns, and THEN turn every 4 rows into 4 columns AFTER that.
Sorry if I'm rambling but it's something like this
Row 1 > Column 1 Row 1
Row 2 > Column 2 Row 1
Row 3 > Column 3 Row 1
Row 4 > Column 4 Row 1
Row 5 > Column 5 Row 1
Row 6 > Column 1 Row 2
Row 7 > Column 2 Row 2
Row 8 > Column 3 Row 2
Row 9 > Column 4 Row 2
Row 10 > Column 5 Row 2
Row 11 > Column 1 Row 3
Row 12 > Column 2 Row 3
Row 13 > Column 3 Row 3
Row 14 > Column 4 Row 3
Row 15 > Column 1 Row 4
Notice how it went from 5 columns to 4 columns now. So yes, highly appreciate it if anyone can help me with this. If it can be done easier in other methods other than PowerShell where I can use PowerShell to run them, i.e a bat file that I can tell PowerShell to run, that would be good by me as I need to automate a very long process, and this one of the later stages.
P.S. These data are NOT comma separated cleanly, the program I am using "DATCOM" outputs a dat file where it looks neat and structured in text format, but when you export csv it destroys it, so it has to be done using
```
out-file name csv
```
P.S.S There is no clear delimiter/cutoff point, and there are no repeating numbers or anything else that can be used as a hint. I have to do it by row number, which I know due to dealing with DATCOM before.
Thank You
I explained more above, but I tried using split commands but it dropped them all into rows, so if there is a way that can just do a literal text to columns delimit using spaces (exactly like in excel) that would be perfect, and even better than breaking them into rows then transposing to columns. However, it has to be EXACTLY like excel. The problem is there are 4-8 "spaces" between each value, so if I try to
import-csv -delim " "
on the file I get something like "Alpha H1 H2 H3 CD H4 H5 H6 H7 H8 CL" and everything else gets destroyed, where as if I actually open Excel, highlight cells, text to columns> delimited> check "spaces" the results are perfect.
Lastly, sorry for any bad formatting, it identified some thing as code when they weren't and it forced me to get creative.
Here are the files: https://easyupload.io/m/6q70ei
for006.dat is the .dat file generated by DATCOM Output1 is what I want done as described below (row to column) Output2 is what I hope I can do later, i.e delete a column and a row to make it cleaner, this is my ideal final output.
Thanks again
CodePudding user response：
A batch file processed by the Windows Command Processor cmd.exe for this task would be:
@echo off
setlocal EnableExtensions DisableDelayedExpansion
set "ColumnData1="
set "ColumnData2="
set "ColumnData3="
set "ColumnData4="
if exist "input.txt" (for /F usebackq^ delims^=^ eol^= %%I in ("input.txt") do (
if not defined ColumnData1 (
set "ColumnData1=%%I"
) else if not defined ColumnData2 (
set "ColumnData2=%%I"
) else if not defined ColumnData3 (
set "ColumnData3=%%I"
) else (
set "ColumnData4=%%I"
setlocal EnableDelayedExpansion
echo !ColumnData1!,!ColumnData2!,!ColumnData3!,!ColumnData4!
endlocal
set "ColumnData1="
set "ColumnData2="
set "ColumnData3="
set "ColumnData4="
)
))>"output.csv"
endlocal
Please note that FOR ignores always empty lines. The batch file does not work for that reason if lines in file input.txt are empty because of an empty value in original CSV file.
There can be used also horizontal tab characters instead of commas as separators (or semicolons or vertical bars which must be escaped with ^ to be interpreted literally) on the ECHO command line which outputs the values in CSV format.
To understand the commands used and how they work, open a command prompt window, execute there the following commands, and read the displayed help pages for each command, entirely and carefully.
echo /?endlocal /?for /?if /?set /?setlocal /?
CodePudding user response：
Try following :
$columns = 4
$data =
"@ALPHA
CD
CL
CM
-5
0.1
-0.2
0.05
0
0.4
0.4
-0.08
5
0.5
0.8
-0.1@"
$data | Format-Table
$headers = [System.Collections.ArrayList]::new()
$table = [System.Collections.ArrayList]::new()
$rows = [System.IO.StringReader]::new($data)
for($i = 0; $i -lt $columns; $i )
{
$headers.Add($rows.ReadLine())
}
$rowCount = 0
Write-Host $headers
While(($line = $rows.ReadLine()) -ne $null)
{
if($rowCount % $columns -eq 0)
{
$newRow = New-Object -TypeName psobject
$table.Add($newRow)
}
$newRow | Add-Member -NotePropertyName $headers[$rowCount % $columns] -NotePropertyValue $line
$rowCount
}
$table | Format-Table
CodePudding user response：
You can use PowerShell's Begin/Process/End lifecycle to "buffer" input data until you have enough for a "row", then output that and start collecting for the next row:
# define width of each row as well as the column separator
$columnCount = 5
$delimiter = "`t"
# read in the file contents, "cell-by-cell"
$rawCSVData = Get-Content path\to\input\file.txt |ForEach-Object -Begin {
# set up a buffer to hold 1 row at a time
$index = 0
$buffer = [psobject[]]::new($columnCount)
} -Process {
# add input to buffer, and optionally output
$buffer[$index ] = $_
if($index -eq $columnCount){
# output row, reset column index
$buffer -join $delimiter
$index = 0
}
} -End {
# Output any partial last row
if($index){
$buffer -join $delimiter
}
}
This will produce a list of strings that can either be written to disk or parsed using regular CSV-parsing tools in PowerShell:
$rawCSVData |Set-Content path\to\output.csv
# or
$rawCSVData |ConvertFrom-Csv -Delimiter $delimiter
CodePudding user response：
Once you know how many rows form the headers and the data, you can convert the file into
an array of objects by using ConvertFrom-Csv.
When done, it is easy to create a new csv from this as below:
# in this example the first 4 lines are the columns, the rest is data
# other files may need a different number of columns
$columns = 4
$data = @(Get-Content -Path 'X:\Somewhere\data.txt')
$count = 0
$result = while ($count -lt ($data.Count - ($columns - 1))) {
$data[$count..($count $columns - 1)] -join "`t" # join the lines with a TAB
$count = $columns
}
$result = $result | ConvertFrom-Csv -Delimiter "`t"
# output on screen
$result | Format-Table -AutoSize
# write to new csv file
$result | Export-Csv -Path 'X:\Somewhere\data_new.csv' -NoTypeInformation
Output on screen:
ALPHA CD CL CM
----- -- -- --
-5 0.1 -0.2 0.05
0 0.4 0.4 -0.08
5 0.5 0.8 -0.1