Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jan 2014
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts

    PowerShell to search record in CSV file

    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,81 9715,819716,819717,819718,819719,81920

    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

    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.
    Last edited by John_2014; 2014-10-29 at 16:07.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Jan 2014
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    John,
    Post back if you need more help. HTH
    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:\alex\textinput.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:\alex\NoValuefound.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:\alex\Valuefound.txt" ($line + $fieldAppend)
    	
    	}
    	else
    	{
    		$line | Out-File "C:\alex\NoValuefound.txt" -Append
    	}
    }
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Lounger
    Join Date
    Jan 2014
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    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
    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.

  6. #6
    Lounger
    Join Date
    Dec 2009
    Location
    Gillingham, Dorset, UK
    Posts
    30
    Thanks
    0
    Thanked 13 Times in 11 Posts
    John,

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

    Code:
    Change:
    Add-Content "C:\alex\Valuefound.txt" ($line + $fieldAppend)
    To:
    if(!(Select-String -Path "C:\alex\Valuefound.txt" -Pattern $textValue))
    {
    	Add-Content "C:\alex\Valuefound.txt" ($line + $fieldAppend)
    }
    else {
    	Add-Content "C:\alex\Duplicatefound.txt" $line
    }
    HTH

    P.S. Get better soon, RG
    Last edited by Cliff.H; 2014-11-20 at 08:52.

  7. #7
    Lounger
    Join Date
    Jan 2014
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thanks Cliff.H,

    Its giving error.
    Select-String : Cannot bind argument to parameter 'Pattern' because it is null.
    At C:\job\search2.ps1:18 char:59
    + if(!(Select-String -Path "C:\alex\Valuefound.txt" -Pattern <<<< $textValue))
    + CategoryInfo : InvalidData: ( [Select-String], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Mic rosoft.PowerShell.Commands.SelectStringCommand
    Awating reply.

  8. #8
    Lounger
    Join Date
    Dec 2009
    Location
    Gillingham, Dorset, UK
    Posts
    30
    Thanks
    0
    Thanked 13 Times in 11 Posts
    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.

  9. #9
    Lounger
    Join Date
    Dec 2009
    Location
    Gillingham, Dorset, UK
    Posts
    30
    Thanks
    0
    Thanked 13 Times in 11 Posts
    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.
    Attached Files Attached Files

  10. The Following User Says Thank You to Cliff.H For This Useful Post:

    John_2014 (2014-11-23)

  11. #10
    Lounger
    Join Date
    Jan 2014
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thanks Cliff.H, Its perfect and works like a charm.

    Thanks a lot.

  12. #11
    Lounger
    Join Date
    Jan 2014
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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:\alex\ExtractFromCSV.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.
    Last edited by John_2014; 2014-11-29 at 13:58.

  13. #12
    Lounger
    Join Date
    Dec 2009
    Location
    Gillingham, Dorset, UK
    Posts
    30
    Thanks
    0
    Thanked 13 Times in 11 Posts
    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:\alex\Errors.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.

  14. #13
    Lounger
    Join Date
    Jan 2014
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Cliff.H View Post
    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.
    Attached Files Attached Files

  15. #14
    Lounger
    Join Date
    Dec 2009
    Location
    Gillingham, Dorset, UK
    Posts
    30
    Thanks
    0
    Thanked 13 Times in 11 Posts
    John,

    Try this one. I hope it meets your requirements.
    Attached Files Attached Files

  16. The Following User Says Thank You to Cliff.H For This Useful Post:

    John_2014 (2014-12-09)

  17. #15
    Lounger
    Join Date
    Jan 2014
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thanks Cliff.H,

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

    Many Thanks & grateful to you.
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •