PowerShell to search record in CSV file

Topic

New Reply

Hello to all,

Dear Powershell Experts I need your help.

A Text file with Pipe-Delimiters of 13 fields, the 2nd field represents CSV file name and the search criteria is in the 5th field. Top line is header, records start from line2, the line length is 227 characters in single line.

I want to search the particular csv file [which is mentioned in 2nd field] for 5th field serial number of text file in very large CSV database.

Once that serial is located in the CSV file, copy the 1st field and replace the 7th field of the text file and 2 & 3 fields of CSV file to the end of line in the text file, after 227 character.

In this if a particular csv file or search serial is not found continue next search just copy the original line/record from the text file to New Text file.

Text File Structure.
-Field 1—|-Field 2—|-Field 3–|-Field 4——–|Field 5-|-Field 6—-|F7|-Field 8—-|Field9|..|Field 13|
004196202|010010481|01110001|0091000440191|610209|4000000089|22|0000000001|ends at 227 char.
003221311|044003610|20222014|0000000500000|051548|0201001000|05|2000100000|ends at 227 char.
———|csv file name| ————————|Search|———–|replace|

CSV File Database Structure.

F1, F2, F3,
72,2321,8272,819710,819711,819712,819713,819714,819715,819716,819717,819718,819719,81920

010010481.csv
14,4595,26172,610201,610202,610203,610204,610205,610206,610207,610208,610209,610210,…,

044003610.csv
12,1122,311,051541,051542,051543,051544,051545,051546,051547,051548,051549,051550

Result To New Text file:
004196202|010010481|01110001|0091000440191|610209|4000000089|14|0000000001|after 227 char.|4595,26172
003221311|044003610|20222014|0000000500000|051548|0201001000|12|2000100000|after 227 char.|1122,311

The CSV data is very large, I need some very beautiful codes which can search very faster, & i feel this can be achived with powershell the real power.
Many Many Thanks in advance.

Reply | Quote

Replies

John,

See post #6 here. This should give you some code to get you started working with .csv files. Post back if you need more help. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

John,
Post back if you need more help. HTH :cheers:

RG, the below script identifies the csv from input text file and searches the 5th field and outputs the search data into found and not-found category, here i face the difficulty if the search field has a duplicate key in csv file then it should output that to a text file as duplicate found.
For testing i have attached the text input file of 200 records & related CSV file.

The results will be Value Found file will contain 190 rows.
The NoValuefound will contain 5 rows & finally the Duplicate found will have 5 rows.
Sorry for troubling you.

Code:

$txtFile = Get-Content -Path “C:alextextinput.txt” | Select-Object -Skip 1

foreach ($line in $txtFile)
{
$txtFileName = $line.Split(‘|’)[1]
$textValue = $line.Split(‘|’)[4]
 
	if (!(Test-Path -Path “C:alex$txtFileName.csv”))
	{
		$line | Out-File “C:alexNoValuefound.txt” -Append
 
		continue	
	}
 
	$txtContains = Select-String -Path “C:alex$txtFileName.csv” -Pattern $textValue
 
	if ($txtContains)
	{
        
        $fieldAppend = $txtContains.Line.ToString().Split(‘|’)[0, 2] -join ‘|’
 
        Add-Content “C:alexValuefound.txt” ($line + $fieldAppend)
	
	}
	else
	{
		$line | Out-File “C:alexNoValuefound.txt” -Append
	}
}

Reply | Quote

John,

Sorry but I’m a bit under the weather and not thinking too straight. What I would suggest though as an approach would be to sort the records by the file number. You can then save the last file number processed and compare it against the next file number to determine duplicates. The link in the previous post can help you accomplish this. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

John,

Sorry but I’m a bit under the weather and not thinking too straight. What I would suggest though as an approach would be to sort the records by the file number. You can then save the last file number processed and compare it against the next file number to determine duplicates. The link in the previous post can help you accomplish this. HTH :cheers:

