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
Solved

Removing first 4 characters from date field in csv file

Posted on 2016-10-19
7
39 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 24

Expert Comment

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

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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 84

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 84

Accepted Solution

by:
oBdA earned 500 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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