Improve company productivity with a Business Account.Sign Up

x
?
Solved

Split content of a csv file using powershell

Posted on 2011-09-26
6
Medium Priority
?
870 Views
Last Modified: 2012-08-14
Hi,
    I want to split the content of a csv file based on a delimiter. The csv file has three columns. I want to split the content of first column i.e i want to split the text after last  occurence of "\". Please provide me a sample.

Sample:
F:\CDR_FTP\SMS\Archive\20110923\IE3_CS_22_09_2011_23_49_00_016145.cdr 68 68
F:\CDR_FTP\SMS\Archive\20110923\IE3_CS_22_09_2011_23_52_18_016146.cdr 68 68
F:\CDR_FTP\SMS\Archive\20110923\IE3_CS_22_09_2011_23_55_30_016147.cdr 68 68
F:\CDR_FTP\SMS\Archive\20110923\IE3_CS_22_09_2011_23_59_11_016148.cdr 15 69

Expected Output:
IE3_CS_22_09_2011_23_49_00_016145.cdr 68 68
IE3_CS_22_09_2011_23_49_00_016146.cdr 68 68
IE3_CS_22_09_2011_23_49_00_016147.cdr 68 68
IE3_CS_22_09_2011_23_49_00_016148.cdr 15 68
0
Comment
Question by:SQLM_M
  • 2
  • 2
  • 2
6 Comments
 
LVL 17

Expert Comment

by:nepaluz
ID: 36599214
here goes, remember to change the path to your file path
        Using sr = New FileIO.TextFieldParser("YouPath") With {.Delimiters = {"\"}}
            While Not sr.EndOfData
                Dim line = sr.ReadFields()
                Dim TheResult = line.Last
            End While
        End Using

Open in new window

0
 
LVL 17

Expert Comment

by:nepaluz
ID: 36599218
Sorry .... did not see the powershell .....
0
 
LVL 9

Accepted Solution

by:
chrismerritt earned 2000 total points
ID: 36601400
I'm making some assumptions here, based on not know what you want to do with these values etc afterwards.

Assumption 1:

Your CSV file lives is called C:\TEMP\file.csv

Assumption 2:

Your CSV file is laid out like this:

Path
F:\CDR_FTP\SMS\Archive\20110923\IE3_CS_22_09_2011_23_49_00_016145.cdr 68 68
F:\CDR_FTP\SMS\Archive\20110923\IE3_CS_22_09_2011_23_52_18_016146.cdr 68 68
F:\CDR_FTP\SMS\Archive\20110923\IE3_CS_22_09_2011_23_55_30_016147.cdr 68 68
F:\CDR_FTP\SMS\Archive\20110923\IE3_CS_22_09_2011_23_59_11_016148.cdr 15 69

If so this code should split the content and give you the last section of the text:

$CSVFile = Import-CSV "C:\TEMP\file.csv"

foreach ($Line in $CSVFile)
{
	$LastSection = ($Line.Path).Split("\")[-1]
	$LastSection | Out-Host
}

Open in new window

0
NEW Internet Security Report Now Available!

WatchGuard’s Threat Lab is a group of dedicated threat researchers committed to helping you stay ahead of the bad guys by providing in-depth analysis of the top security threats to your network.  Check out this quarters report on the threats that shook the industry in Q4 2017.

 

Author Comment

by:SQLM_M
ID: 36707513
Hi,

Thanks for your response, I get the following error when i execute your code.

You cannot call a method on a null-valued expression.
At line:3 char:38
+     $LastSection = ($Line.Path).Split <<<< ("\")[-1]
    + CategoryInfo          : InvalidOperation: (Split:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
0
 
LVL 9

Expert Comment

by:chrismerritt
ID: 36707866
To execute my code the CSV must be formatted exactly the same as I have entered, including the "Path" heading at the top of the list of directories.

$Line.Path is saying saying that for each line of the CSV file, use the value found in the "Path" column.

I have tested the script and it does work, so either it's not linking to the right CSV file location or the CSV file does not contain the data in the same way I have entered it.

You can try stepping through it to make sure that values are set properly, i.e. after executing my code but before closing the window, echo back the value of $CSVFile by simply entering $CSVFile into the prompt and hitting return.

You can do this for any value, powershell will remember what it's assigned to variables in the same window, so for example if you do this:

$a = 10

Then afterwards just put this:

$a

It will echo back to you the value 10.
0
 

Author Closing Comment

by:SQLM_M
ID: 36812824
Thanks for your solution..
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
In an Exchange Crossforest migration, the distribution groups can be a very complex operation that would cause loss of time, lots of issues and continued headaches if not solved in a timely manner. I had to do a similar project so I created a sc…
Loops Section Overview
Screencast - Getting to Know the Pipeline

595 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