Search CSV file columns and return value to Text file

PowerShell Help
1

Hello,

I am fairly new to Powershell and attempting to write a script to achieve the following:

Text file divided into 13 fields Pipe delimited, line length of 227 alpha,numeric in single line.
Text file Field 2 is search CSV file name, 5th Field is Search criteria in CSV file.
If search in CSV file located/found copy field 1,2 & 3 and append it to text file at the end of line, i.e. after 227.
If search csv file or search criteria is not found continue next line in the text file till the end.

Field-1|-Field 2-----|F-3|F-4|Field 5-|-F-6|F-7|F-8|F-9|…|F13| first line details of file generated records starts from line2
Field-1|010010481|F-3|F-4|610209|-F-6|F-7|F-8|F-9|…|F13|
Field-1|044003610|F-3|F-4|051548|-F-6|F-7|F-8|F-9|…|F13|

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

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

Desired Output in Text file.
Field-13 end at 227|14,4595,26172
Field-13 end at 227|12,1122,311

Hope I’ve done a good enough job of explaining what I am attempting, if not I will explain further. From the flexibility I’ve seen with Powershell I have to believe this is doable, it’s just beyond my experience level at this point. Any help would be greatly appreciated.
Thanks in advance.

2

I dont think I fully understand your question, please correct me if I am wrong.

*You would like to import the CSV-file into powershell
*Field 2 contains a path to a CSV-file in which you want to check if the value from field 5 exist
*If it exists then you want to append fields 1,2 and 3 to the end of the first CSV-file that you imported
*If it doesn’t exist you want to do the same check on the next line in the CSV-file that you imported

/Alexander

3

Hi,

 You would like to import the CSV-file into powershell

Yes, particular CSV file metioned in field 2 of the Text file. Text file record starts from 2nd line.

 *Field 2 contains a path to a CSV-file in which you want to check if the value from field 5 exist

Its not the path, but its the name of particular CSV file which contains the record. i.e. 010010481 by default they are .csv file. CSV file name & its numeric, constant 9 digits.

 *If it exists then you want to append fields 1,2 and 3 to the end of the first CSV-file that you imported

Append CSV file field 1,2 & 3, to Text File and that to at end of line.

 *If it doesn't exist you want to do the same check on the next line in the CSV-file that you imported

Yes, to continue for next search in the Text file.

Hope to have a favorable reply.

4

Hello,

First of all sorry for my late answer, I have been pretty busy in the weekend.

The following code will import the specified text file, and skip the first line.
It will then go through each line in the text file and extracting the CSV filename and the value to check for in that CSV file.
After the extraction of the file name and the value to check it will then import the CSV file which has that filename and check for the extracted value in it.
If the CSV file contains the value it will append the values from the specified fields in the CSV file to the specified text file.

If you have any questions regarding the script or if I haven’t understood you correctly, then ask/clarify.

The code could maybe be a bit improved, but we will skip that until it does exactly what you want.

## Imports the text file that contains the CSV information, and skips the first line.
## Change to the correct path.
$txtFile = Get-Content -Path "pathToTxtFile\txtFile.txt" | Select-Object -Skip 1

foreach ($line in $txtFile)
{
	## Splits the line from the text file and selects the correct index.
	$csvFileName = $line.Split('|')[1]
	$csvContains = $line.Split('|')[4]
	
	## Imports the CSV to check.
	## Change to the correct path, the filename will be the one from the
	## text file which has been collected earlier.
	$csvToCheck = Import-Csv -Path "pathToCsv\$csvFileName.csv"
	
	## Change to the name of the column that you want to check in the CSV.
	$columnToCheck = $csvToCheck | Group-Object -AsHashTable -AsString -Property nameOfColumnToCheck
	
	## If the column, which you have stated earlier, in the CSV contains the data from
	## the fifth field in the text file.
	if ($columnToCheck.ContainsKey($csvContains))
	{
		## Change nameOfColumnToCheck to the name of the column that has been checked for the value
		$appendData = ($columnToCheck).Values | Where-Object {$_.nameOfColumnToCheck -eq $csvContains}
		
		## Appends the data from field 1,2 and 3 from the CSV to the bottom
		## of the specified text file.
		"$($appendData.nameOfColumn1),$($appendData.nameOfColumn2),$($appendData.nameOfColumn3)" | Out-File "pathToTxtFile\txtFile.txt" -Append
	}
}

Keep in mind that I haven’t tested the script because the lack of your text/csv files.

/Alexander

5

Thanks a lot for helping, Alexander Johansson.

