Solved

Removing first 4 characters from date field in csv file

Posted on 2016-10-19
7
25 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
  • 3
  • 3
7 Comments
 
LVL 23

Expert Comment

by:NVIT
Comment Utility
Can you submit a sample csv file?
0
 
LVL 82

Expert Comment

by:oBdA
Comment Utility
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
Comment Utility
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 82

Expert Comment

by:oBdA
Comment Utility
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
Comment Utility
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 82

Accepted Solution

by:
oBdA earned 500 total points
Comment Utility
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
Comment Utility
Perfect,

Thank you
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now