Solved

Need Powershell Script:

Posted on 2014-02-10
9
440 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 69

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 69

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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This article will help you understand what HashTables are and how to use them in PowerShell.
Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

792 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