Search CSV file columns and return value to Text file

This topic contains 12 replies, has 2 voices, and was last updated by Profile photo of johan Fernandes johan Fernandes 2 years, 1 month ago.

  • Author
    Posts
  • #20190
    Profile photo of johan Fernandes
    johan Fernandes
    Participant

    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|[u]610209[/u]|-F-6|F-7|F-8|F-9|........|F13|
    Field-1|044003610|F-3|F-4|[u]051548[/u]|-F-6|F-7|F-8|F-9|........|F13|

    010010481.csv
    [u]14,4595,26172[/u],610201,610202,610203,610204,610205,6 10206,610207,610208,[u]610209[/u],610210,...,

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

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

    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.

  • #20193
    Profile photo of Alexander Johansson
    Alexander Johansson
    Participant

    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

  • #20216
    Profile photo of johan Fernandes
    johan Fernandes
    Participant

    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 [u]Text File [/u]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.

  • #20245
    Profile photo of Alexander Johansson
    Alexander Johansson
    Participant

    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

  • #20292
    Profile photo of johan Fernandes
    johan Fernandes
    Participant

    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.

  • #20303
    Profile photo of Alexander Johansson
    Alexander Johansson
    Participant

    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

  • #20312
    Profile photo of johan Fernandes
    johan Fernandes
    Participant

    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.

  • #20325
    Profile photo of Alexander Johansson
    Alexander Johansson
    Participant

    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

  • #20328
    Profile photo of johan Fernandes
    johan Fernandes
    Participant

    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.

  • #20338
    Profile photo of Alexander Johansson
    Alexander Johansson
    Participant

    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

  • #20362
    Profile photo of johan Fernandes
    johan Fernandes
    Participant

    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.

  • #20386
    Profile photo of Alexander Johansson
    Alexander Johansson
    Participant

    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

  • #20388
    Profile photo of johan Fernandes
    johan Fernandes
    Participant

    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.

You must be logged in to reply to this topic.