Split CSV file by column number using Powershell
0
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.
xml powershell
edited Dec 31 '18 at 16:25
James C.
8,78822131
asked Dec 31 '18 at 14:57
AVPAVP
112
add a comment |
0
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.
xml powershell
edited Dec 31 '18 at 16:25
James C.
8,78822131
asked Dec 31 '18 at 14:57
AVPAVP
112
add a comment |
0
0
0
1
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.
xml powershell
edited Dec 31 '18 at 16:25
James C.
8,78822131
asked Dec 31 '18 at 14:57
AVPAVP
112
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.
xml powershell
xml powershell
edited Dec 31 '18 at 16:25
James C.
8,78822131
asked Dec 31 '18 at 14:57
AVPAVP
112
edited Dec 31 '18 at 16:25
James C.
8,78822131
asked Dec 31 '18 at 14:57
AVPAVP
112
edited Dec 31 '18 at 16:25
James C.
8,78822131
edited Dec 31 '18 at 16:25
James C.
8,78822131
edited Dec 31 '18 at 16:25
James C.
8,78822131
8,78822131
asked Dec 31 '18 at 14:57
AVPAVP
112
asked Dec 31 '18 at 14:57
AVPAVP
112
asked Dec 31 '18 at 14:57
AVPAVP
112
112
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
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:CsvData20181231SO_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
answered Dec 31 '18 at 15:32
LotPingsLotPings
19k61532
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53988782%2fsplit-csv-file-by-column-number-using-powershell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
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:CsvData20181231SO_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
answered Dec 31 '18 at 15:32
LotPingsLotPings
19k61532
add a comment |
1
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:CsvData20181231SO_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
answered Dec 31 '18 at 15:32
LotPingsLotPings
19k61532
add a comment |
1
1
1
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:CsvData20181231SO_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
answered Dec 31 '18 at 15:32
LotPingsLotPings
19k61532
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:CsvData20181231SO_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
answered Dec 31 '18 at 15:32
LotPingsLotPings
19k61532
edited Dec 31 '18 at 15:40
answered Dec 31 '18 at 15:32
LotPingsLotPings
19k61532
answered Dec 31 '18 at 15:32
LotPingsLotPings
19k61532
answered Dec 31 '18 at 15:32
LotPingsLotPings
19k61532
19k61532
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53988782%2fsplit-csv-file-by-column-number-using-powershell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
