Vista - Retrieving selected lines from all text files in a directory

I need to extract all lines that begin with "B3" from text files that all
reside in the same directory on a server and export it to a comma separated
values file. For each "B3" line extracted from a file, I would like to
capture the create date of the file the line came from. Here is a sample of
text from one of the files I would like to parse:

"B3" "XXINVOICE DIR A" "XXINVOICEROOT" "CC" 080124 ###.## "" "CEPH"
"B2A" "00" "CI"
"G62" "86" 080117
"G62" "66" 080121
"N1" "BT" "" "94" "0501"
"N1" "SH" "CUSTOMER NAME"
"N3" "CUSTOMER ADDRESS" ""
"N4" "REDMOND" "OR" "97756"
"N1" "ST" "MY COMPANY NAME"
"N3" "MY COMPANY STREET ADDRESS"
"N4" "TACOMA" "WA" "98421"
"N9" "BM" "574425"
"SPO" "A PO NUMBER HERE" "PC" 4 "L" 625
"LX" 1
"L1" 1 ##.## "PH" 458.06 "" "FREIGHT CHARGE"
"LX" 2
"L1" 2 "" ##.## "FUE" "FUEL SURCHARGE"
"LX" 3
"L1" 3 "" ##.## "CSC" "ADMIN FEE"
"L3" 625 "G" ##.## 4
"B3" "XXINVOICE DIR B" "XXINVOICE ROOT" "CC" 080124 ##.## "" "CEPH"


And here is what I would to extract from the above:
"B3" "INVOICE DIR A" "XXINVOICEROOT" "CC" 080124 ###.## "" "CEPH"
"B3" "XXINVOICE DIR B" "XXINVOICE ROOT" "CC" 080124 ##.## "" "CEPH"


The text is tab delimited, and I am only interested in lines beginning "B3".

When this is output to .csv file, I would like to add the create date of the
file the "B3" line came from so that it looks something like this:
"B3" "INVOICE DIR A" "XXINVOICEROOT" "CC" 080124 ###.## "" "CEPH"
06/30/2008
"B3" "XXINVOICE DIR B" "XXINVOICE ROOT" "CC" 080124 ##.## "" "CEPH"
06/30/2008

I know how to get the names of all files in the directory:
gci \\serverpath\subdirectorypath -include *.txt -name | export-csv
c:\users\username\desktop\filenames.csv

But it's only a small part of what I need.

Any ideas?

Hi Shawn,

gc .\1.txt | ?{ $_.split()[0] -ilike '"B3"'} |`
%{ $_ -replace "`t","," -replace '$',$( `
get-date -uformat ",""%m/%d/%Y""") } | out-file -encoding ascii a.csv

It just does all of work by itself. I cannot find an easy way to save the
Strings using export-csv. So I convert them into csv format instead of
invetigating on how to use Export-CSV.

Best regards,
Tao Ma


"Shawn" <Shawn@xxxxxx> дÈëÏûÏ¢ÐÂÎÅ:E99F8C1F-89C6-411F-B391-5E5E7D20085C@xxxxxx

You can pipe the files to Select-String and filter their content. Select-String outputs MatchInfo objects, we'll use the Path and Line properties to build the new CSV content. Use Get-Item and the MatchInfo's Path value to get the file's CreationTime property and append it to the MatchInfo's Line modified value after replacing each '\t' with a ','.

# set the field header for the new CSV file
sc test.csv 'Field0,Field1,Field2,Field3,Field4,Field5,Field6,Field7,CreationTime'

# use Add-Content to append the new lines to the new file, here ASCII
# encoding is specified. Unicode is the default encoding
ls \\serverpath\subdirectorypath *.txt | select-string '^"B3"' |
% {($_.line -replace '\t',',') +
",$("{0:MM/dd/yyyy}" -f (gi $_.path).creationTime)"} |
ac test.csv -en ASCII

# if Unicode is fine, you can redirect and append the output to the new
# file using the redirection operators '>' and '>>'
'Field0,Field1,Field2,Field3,Field4,Field5,Field6,Field7,CreationTime' > test.csv
ls \\serverpath\subdirectorypath *.txt | select-string '^"B3"' |
% {($_.line -replace '\t',',') +
",$("{0:MM/dd/yyyy}" -f (gi $_.path).creationTime)"} >> test.csv

# verify the new CSV
ipcsv test.csv

--
Kiron

There are some bugs in my original code. Sorry for my careless.

