?
Solved

Need Powershell Script:

Posted on 2014-02-10
9
Medium Priority
?
472 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

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
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 70

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: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 70

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A brief introduction to what I consider to be the best editor for PowerShell.
Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Suggested Courses

764 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