Solved

Need Powershell Script:

Posted on 2014-02-10
9
413 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:amstoots
  • 4
  • 3
  • 2
9 Comments
 
LVL 45

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 68

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:amstoots
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
 
LVL 68

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 45

Expert Comment

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

Author Comment

by:amstoots
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:amstoots
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 68

Accepted Solution

by:
Qlemo earned 500 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:amstoots
ID: 39879797
This worked .... thank you Qlemo....
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Synchronize a new Active Directory domain with an existing Office 365 tenant
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now