none
Powershell Splitting file name issue RRS feed

  • Question

  • Hey guys,

           I have an issue with splitting a file name and assigning the splits to variables to i can write them to a SQL database.

    my sample file name is: I12345L98765.tif where this variable is $name

    what im using is 

          $splitup=$name -split'[I,L,.]'
          $item=$splitup[1]
          $lot=$splitup[2]

    Then i'm trying to write the $item and $lot to a SQL database. 

    Two problems i'm having is the the $lot doesnt always seem to get data and secondly if my sample file name has to characters the same ie: I12345LL98765.tif the variable never gets passed at all.

    If hope i was clear in my explanation. Any help is greatly appreciated.

    Thanks

    Rich T.

    


    Rich Thompson

    Monday, June 16, 2014 11:14 AM

Answers

  • function Invoke-SQL {
        #http://stackoverflow.com/questions/8423541/how-do-you-run-a-sql-server-query-from-powershell
        param(
            [string] $dataSource = ".\SQLEXPRESS",
            [string] $database = "MasterData",
            [string] $sqlCommand = $(throw "Please specify a query."),
            [string] $security = "SSPI"
          )
    
        $connectionString = "Data Source=$dataSource; " +
                "Integrated Security=$security; " +
                "Initial Catalog=$database"
    
        $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
        $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
        $connection.Open()
    
        $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
        $dataset = New-Object System.Data.DataSet
        $adapter.Fill($dataSet) | Out-Null
    
        $connection.Close()
        $dataSet.Tables
    
    }
    
    #### SQL Database Variables ####
          $SQLServerName="pvsntdb33"
          $SQLDatabase="adagecopy_daily"
    
    #### DEFINE WATCH FOLDERS AND DEFAULT FILE EXTENSION TO WATCH FOR ####
          $cofa_folder = '\\cpsfs001\Data_pvs\TestCofA'
          $filter = '*.tif'
          $cofa = New-Object IO.FileSystemWatcher $cofa_folder, $filter -Property @{ IncludeSubdirectories = $false; NotifyFilter = [IO.NotifyFilters]'FileName, LastWrite' }
    
    #### CERTIFICATE OF ANALYSIS AND PACKAGE SHIPPER PROCESSING ####
    Register-ObjectEvent $cofa Created -SourceIdentifier CofA_PackageShipper -Action {
        $name = $Event.SourceEventArgs.Name
        if ($name -match "^I(?<item>[\d]+)L(?<lot>[\d]+)\.(?<ext>.{2,4})$") {
            $item = $Matches['item']
            $lot = $Matches['lot']
        } else {
            throw "name did not match"
        }
    
        #### WRITE THE TABLES TO THE SQL DATABASE ####
        Invoke-SQL -dataSource $SQLServerName -database $SQLDatabase -sqlCommand "INSERT INTO in_cofa_pvs (in_item_key, in_lot_key, imgfileName, in_cofa_crtdt) VALUES ('$item','$lot','$name','$timestamp')"
    }

    **This code was not tested** as I haven't a sql server handy.



    If you found this post helpful, please "Vote as Helpful". If it answered your question, remember to "Mark as Answer"
    MCC & PowerShell enthusiast
    http://oliver.lipkau.net/blog

    Monday, June 16, 2014 2:05 PM
    Moderator