There is no Out-File generated.

 ## Change to the name of the column that you want to check in the CSV.
	$columnToCheck = $csvToCheck | Group-Object -AsHashTable -AsString -Property nameOfColumnToCheck

Does it mean to mention all the columns headers name. Whereas my search columns starts from 4th field, i.e. D:CY 100 Column.
For reference i have attached the sample csv file.

Reference Point : If there is no csv file or if search is not found copy the text file line as it is to new out-put file.

Hope to have a favourable reply.

6

Hello,

Your attached CSV-file isn’t a “correctly” formatted CSV-file.
I have made a few changes, please replace with your paths.

$txtFile = Get-Content -Path "path\txtToReadFrom.txt" | Select-Object -Skip 1

foreach ($line in $txtFile)
{
	$txtFileName = $line.Split('|')[1]
	$txtValueToCheck = $line.Split('|')[4]
	
	if (!(Test-Path -Path "path\$txtFileName.csv"))
	{
		$line | Out-File "path\txtNoFileOrValueExist.txt" -Append
	
		continue	
	}
	
	$txtContains = Select-String -Path "path\$txtFileName.csv" -Pattern $txtValueToCheck
	
	if ($txtContains)
	{
		$fieldAppend = $txtContains.Line.ToString().Split('|')[0, 1, 2] -join '|'
		
		$fieldAppend | Out-File "path\txtForOutput.txt" -Append
	}
	else
	{
		$line | Out-File "path\txtNoFileOrValueExist.txt" -Append
	}
}

path\txtToReadFrom.txt = The text file that you use as your input (Change both path and filename)
path$txtFileName.csv = The “CSV-file” to check (Change ONLY the path)
path\txtNoFileOrValueExist.txt = The text file that it will write to if it can’t find the value or the file (Change name and path)
path\txtForOutput.txt = The text file that it will write the output to when it has found the value (Change name and path)

/Alexander

7

Thanks Alexander Johansson.

The Non Value file (txtNoFileOrValueExist.txt) is created perfectly, It bifurcates and appends to text file.

The Value file still does not copy the text file contents, it creates only the values found from csv file field 1,2 & 3. I have attached the file for reference. The error on console.

You cannot call a method on a null-valued expression.
At C:\job\search0.ps1:19 char:44
+         $fieldAppend = $txtContains.Line.ToString <<<< ().Split('|')[0, 1, 2] -join '|'
    + CategoryInfo          : InvalidOperation: (ToString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Hope to have a favourable response.

8

Which are the text file contents that you want it to copy?
As I understood you, you did only want the field 1, 2 and 3 in the txtForOutput file.

/Alexander

9

Thanks Alexander Johansson.

I am forwarding both the desired result text file for reference.

The Non Value file (txtNoFileOrValueExist.txt) is created perfectly.

As i had described in my first post regarding the csv file fields to be appended at the end of line i.e. after 227 length of text file line.

Desired Output in txtForOutput.txt file.
All Text file Fields which end at 227|14|4595|26172

Awaiting Response.
Thanks.

10

So you want the full line to the txtForOutput.txt?

Try to change:

$fieldAppend = $txtContains.Line.ToString().Split('|')[0, 1, 2] -join '|'

To this:

$fieldAppend = $txtContains.Line.ToString()

Is that correct?

I’m sorry but I’m having a hard time to understand you!

/Alexander

11

Thanks Alexander Johansson.

This gives the desired results.

 Add-Content "C:\johan\txtForOutput.txt" ($line + $fieldAppend)

I get error message.

You cannot call a method on a null-valued expression.
At C:\job\search0.ps1:19 char:44
+         $fieldAppend = $txtContains.Line.ToString <<<< ().Split("|")[0, 1, 2] -join "|"
    + CategoryInfo          : InvalidOperation: (ToString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Highly Grateful to you.

12

Regarding your error message, could you try to add this:

Write-Output "txtContains equals: $txtContains"

Above this:

$fieldAppend = $txtContains.Line.ToString().Split('|')[0, 1, 2] -join '|'

So it looks like this:

Write-Output "txtContains equals: $txtContains"
$fieldAppend = $txtContains.Line.ToString().Split('|')[0, 1, 2] -join '|'

And when you get the error, what does the Write-Output say?

/Alexander

13

Thanks Alexander Johansson.

CSV-file isn’t a “correctly” formatted CSV-file that was the reason of error now recified.

One question, as i append this $fieldAppend to the end of line instead of that can i position it at 6,7 & 8 field of the $line.

Many thanks in advance.