RG thanks for reply.

A bit briefing, Infact its not required to compare with other csv files for duplicates, i mean the input file (2nd field) indicates the csv file name and in that file only it has to search the 5th field & if its there then found or not-found or it has record but with duplicates in it. So i want to pull the duplicates to third file.
I am able to locate 2 criteria found and not-found, Now its hard time for me, and i seek your assistance.
If possible test my sample files it will give some idea.
Thanks in advance.

Reply | Quote

John,

To capture duplicates, after the line “$fieldAppend = ….”, try making the following change to your script

Code:

Change:
Add-Content "C:alexValuefound.txt" ($line + $fieldAppend)
To:
if(!(Select-String -Path "C:alexValuefound.txt" -Pattern $textValue))
{
	Add-Content "C:alexValuefound.txt" ($line + $fieldAppend)
}
else {
	Add-Content "C:alexDuplicatefound.txt" $line
}

HTH

P.S. Get better soon, RG

Reply | Quote

Thanks Cliff.H,

Its giving error.

Select-String : Cannot bind argument to parameter ‘Pattern’ because it is null.
At C:jobsearch2.ps1:18 char:59
+ if(!(Select-String -Path “C:alexValuefound.txt” -Pattern <<<< $textValue))
+ CategoryInfo : InvalidData: (:) [Select-String], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.SelectStringCommand

Awating reply.

Reply | Quote

John,

That is odd. It appears to suggest that “$textValue” is null, and yet the line causing the error would only execute if “$txtContains” contained a value, and that is assigned a couple of lines above by using “$textValue” as a search criteria.???

Try to see if you can find where the error is being generated, in the mean time I’ll take a look at your sample file to see if I can come up with something.

Reply | Quote

John,

The attached .zip file contains a script file as well as the output files, based on your sample data files.
Let me know if it’s OK.

Reply | Quote

Thanks Cliff.H, Its perfect and works like a charm.

Thanks a lot.

Reply | Quote

Hello Cliff.H,

I tested it on sample data and found its okay, and i thanked you for that, Now when i wanted to implement it on actual data i found its does not append the values from csv file to valuefound.txt, and the error is..

You cannot call a method on a null-valued expression.
At C:alexExtractFromCSV.ps1:26 char:65
+ $fieldAppend = $appendPattern.Match($txtContains.Line.ToString <<<< ()).groups[1..2].value -join '|'
+ CategoryInfo : InvalidOperation: (ToString:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

DuplicateFound.txt & NoValuefound.txt are perfect,
Sorry for this, but i am lost, Please have a look and provide a solution.
Thanks in advance.

Reply | Quote

John,
That would suggest that your actual data is different to the sample data you provided.
Try adding the following lines to the script file, before line 26, i.e. before the line starting with “$fieldAppend = $appendPattern…….”

Code:

if ( -not $appendPattern.Match($txtContains.Line.ToString()) ) {
	$err = @()
	$err += “Line: $line”
	$err += “TextValue: $textValue”
	$err += “TxtContains: $txtContains”
	$err +=  ‘—‘
	Add-Content -Path ‘C:alexErrors.txt’ -Value $err
}

I cannot reproduce the errors, but this should hopefully place the errors in the file Errors.txt. Post the file and I’ll take a look at it as soon as I can.

Reply | Quote

John,
Post the file and I’ll take a look at it as soon as I can.

Sorry Cliff.H to trouble you, I tried your suggestion but there is no error.txt generated.
Now i have uploaded my actual data. When time permits you please have a look, its not giving the values in valuefound.txt and in there are duplicates but it does not generate the duplicatefound.txt.

Thanks.

Reply | Quote

John,

Try this one. I hope it meets your requirements.

Reply | Quote

Thanks Cliff.H,

I have tested it on actual data and its perfect.
Sorry for delay reply.

Many Thanks & grateful to you.
Thanks

Reply | Quote