Solved

Need Powershell Script:

Posted on 2014-02-10
9
421 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
Synchronize a new Active Directory domain with an existing Office 365 tenant
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

895 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

17 Experts available now in Live!

Get 1:1 Help Now