All replies

  • Hi Rich,

    you can only reliably split a string if you know the pattern will always be correct. Example:

    I12345LL98765.tif will be split into 5 parts:

    $Splitup[0] = 
    $Splitup[1] = 12345
    $Splitup[2] = 
    $Splitup[3] = 98765
    $Splitup[4] = tif

    You can either work to prevent the creation of names that bust the pattern, or you need to improve your pattern recognition. Or you find a way to react to the values that bust the pattern. The choice is yours.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Monday, June 16, 2014 11:47 AM
  • I would prefer to do a more strict validation on the naming pattern, personally. I rarely use -split in situations like this:

    $name = 'I12345L98765.tif'
    
    if ($name -match '^I(\d+)L(\d+)')
    {
        $item = $matches[1]
        $lot = $matches[2]
    
        "Item: $item , Lot: $lot"
    }
    else
    {
        Write-Error "Name '$name' did not match the expected pattern."
    }
    
    


    This way, the name has to begin with I<digits>L<digits> , and any other naming pattern will be ignored with an error.

    Monday, June 16, 2014 12:00 PM
  • I agree with david.

    I would change the regex code a bit:

    $name = "I12345L98765.tif"
    $name -match "^I(?<item>[\d]+)L(?<lot>[\d]+)\.(?<ext>.{2,4})$"
    $matches

    RegEx explained here: http://regex101.com/r/eQ4sK2


    If you found this post helpful, please "Vote as Helpful". If it answered your question, remember to "Mark as Answer"
    MCC & PowerShell enthusiast
    http://oliver.lipkau.net/blog

    Monday, June 16, 2014 1:41 PM
    Moderator
  • ok guys i had an intern figure out the file name issue for me. Not sure if this is the best way to do it. I will investigate all your solutions they ALL look better then what i have. Now my second problem. I'm trying to write all this data to a SQL database and in my write-host everything comes up perfect, but nothing ever gets written to the SQL database.

    here's all my code. The end goal of this is to have FileSystemWatcher write a SQL Table with info every time a file meeting the criteria is created in the directory. I'm not sure where im going wrong here.

    Thoughts ?

    #### SQL Database Variables ####
          $SQLServerName="pvsntdb33"
          $SQLDatabase="adagecopy_daily"

    #### DEFINE WATCH FOLDERS AND DEFAULT FILE EXTENSION TO WATCH FOR ####
          $cofa_folder = '\\cpsfs001\Data_pvs\TestCofA'
          $filter = '*.tif'
          $cofa = New-Object IO.FileSystemWatcher $cofa_folder, $filter -Property @{ IncludeSubdirectories = $false; NotifyFilter = [IO.NotifyFilters]'FileName, LastWrite' }

    #### CERTIFICATE OF ANALYSIS AND PACKAGE SHIPPER PROCESSING ####
        Register-ObjectEvent $cofa Created -SourceIdentifier CofA_PackageShipper -Action {
    $name = $Event.SourceEventArgs.Name
    $changeType = $Event.SourceEventArgs.ChangeType
    $timeStamp = $Event.TimeGenerated
    $pos = $name.IndexOf(".")
    $left = $name.substring(0, $pos)
    $pos = $left.IndexOf("L")
    $tempItem = $left.substring(0, $pos)
    $lot = $left.Substring($pos + 1)
    $item = $tempItem.Substring(1)

    #### WRITE THE TABLES TO THE SQL DATABASE ####
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$SQLServerName; Initial Catalog=$SQLDatabase; Integrated Security=TRUE")
    $conn.Open()
    $insert_stmt = "INSERT INTO in_cofa_pvs (in_item_key, in_lot_key, imgfileName, in_cofa_crtdt) VALUES ('$item','$lot','$name','$timestamp')"
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $insert_stmt
    $cmd.ExecuteNonQuery()
    $conn.Close()
    }


    Rich Thompson

    Monday, June 16, 2014 1:52 PM
  • The script block that you pass to the Action parameter of Register-ObjectEvent can read variables in the Global scope, but not variables in the script scope.  As a result, $SQLDatabase and $SQLServerName have no values when the events fire.

    You can fix this by either defining your two variables as Global, or just embedding the values right into the script block.  Which approach you use is up to you; I would mainly base that decision on whether you think something might come along and change the values of those variables while the session is still running.

    $global:SQLServerName="pvsntdb33"
    $global:SQLDatabase="adagecopy_daily"
    
    # Or:
    
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=pvsntdb33; Initial Catalog=adagecopy_daily; Integrated Security=TRUE")

    There are ways to leave the values in variables, and dynamically create a script block which has the values hard-coded, but that's probably more complicated than you want to get into right now.
    Monday, June 16, 2014 2:03 PM
  • function Invoke-SQL {
        #http://stackoverflow.com/questions/8423541/how-do-you-run-a-sql-server-query-from-powershell
        param(
            [string] $dataSource = ".\SQLEXPRESS",
            [string] $database = "MasterData",
            [string] $sqlCommand = $(throw "Please specify a query."),
            [string] $security = "SSPI"
          )
    
        $connectionString = "Data Source=$dataSource; " +
                "Integrated Security=$security; " +
                "Initial Catalog=$database"
    
        $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
        $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
        $connection.Open()
    
        $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
        $dataset = New-Object System.Data.DataSet
        $adapter.Fill($dataSet) | Out-Null
    
        $connection.Close()
        $dataSet.Tables
    
    }
    
    #### SQL Database Variables ####
          $SQLServerName="pvsntdb33"
          $SQLDatabase="adagecopy_daily"
    
    #### DEFINE WATCH FOLDERS AND DEFAULT FILE EXTENSION TO WATCH FOR ####
          $cofa_folder = '\\cpsfs001\Data_pvs\TestCofA'
          $filter = '*.tif'
          $cofa = New-Object IO.FileSystemWatcher $cofa_folder, $filter -Property @{ IncludeSubdirectories = $false; NotifyFilter = [IO.NotifyFilters]'FileName, LastWrite' }
    
    #### CERTIFICATE OF ANALYSIS AND PACKAGE SHIPPER PROCESSING ####
    Register-ObjectEvent $cofa Created -SourceIdentifier CofA_PackageShipper -Action {
        $name = $Event.SourceEventArgs.Name
        if ($name -match "^I(?<item>[\d]+)L(?<lot>[\d]+)\.(?<ext>.{2,4})$") {
            $item = $Matches['item']
            $lot = $Matches['lot']
        } else {
            throw "name did not match"
        }
    
        #### WRITE THE TABLES TO THE SQL DATABASE ####
        Invoke-SQL -dataSource $SQLServerName -database $SQLDatabase -sqlCommand "INSERT INTO in_cofa_pvs (in_item_key, in_lot_key, imgfileName, in_cofa_crtdt) VALUES ('$item','$lot','$name','$timestamp')"
    }

    **This code was not tested** as I haven't a sql server handy.



    If you found this post helpful, please "Vote as Helpful". If it answered your question, remember to "Mark as Answer"
    MCC & PowerShell enthusiast
    http://oliver.lipkau.net/blog

    Monday, June 16, 2014 2:05 PM
    Moderator