SQL SERVER – Powershell – Importing CSV File Into Database – Video

Laerte Junior is my very dear friend and Powershell Expert. On my request he has agreed to share Powershell knowledge with us. Laerte Junior is a SQL Server MVP and, through his technology blog and simple-talk articles, an active member of the Microsoft community in Brasil. He is a skilled Principal Database Architect, Developer, and Administrator, specializing in SQL Server and Powershell Programming with over 8 years of hands-on experience. He holds a degree in Computer Science, has been awarded a number of certifications (including MCDBA), and is an expert in SQL Server 2000 / SQL Server 2005 / SQL Server 2008 technologies. Let us read the blog post in his own words.


I was reading an excellent post from my great friend Pinal about loading data from CSV files, SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video,   to SQL Server and was honored to write another guest post on SQL Authority about the magic of the PowerShell.

The biggest stuff in TechEd NA this year was PowerShell. Fellows, if you still don’t know about it, it is better to run. Remember that The Core Servers to SQL Server are the future and consequently the Shell. You don’t want to be out of this, right?

Let’s see some PowerShell Magic now. To start our tour, first we need to download these two functions from Powershell and SQL Server Master Jedi Chad Miller.Out-DataTable and Write-DataTable.

Save it in a module and add it in your profile. In my case, the module is called functions.psm1.

To have some data to play, I created 10 csv files with the same content. I just put the SQL Server Errorlog into a csv file and created 10 copies of it.

#Just create a CSV with data to Import. Using SQLErrorLog

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

$ServerInstance=new-object (Microsoft.SqlServer.Management.Smo.Server) $Env:Computername

$ServerInstance.ReadErrorLog() | export-csv-path“c:\SQLAuthority\ErrorLog.csv”-NoTypeInformation

for($Count=1;$Count-le 10;$count++)  {

      Copy-Item“c:\SQLAuthority\Errorlog.csv”“c:\SQLAuthority\ErrorLog$($count).csv”

}

Now in my path c:\sqlauthority, I have 10 csv files :

Now it is time to create a table. In my case, the SQL Server is called R2D2 and the Database is SQLServerRepository and the table is CSV_SQLAuthority.

CREATE TABLE [dbo].[CSV_SQLAuthority](
[LogDate] [datetime] NULL,
[Processinfo] [varchar](20) NULL,
[Text] [varchar](MAX) NULL
)

Let’s play a little bit.

I want to import synchronously all csv files from the path to the table:

#Importing synchronously

$DataImport=Import-Csv-Path ( Get-ChildItem“c:\SQLAuthority\*.csv”)

$DataTable=Out-DataTable-InputObject$DataImport

Write-DataTable-ServerInstanceR2D2-DatabaseSQLServerRepository-TableNameCSV_SQLAuthority-Data$DataTable

Very cool, right? Let’s do it asynchronously and in background using PowerShell  Jobs:

#If you want to do it to all asynchronously

Start-job-NameImportingAsynchronously `

-InitializationScript  {IpmoFunctions-Force-DisableNameChecking} `

-ScriptBlock {    `

$DataImport=Import-Csv-Path ( Get-ChildItem“c:\SQLAuthority\*.csv”)

$DataTable=Out-DataTable-InputObject$DataImport

Write-DataTable   -ServerInstance“R2D2″`

                  -DatabaseSQLServerRepository`

                  -TableNameCSV_SQLAuthority`

                  -Data$DataTable

            }

Oh, but if I have csv files that are large in size and I want to import each one asynchronously. In this case, this is what should be done:

