Split CSV file by column number using Powershell

Question

I have a CSV file with around 2500 columns, without headers that split by pipeline | delimiter.

file.csv looks like this:

x,y,z,x1,x2,x3,x4,x5,x6,x7,x8,x9,...(about 2500 more)...,x2500
0,0,0,a1,a2,a3,a4,a5,a6,a7,a8,a9,...(about 2500 more)...,s2500
1,1,1,b1,b2,b3,b4,b5,b6,b7,b8,b9,...(about 2500 more)...,b2500
….

I want to split the this file into multiple files based on their column number. Using Bash I have used cut -d "|" -f1,2-901 and selected the columns I wanted to save in the new file.

Output:

file1.csv

Key1,x2,x3,x4,x5,x6,x7,x8,x9,...(about 900 more)...,x900  
Key2,a2,a3,a4,a5,a6,a7,a8,a9,...(about 900 more)...,a900  
Key3,b2,b3,b4,b5,b6,b7,b8,b9,...(about 900 more)...,b900 <BL>
…

file2.csv

Key1,x901,x902,x903,x904,...(about 900 more)...,x1800  
Key2,a901,a902,a904,a904,...(about 900 more)...,a1800  
Key3,b901,b902,b903,b904,...(about 900 more)...,b1800  
…

How do I do it in Powershell?

Any help would be greatly appreciated.

score 1 · Accepted Answer · 2018-12-31 15:40:37Z

Use Select-Object to only get wanted columns.

Build an array for the headers/properties

This script creates a csv with only 25 columns for demonstration

## Q:\CsvData\2018\12\31\SO_53988782.ps1
$file = '.\Data.csv'

#create sample csv with headers x1..x25
(1..25|ForEach-Object{"x{0}" -f $_}) -join ',' | set-content $file
(1..25|ForEach-Object{$_}) -join ',' | add-content $file

Get-Content $file

$Range1 = 1..9  | ForEach-Object{"x{0}" -f $_}
$Range2 = 10..19| ForEach-Object{"x{0}" -f $_}

$CsvData = Import-csv $file

$CsvData | Select-Object $Range1 | Format-Table -auto

$CsvData | Select-Object $Range2 | Format-Table -auto

> Get-Content $file
x1,x2,x3,x4,x5,x6,x7,x8,x9,x10,x11,x12,x13,x14,x15,x16,x17,x18,x19,x20,x21,x22,x23,x24,x25
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25

> $CsvData | Select-Object $Range1 | Format-Table -auto

x1 x2 x3 x4 x5 x6 x7 x8 x9
-- -- -- -- -- -- -- -- --
1  2  3  4  5  6  7  8  9

> $CsvData | Select-Object $Range2 | Format-Table -auto

x10 x11 x12 x13 x14 x15 x16 x17 x18 x19
--- --- --- --- --- --- --- --- --- ---
10  11  12  13  14  15  16  17  18  19

Collectives™ on Stack Overflow

Split CSV file by column number using Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related