I rewrite it here:
PS C:\temp\t> dir *.txt | % {$t = $_.CreationTime.ToString('MM\/dd\/yyyy');
gc $_ | `Tao Ma

"Tao Ma" <feng_eden@xxxxxx> дÈëÏûÏ¢ÐÂÎÅ:%23CLIeX%232IHA.1196@xxxxxx

Hi Tao Ma -

Thank you for your help! Since I am new to this, I haven't figured out how
to get your code to return any results, probably because I'm not sure how to
get it to execute. When I run it by pasting the text into PowerShell, I get
the following error:
Get-Process : A parameter cannot be found that matches parameter name 'dir'.
At line:1 char:3

If I replace dir with the path to the files I am interested in, I get the
same error, but it treats the path as a parameter that it cannot find. I
could use a bit of explanation.

* Where do I specify the path to the files I would like to parse?
* Where will the script output the csv results? C:\temp ?
* How do I get the script to execute? Is this something I need to do from
PowerShell, or do I call it from an ordinary command line?

Hi Shawn,

You just copied too many chars..."PS C:\temp\t>" is command
prompt..."C:\temp\t" indicates current working directory.
So if you emit the file path, powershell will find it in the current working
directory.
Just replace '*.txt' to the files you are interested in. If there is more
than one files, please separate them using commas.
Paste them into PowerShell to execute them.

I have to go to sleep. It's 00:44...I hope you can figure it out soon.

Best regards,
Tao Ma


"Shawn" <Shawn@xxxxxx> дÈëÏûÏ¢ÐÂÎÅ:83D59990-E4C1-4060-A86A-84E2EBED84D4@xxxxxx

Hi Kiron -
I'm having trouble getting this part of the script to work:
% {($_.line -replace '\t',',') +
",$("{0:MM/dd/yyyy}" -f (gi $_.path).creationTime)"}

There are errors that it doesn't recognize parameters. Could you give me a
breakdown of what the first and second lines are doing? I was able to
retrieve the B3 lines without trouble by using:
select-string '^"B3"' | export-csv c:\temp\Log.csv

But I wasn't able to figure out how to use what you gave me. I am new to
PowerShell, so would you be willing to give me a line by line description of
what's happening?

Thanks for your work on this - I'm pretty close to what I need.

"Kiron" wrote:

Sorry Shawn, word wrapping and negligence are the culprits for the errors you're getting, although I always try to keep lines short, there were a couple here that I missed. Here is a description of the code, feel free to ask questions if something isn't clear - I'm not a good 'explainer' - but I'm glad to help.

# 1
$headers = 'Fld0,Fld1,Fld2,Fld3,Fld4,Fld5,Fld6,Fld7,CreationTime'
# 2
sc test.csv $headers
# 3
ls \\serverpath\subdirectorypath *.txt |
# 4
select-string '^"B3"' |
# 5
% {
# 6
($_.line -replace '\t',',') +
# 7
",{0:MM/dd/yyyy}" -f (gi $_.path).creationTime} |
# 8
ac test.csv -en ASCII
# 9
ipcsv test.csv

# 10
$headers > test.csv
# 11
ls \\serverpath\subdirectorypath *.txt |
# 12
select-string '^"B3"' |
# 13
% {
# 14
($_.line -replace '\t',',') +
# 15
",{0:MM/dd/yyyy}" -f (gi $_.path).creationTime} >> test.csv

# 16
ipcsv test.csv

1.- First assign CSV field names to a variable, I've shorten the field names to avoid the 'word wrap beast' but you should substitute them with the relevant names, remember to double-quote any name that contains spaces, although I recommend not to use spaces.
2.- Use Set-Content to create the new CSV file with a header row. In this sample the file is 'test.csv' and it will be created in the current directory. You can also specify the file's fully qualified or relative path.
3.- Get-ChildItem binds '\\serverpath\subdirectorypath' and '*.txt' to its -Path and -Filter parameters 'ByPosition' respectively and pipes the [System.IO.FileInfo] objects to Select-String.
4.- Select-String binds the current pipe element's path to its -Path parameter 'ByPropertyName' and '^"B3"' to its -Pattern parameter 'ByPosition'. All lines that match the pattern are piped as [Microsoft.PowerShell.Commands.MatchInfo] objects to ForEach-Object.
5.- ForEach-Object assigns each [Microsoft.PowerShell.Commands.MatchInfo] object to the automatic variable '$_'.
6.- The Line value of the [Microsoft.PowerShell.Commands.MatchInfo] object is modified with the -Replace operator, it finds and replaces each tab '\t' and replaces it with a comma ','.
7.- Get-Item binds the [Microsoft.PowerShell.Commands.MatchInfo] object's Path value 'ByPosition' and, by enclosing the statement in an Expression - inside parenthesis '(gi $_.path)' - we can, get the [System.IO.FileInfo] object's CreationTime. This value is formatted with the Format operator '-F' into the string ",{0:MM/dd/yyyy}". Note the comma at the beginning, it's necessary because the formatted date string is appended to the modified line and the new line is outputted to Add-Content.
8.- Add-Content binds 'test.csv' to its -Path parameter 'ByPosition', each piped - and modified - line to its -Value parameter 'ByPipedValue' annd 'ASCII' to its -Encoding parameter directly. I used ASCII just to demonstrate its availability, but it is not necessary, PowerShell's default encoding is Unicode, this is why I also included a version that creates the new CSV file simply through PowerShell's Redirection operators '>' and '>>'.
9.- Finally, test the new CSV data with Import-CSV. Here the Cmdlet binds 'test.csv' to its -Path parameter 'ByPosition'.
10.- Using PowerShell's Redirection operators - if you prefer Unicode encoding -. '>' redirects the CSV field names to 'test.csv', the file is created if it doesn't exist, if it exists, the file is overwritten.
11.- Same as #3
12.- Same as #4
13.- Same as #5
14.- Same as #6
15.- Get-Item binds the [Microsoft.PowerShell.Commands.MatchInfo] object's Path value 'ByPosition' and, by enclosing the statement in an Expression - inside parenthesis '(gi $_.path)' - we can, get the [System.IO.FileInfo] object's CreationTime. This value is formatted with the Format operator '-F' into the string ",{0:MM/dd/yyyy}". Note the comma at the beginning, it's necessary because the formatted date string is appended to the modified line and '>>' appends the new line to 'test.csv'.
16.- Same as #9

--
Kiron

Kiron - thank you so very much! This is 100% on the money and will save me
untold hours of work. By the way, your explanations were just what I needed,
your assessment of your ability to explain not withstanding.

"Kiron" wrote: