Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need Powershell Script:

Posted on 2014-02-10
9
Medium Priority
?
500 Views
Last Modified: 2014-02-22
Greeting Experts,

      I am looking for a PowerShell script to import excel.csv file over to a Microsoft SQL 2012 Express database. The file is labeled with the following below.  The excel file is identified by the date on the end “02-10-2014s” of each file. What I am looking for is to identify the name of the file and select it based on the date at the end of the file name…. does anybody have a script that can do this process…

Name of File:
JT_VSE__Summary_of_Threats_Detected_in_the_Last_24_Hour_02-10-2014s.csv”
0
Comment
Question by:Mike
  • 4
  • 3
  • 2
9 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 39850409
Something like this?
get-item -path *.csv | where-object {$_.pschildname -match "_\d{2}\-\d{2}\-\d{4}"}

Open in new window

0
 
LVL 72

Expert Comment

by:Qlemo
ID: 39851644
I'm not clear about your goal here. Do you want to have
a) all files matching a date pattern style in their filename (as aikimark demonstrated)
b) a single file matching exactly one specific date?

And as soon as you have that file, what to do?
0
 

Author Comment

by:Mike
ID: 39853466
I looking for a script that will scan a file directory with the file name " JT_VSE__Summary_of_Threats_Detected_in_the_Last_24_Hour_MM-dd-YYYY.csv" and choose the one it needs from the date on the end of the file name... i.e. the current date...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 72

Expert Comment

by:Qlemo
ID: 39853554
dir "C:\Temp\EE\ JT_VSE__Summary_of_Threats_Detected_in_the_Last_24_Hour_$(get-date -format 'MM-dd-yyyy').csv"

Open in new window

will detect the current file.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39853600
Where/how does the trailing "s" come into play?
0
 

Author Comment

by:Mike
ID: 39870607
Yes, that is what I am looking for Qlemo... I just need to set it up in a way that I can give the script name of the server, Database, and Table name... so it can upload everything for just that day only and stop.

Sorry for replying back so late... thank you for your help...
0
 

Author Comment

by:Mike
ID: 39870628
The Trailing "s" on the end of the file is part of the name of the file that is created by the Vender software. when it creates the reports everyday..
0
 
LVL 72

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 39870801
You'll have to make sure the target table has (at least) all the columns contained in the CSV file.
I will show a solution based on code of Hey, Scripting Guy.
$Instance = 'mssql\express'
$Database = 'ThreatsDB'
$table    = 'Threats'
# Use the following for SQL Server Authentication instead of Windows Authentication
# $User = 'User1'
# $Pwd  = 'Pwd1'
$Data = Import-CSV "C:\Temp\EE\JT_VSE__Summary_of_Threats_Detected_in_the_Last_24_Hour_$(get-date -format 'MM-dd-yyyy').csv" 


<# --- Don't need to change anything down here --- #>

####################### 
function Get-Type 
{ 
    param($type) 
 
$types = @( 
'System.Boolean', 
'System.Byte[]', 
'System.Byte', 
'System.Char', 
'System.Datetime', 
'System.Decimal', 
'System.Double', 
'System.Guid', 
'System.Int16', 
'System.Int32', 
'System.Int64', 
'System.Single', 
'System.UInt16', 
'System.UInt32', 
'System.UInt64') 
 
    if ( $types -contains $type ) { 
        Write-Output "$type" 
    } 
    else { 
        Write-Output 'System.String' 
         
    } 
} #Get-Type 
 
####################### 
<# 
.SYNOPSIS 
Creates a DataTable for an object 
.DESCRIPTION 
Creates a DataTable based on an objects properties. 
.INPUTS 
Object 
    Any object can be piped to Out-DataTable 
.OUTPUTS 
   System.Data.DataTable 
.EXAMPLE 
$dt = Get-psdrive| Out-DataTable 
This example creates a DataTable from the properties of Get-psdrive and assigns output to $dt variable 
.NOTES 
Adapted from script by Marc van Orsouw see link 
Version History 
v1.7 - Chad Miller - Fixed issue with IsArray 
.LINK 
http://thepowershellguy.com/blogs/posh/archive/2007/01/21/powershell-gui-scripblock-monitor-script.aspx 
#> 
function Out-DataTable 
{ 
    [CmdletBinding()] 
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject) 
 
    Begin 
    { 
        $dt = new-object Data.datatable   
        $First = $true  
    } 
    Process 
    { 
        foreach ($object in $InputObject) 
        { 
            $DR = $DT.NewRow()   
            foreach($property in $object.PsObject.get_properties()) 
            {   
                if ($first) 
                {   
                    $Col =  new-object Data.DataColumn   
                    $Col.ColumnName = $property.Name.ToString()   
                    if ($property.value) 
                    { 
                        if ($property.value -isnot [System.DBNull]) { 
                            $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 
                         } 
                    } 
                    $DT.Columns.Add($Col) 
                }   
                if ($property.Gettype().IsArray) { 
                    $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 
                }   
               else { 
                    $DR.Item($property.Name) = $property.value 
                } 
            }   
            $DT.Rows.Add($DR)   
            $First = $false 
        } 
    }  
      
    End 
    { 
        Write-Output @(,($dt)) 
    } 
 
} #Out-DataTable


# CSV to SQL Table:

$conn = new-object System.Data.SqlClient.SQLConnection 
$conn.ConnectionString = "Server=$Instance; Database=$Database; Trusted_Connection={0}" -f $(
  if ($User) { "false; User ID=$User; Password=$Pwd" } else { "true" })
try 
{ 
  $conn.Open() 
  $bulkCopy = new-object Data.SqlClient.SqlBulkCopy $conn
  $bulkCopy.DestinationTableName = $table
  $bulkCopy.BatchSize = 100
# $bulkCopy.BulkCopyTimeout = $QueryTimeOut 
  $bulkCopy.WriteToServer($Data | Out-DataTable) 
  $conn.Close() 
} 
catch 
{ 
  $ex = $_.Exception 
  Write-Error "$ex.Message" 
  continue 
} 

Open in new window

You should have to change nothing more than the vars at the very beginning of the script.
0
 

Author Closing Comment

by:Mike
ID: 39879797
This worked .... thank you Qlemo....
0

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Loops Section Overview
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question