Solved

replacing csv headers and RemedyBMC export

Posted on 2011-09-13
2
537 Views
Last Modified: 2012-05-12
Hello EE,

I have a csv export from Remedy BMC (ticket tracking system) and I want to run some logic on some of the data columns in the export using psh. I am in the first stages of the task, step one is to remove the column headers and replace them with new headers. I have done this many times before using the replace parameter but this Remedy csv file is not cooperating, the headers do not change. I suspect it is the meta characters used in the column headers. Is that possible? and how do I replace the headers with "new headers" . See script below
$file = gc "C:\Documents and Settings\user\My Documents\WindowsPowerShell\augmetrics.csv" 
$existingheaders = $file[0] 
$newheaders = "`"Site`""+",`"Incident`""+ ",`"ID`""+",`"LastName`""+",`"FirstName `""+",`"ServiceType`""+",`"Summary`""+",`"Notes`""+",`"Submitter`""+",`"Assignee`""+",`"ReportedDate`""+",`"ClosedDate`""+",`"Status`""+",`"StatusReasonHidden`""+",`"OperationCatTier1`""+",`"OperationCatTier2`""+",`"OperationCatTier3`""+",`"ProductCatTier1`""+",`"ProductCatTier2`""+",`"ProductCatTier3`""+",`"Category`""+",`"ResolutionClosureSource`""+",`"ResolutionMethod`""+",`"Email`""+",`"ReportedSource`""+",`"LastModifiedDate`""+",`"LastModifiedBy`""
$file = $file -replace "$existingheaders","$newheaders"
$file | set-content  "C:\Documents and Settings\user\My Documents\WindowsPowerShell\augmetrics.csv" 
$a = import-csv "C:\Documents and Settings\user\My Documents\WindowsPowerShell\augmetrics.csv"  

Open in new window

Here is the variable output of "existing headers" and "new headers"
$existingheaders
"Site+","Incident ID*+","Last Name*+","First Name*+","Service Type*","Summary*","Notes","Submitter*","Assignee+","Reported Date+","Closed Date","Status*","Status_Reason_Hi
dden","Operational Categorization Tier 1","Operational Categorization Tier 2","Operational Categorization Tier 3","Product Categorization Tier 1","Product Categorization T
ier 2","Product Categorization Tier 3","Priority*","Resolution","Closure Source","Resolution Method","Internet E-mail(1000000048)","Reported Source","Last Modified Date","
Last Modified By"

Open in new window

$newheaders
"Site","Incident","ID","LastName","FirstName ","ServiceType","Summary","Notes","Submitter","Assignee","ReportedDate","ClosedDate","Status","StatusReasonHidden","OperationC
atTier1","OperationCatTier2","OperationCatTier3","ProductCatTier1","ProductCatTier2","ProductCatTier3","Category","ResolutionClosureSource","ResolutionMethod","Email","Rep
ortedSource","LastModifiedDate","LastModifiedBy"
 

Open in new window

0
Comment
Question by:davesnb
2 Comments
 
LVL 16

Accepted Solution

by:
Learnctx earned 500 total points
Comment Utility
Rather than using the replace switch, it looks like you just want to replace the first line outright. So why not just set the first line to whatever you want regardless of what is currently there.
$file = gc $filelocation
$file[0]="`"Site`",`"Incident`",`"ID`",`"LastName`",`"FirstName `",`"ServiceType`",`"Summary`",`"Notes`",`"Submitter`",`"Assignee`",`"ReportedDate`",`"ClosedDate`",`"Status`",`"StatusReasonHidden`",`"OperationC
atTier1`",`"OperationCatTier2`",`"OperationCatTier3`",`"ProductCatTier1`",`"ProductCatTier2`",`"ProductCatTier3`",`"Category`",`"ResolutionClosureSource`",`"ResolutionMethod`",`"Email`",`"Rep
ortedSource`",`"LastModifiedDate`",`"LastModifiedBy`""
$file | Set-Content $filelocation

Open in new window

0
 

Author Closing Comment

by:davesnb
Comment Utility
yup, that did it, thanks for simplifying that for me.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Why would I want to create a function for tracking messages? I am glad you asked. As with most monotonous/routine tasks, human error tends to creep in after doing the same task over and over again. By creating a function, you load the function once…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

16 Experts available now in Live!

Get 1:1 Help Now