Manipulating csv data in powershell 2
I am going nutty trying to understand my issue. Normally in Powershell I do an import-csv and I get a nice output that looks something like:
col1 : Mary.jane.doe
Col2 : John
Which I can then easily move around and do whatever I want really. But I have file that gives me:
col1 col2
---- ----
Mary.jane.doe John
I need to be able to do a split on the first column and put it into a variable so I just get "Mary", then put that into its own column so my output looks like:
col1 col2 col3
---- ---- -----
Mary Mary.jane.doe John
Thanks in advance for answers, also what would I need to really study in Powershell to understand how this is working, as this is the first time I have pulled my hair out with manipulating csv,txt files etc...
In Powershell? Most of the machines I work on have Powershell2.
powershell powershell-v2.0
edited Nov 15 '18 at 14:11
LotPings
18.1k61532
asked Nov 12 '18 at 22:25
jim woodjim wood
343
add a comment |
I am going nutty trying to understand my issue. Normally in Powershell I do an import-csv and I get a nice output that looks something like:
col1 : Mary.jane.doe
Col2 : John
Which I can then easily move around and do whatever I want really. But I have file that gives me:
col1 col2
---- ----
Mary.jane.doe John
I need to be able to do a split on the first column and put it into a variable so I just get "Mary", then put that into its own column so my output looks like:
col1 col2 col3
---- ---- -----
Mary Mary.jane.doe John
Thanks in advance for answers, also what would I need to really study in Powershell to understand how this is working, as this is the first time I have pulled my hair out with manipulating csv,txt files etc...
In Powershell? Most of the machines I work on have Powershell2.
powershell powershell-v2.0
edited Nov 15 '18 at 14:11
LotPings
18.1k61532
asked Nov 12 '18 at 22:25
jim woodjim wood
343
add a comment |
I am going nutty trying to understand my issue. Normally in Powershell I do an import-csv and I get a nice output that looks something like:
col1 : Mary.jane.doe
Col2 : John
Which I can then easily move around and do whatever I want really. But I have file that gives me:
col1 col2
---- ----
Mary.jane.doe John
I need to be able to do a split on the first column and put it into a variable so I just get "Mary", then put that into its own column so my output looks like:
col1 col2 col3
---- ---- -----
Mary Mary.jane.doe John
Thanks in advance for answers, also what would I need to really study in Powershell to understand how this is working, as this is the first time I have pulled my hair out with manipulating csv,txt files etc...
In Powershell? Most of the machines I work on have Powershell2.
powershell powershell-v2.0
edited Nov 15 '18 at 14:11
LotPings
18.1k61532
asked Nov 12 '18 at 22:25
jim woodjim wood
343
I am going nutty trying to understand my issue. Normally in Powershell I do an import-csv and I get a nice output that looks something like:
col1 : Mary.jane.doe
Col2 : John
Which I can then easily move around and do whatever I want really. But I have file that gives me:
col1 col2
---- ----
Mary.jane.doe John
I need to be able to do a split on the first column and put it into a variable so I just get "Mary", then put that into its own column so my output looks like:
col1 col2 col3
---- ---- -----
Mary Mary.jane.doe John
Thanks in advance for answers, also what would I need to really study in Powershell to understand how this is working, as this is the first time I have pulled my hair out with manipulating csv,txt files etc...
In Powershell? Most of the machines I work on have Powershell2.
powershell powershell-v2.0
powershell powershell-v2.0
edited Nov 15 '18 at 14:11
LotPings
18.1k61532
asked Nov 12 '18 at 22:25
jim woodjim wood
343
edited Nov 15 '18 at 14:11
LotPings
18.1k61532
asked Nov 12 '18 at 22:25
jim woodjim wood
343
edited Nov 15 '18 at 14:11
LotPings
18.1k61532
edited Nov 15 '18 at 14:11
LotPings
18.1k61532
edited Nov 15 '18 at 14:11
LotPings
18.1k61532
18.1k61532
asked Nov 12 '18 at 22:25
jim woodjim wood
343
asked Nov 12 '18 at 22:25
jim woodjim wood
343
asked Nov 12 '18 at 22:25
jim woodjim wood
343
343
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Well, to start with, you need to update your OS since PowerShell 2.0 is only default on versions of Windows that are out of support now I'm pretty sure. That aside, this can be done fairly simply a few ways. Since you are going to be changing the column headers for each column (per your example), I would pipe the CSV data through a ForEach-Object loop, and create a new object based off each existing object, and output that.
$MyCSV = Import-Csv C:PathToFile.csv
$NewCSV = $MyCSV | ForEach-Object {
New-Object PSCustomObject -Property @{
'Col1' = $_.Col1.Split('.')[0]
'Col2' = $_.Col1
'Col3' = $_.Col2
}
} | Select-Object col1, col2, col3
Editor's note: PSv2 didn't support defining a custom object's properties in order, which is why the Select-Object call is needed to ensure the desired property enumeration order. In PSv3+ you can create custom objects as [pscustomobject] @{ ... }, which does respect the property-definition order.
This iterates the CSV, and for each record makes a new record with your desired properties. Like I said, there's a few ways to accomplish this, but I think this would be the simplest considering your needs.
edited Nov 15 '18 at 2:55
mklement0
127k20241269
answered Nov 12 '18 at 22:58
TheMadTechnicianTheMadTechnician
25.1k21631
Glad to hear it, @TheMadTechnician; I appreciate the nice feedback, and also appreciate the quality of your answers here.
– mklement0
Nov 15 '18 at 18:50
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
});
}
});
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%2f53270986%2fmanipulating-csv-data-in-powershell-2%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
Well, to start with, you need to update your OS since PowerShell 2.0 is only default on versions of Windows that are out of support now I'm pretty sure. That aside, this can be done fairly simply a few ways. Since you are going to be changing the column headers for each column (per your example), I would pipe the CSV data through a ForEach-Object loop, and create a new object based off each existing object, and output that.
$MyCSV = Import-Csv C:PathToFile.csv
$NewCSV = $MyCSV | ForEach-Object {
New-Object PSCustomObject -Property @{
'Col1' = $_.Col1.Split('.')[0]
'Col2' = $_.Col1
'Col3' = $_.Col2
}
} | Select-Object col1, col2, col3
Editor's note: PSv2 didn't support defining a custom object's properties in order, which is why the Select-Object call is needed to ensure the desired property enumeration order. In PSv3+ you can create custom objects as [pscustomobject] @{ ... }, which does respect the property-definition order.
This iterates the CSV, and for each record makes a new record with your desired properties. Like I said, there's a few ways to accomplish this, but I think this would be the simplest considering your needs.
edited Nov 15 '18 at 2:55
mklement0
127k20241269
answered Nov 12 '18 at 22:58
TheMadTechnicianTheMadTechnician
25.1k21631
Glad to hear it, @TheMadTechnician; I appreciate the nice feedback, and also appreciate the quality of your answers here.
– mklement0
Nov 15 '18 at 18:50
add a comment |
Well, to start with, you need to update your OS since PowerShell 2.0 is only default on versions of Windows that are out of support now I'm pretty sure. That aside, this can be done fairly simply a few ways. Since you are going to be changing the column headers for each column (per your example), I would pipe the CSV data through a ForEach-Object loop, and create a new object based off each existing object, and output that.
$MyCSV = Import-Csv C:PathToFile.csv
$NewCSV = $MyCSV | ForEach-Object {
New-Object PSCustomObject -Property @{
'Col1' = $_.Col1.Split('.')[0]
'Col2' = $_.Col1
'Col3' = $_.Col2
}
} | Select-Object col1, col2, col3
Editor's note: PSv2 didn't support defining a custom object's properties in order, which is why the Select-Object call is needed to ensure the desired property enumeration order. In PSv3+ you can create custom objects as [pscustomobject] @{ ... }, which does respect the property-definition order.
This iterates the CSV, and for each record makes a new record with your desired properties. Like I said, there's a few ways to accomplish this, but I think this would be the simplest considering your needs.
edited Nov 15 '18 at 2:55
mklement0
127k20241269
answered Nov 12 '18 at 22:58
TheMadTechnicianTheMadTechnician
25.1k21631
Glad to hear it, @TheMadTechnician; I appreciate the nice feedback, and also appreciate the quality of your answers here.
– mklement0
Nov 15 '18 at 18:50
add a comment |
Well, to start with, you need to update your OS since PowerShell 2.0 is only default on versions of Windows that are out of support now I'm pretty sure. That aside, this can be done fairly simply a few ways. Since you are going to be changing the column headers for each column (per your example), I would pipe the CSV data through a ForEach-Object loop, and create a new object based off each existing object, and output that.
$MyCSV = Import-Csv C:PathToFile.csv
$NewCSV = $MyCSV | ForEach-Object {
New-Object PSCustomObject -Property @{
'Col1' = $_.Col1.Split('.')[0]
'Col2' = $_.Col1
'Col3' = $_.Col2
}
} | Select-Object col1, col2, col3
Editor's note: PSv2 didn't support defining a custom object's properties in order, which is why the Select-Object call is needed to ensure the desired property enumeration order. In PSv3+ you can create custom objects as [pscustomobject] @{ ... }, which does respect the property-definition order.
This iterates the CSV, and for each record makes a new record with your desired properties. Like I said, there's a few ways to accomplish this, but I think this would be the simplest considering your needs.
edited Nov 15 '18 at 2:55
mklement0
127k20241269
answered Nov 12 '18 at 22:58
TheMadTechnicianTheMadTechnician
25.1k21631
Well, to start with, you need to update your OS since PowerShell 2.0 is only default on versions of Windows that are out of support now I'm pretty sure. That aside, this can be done fairly simply a few ways. Since you are going to be changing the column headers for each column (per your example), I would pipe the CSV data through a ForEach-Object loop, and create a new object based off each existing object, and output that.
$MyCSV = Import-Csv C:PathToFile.csv
$NewCSV = $MyCSV | ForEach-Object {
New-Object PSCustomObject -Property @{
'Col1' = $_.Col1.Split('.')[0]
'Col2' = $_.Col1
'Col3' = $_.Col2
}
} | Select-Object col1, col2, col3
Editor's note: PSv2 didn't support defining a custom object's properties in order, which is why the Select-Object call is needed to ensure the desired property enumeration order. In PSv3+ you can create custom objects as [pscustomobject] @{ ... }, which does respect the property-definition order.
This iterates the CSV, and for each record makes a new record with your desired properties. Like I said, there's a few ways to accomplish this, but I think this would be the simplest considering your needs.
edited Nov 15 '18 at 2:55
mklement0
127k20241269
answered Nov 12 '18 at 22:58
TheMadTechnicianTheMadTechnician
25.1k21631
edited Nov 15 '18 at 2:55
mklement0
127k20241269
edited Nov 15 '18 at 2:55
mklement0
127k20241269
edited Nov 15 '18 at 2:55
mklement0
127k20241269
127k20241269
answered Nov 12 '18 at 22:58
TheMadTechnicianTheMadTechnician
25.1k21631
answered Nov 12 '18 at 22:58
TheMadTechnicianTheMadTechnician
25.1k21631
answered Nov 12 '18 at 22:58
TheMadTechnicianTheMadTechnician
25.1k21631
25.1k21631
Glad to hear it, @TheMadTechnician; I appreciate the nice feedback, and also appreciate the quality of your answers here.
– mklement0
Nov 15 '18 at 18:50
add a comment |
Glad to hear it, @TheMadTechnician; I appreciate the nice feedback, and also appreciate the quality of your answers here.
– mklement0
Nov 15 '18 at 18:50
Glad to hear it, @TheMadTechnician; I appreciate the nice feedback, and also appreciate the quality of your answers here.
– mklement0
Nov 15 '18 at 18:50
Glad to hear it, @TheMadTechnician; I appreciate the nice feedback, and also appreciate the quality of your answers here.
– mklement0
Nov 15 '18 at 18:50
add a comment |
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.
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%2f53270986%2fmanipulating-csv-data-in-powershell-2%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
