[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Removing first 4 characters from date field in csv file

Posted on 2016-10-19
7
Medium Priority
?
63 Views
Last Modified: 2016-10-19
I have a csv that is an export from a student information system. Lets say field J is the student enrollment date. it comes out as 09/02/2012 i need to change it to just read 2012 (essentially losing the first four characters) for each line of J in the CSV file ?

Does anyone have a quick batch or VB script that would do something like that ?
0
Comment
Question by:sattermc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 25

Expert Comment

by:NVIT
ID: 41850858
Can you submit a sample csv file?
0
 
LVL 85

Expert Comment

by:oBdA
ID: 41850866
Batch can be tricky when handling text files containing special characters.
Powershell is safer (and not as nineties as VB Script).
If the csv has no header line, you need to provide at least the number of columns in the file.
Assuming the csv has a header line, and the column is called "J":
$ColumnHeader = "J"
Get-Content C:\Temp\Input.csv | % {$_.$ColumnHeader = ($_.$ColumnHeader).Split('/')[2]; $_} | Export-Csv C:\Temp\Output.csv -NoTypeInformation

Open in new window

And you'd lose the first 6 characters ...
0
 

Author Comment

by:sattermc
ID: 41850884
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:oBdA
ID: 41850904
This will add quotes around the fields as well; is that a problem?
Import-Csv -Path C:\Temp\Input.csv -Header (1..17 | % {"Col_$($_)"}) |
	ForEach-Object {$_.Col_10 = ($_.Col_10).Split('/')[2]; $_} | 
	Export-Csv C:\Temp\Output.csv -NoTypeInformation

Open in new window

0
 

Author Comment

by:sattermc
ID: 41850918
with ps script get "You cannot call a method on a null-valued expression."

script i'm running is:
$ColumnHeader = "m"
Get-Content d:\ioeducation\iostudent.csv | % {$_.$ColumnHeader = ($_.$ColumnHeader).Split('/')[2]; $_} | Export-Csv D:\ioeducation\student1.csv -NoTypeInformation

quotes would be a problem
0
 
LVL 85

Accepted Solution

by:
oBdA earned 2000 total points
ID: 41850948
Try this then:
$InputFile = "C:\Temp\Input.csv"
$OutputFile = "C:\Temp\Output.csv"
Import-Csv -Path $InputFile -Header (1..17 | % {"Col_$($_)"}) |
	ForEach-Object {$_.Col_10 = ($_.Col_10).Split('/')[2]; $_} | 
	ConvertTo-Csv -NoTypeInformation |
	Select-Object -Skip 1 |
	ForEach-Object {$_ -replace '"', ''} |
	Set-Content -Path $OutputFile

Open in new window


Alternative, string-based split:
$InputFile = "C:\Temp\Input.csv"
$OutputFile = "C:\Temp\Output.csv"
Get-Content -Path $InputFile |
	ForEach-Object {
		$Cols = $_.Split(',')
		$Cols[9] = $Cols[9].Split('/')[2]
		$Cols -join ','
	} |
	Set-Content -Path $OutputFile

Open in new window

0
 

Author Closing Comment

by:sattermc
ID: 41850971
Perfect,

Thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Introduction: Recently, I got a requirement to zip all files individually with batch file script in Windows OS. I don't know much about scripting, but I searched Google and found a lot of examples and websites to complete my task. Finally, I was ab…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question