Get-ChildItem“c:\SQLAuthority\*.csv” | % {

Start-job-Name“$($_)” `

-InitializationScript  {IpmoFunctions-Force-DisableNameChecking} `

-ScriptBlock { $DataImport=Import-Csv-Path$args[0]

               $DataTable=Out-DataTable-InputObject$DataImport

               Write-DataTable-ServerInstance“R2D2″`

                              -DatabaseSQLServerRepository`

                              -TableNameCSV_SQLAuthority`

                              -Data$DataTable

            } -ArgumentList$_.fullname

}

How cool is that?

Let’s make the funny stuff now. Let’s schedule it on an SQL Server Agent Job.

If you are using SQL Server 2012, you can use the PowerShell Job Step. Otherwise you need to use a CMDexec job step calling PowerShell.exe. We will use the second option.

First, create a ps1 file called ImportCSV.ps1 with the script above and save it in a path. In my case, it is in c:\temp\automation. Just add the line at the end:

Get-ChildItem“c:\SQLAuthority\*.csv” | % {

Start-job-Name“$($_)” `

-InitializationScript  {IpmoFunctions-Force-DisableNameChecking} `

-ScriptBlock { $DataImport=Import-Csv-Path$args[0]

               $DataTable=Out-DataTable-InputObject$DataImport

               Write-DataTable-ServerInstance“R2D2″`

                              -DatabaseSQLServerRepository`

                              -TableNameCSV_SQLAuthority`

                              -Data$DataTable

            } -ArgumentList$_.fullname

}

Get-Job | Wait-Job | Out-Null

Remove-Job -State Completed

Why? See my post Dooh PowerShell Trick–Running Scripts That has Posh Jobs on a SQL Agent Job

Remember, this trick is for  ALL scripts that will use PowerShell Jobs and any kind of schedule tool (SQL Server agent, Windows Schedule)

Create a Job Called ImportCSV and a step called Step_ImportCSV and choose CMDexec.

Then you just need to schedule or run it.

I did a short video (with matching good background music) and you can see it at:

That’s it guys. C’mon, join me in the #PowerShellLifeStyle. You will love it. If you want to check what we can do with PowerShell and SQL Server, don’t miss Laerte Junior LiveMeeting on July 18. You can have more information in : LiveMeeting VC PowerShell PASS–Troubleshooting SQL Server With PowerShell–English

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

9 thoughts on “SQL SERVER – Powershell – Importing CSV File Into Database – Video

  1. Pingback: SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video « SQL Server Journey with SQL Authority

  2. Good article. But using Import-CSV imports everything as text/strings and doesn’t maintain any object-types. How did you manage to import it with object types?

  3. Thank you and It helps me a lot.
    I have a question that
    How can I skip or say “error” on SQL server when the data does not match with data type?
    such as ID has nchar(2) on SQL server and I have csv file that one of data is ‘1234’ for ID.
    How can I have error sign or error report for that data in SQL server.
    It is ok that I can have separate excel file about all the errors.
    I just want to know which data got error.

    And make the script without stopping even there is an error.

    Some questions :) please reply if you have some time thank you

  4. When we start to take it Async I get an error on the following line.
    -InitializationScript {Ipmo Functions -Force -DisableNameChecking} `

    The error is saying that it doesn’t recognize the module called “Functions”, but this is the code you use to import from a ScriptBlock. I’m not clear as to why we’re specifying a keyword of “Functions”, but it doesn’t cause an error in your video and you use it all over.

    I tried on v2 (2003 Server 32bit) and v4 (win7 32bit) with the same results

    Error:
    Import-Module : The specified module ‘Functions’ was not loaded because no valid module file was found in any module directory.
    At line:1 char:5
    + Ipmo <<<< Functions -Force -DisableNameChecking
    + CategoryInfo : ResourceUnavailable: (Functions:String) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

    Full code:
    Start-job -Name ‘ImportingAsynchronously‘ `
    -InitializationScript {Ipmo Functions -Force -DisableNameChecking} `
    -ScriptBlock { `
    $DataImport= Import-Csv -Path ( Get-ChildItem “c:\SQLAuthority\*.csv”)
    $DataTable= Out-DataTable -InputObject $DataImport
    Write-DataTable -ServerInstance "Development02" `
    -Database "Reporting" `
    -TableName "CSV_SQLAuthority" `
    -Data $DataTable
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s