BATCHman Uses PowerShell to Import a CSV File into His SQL Server

BATCHman Uses PowerShell to Import a CSV File into His SQL Server

  • Comments 11
  • Likes

Summary: Windows PowerShell superhero BATCHman quickly imports CSV data into his SQL Server.

 

Microsoft Scripting Guy Ed Wilson here. Sean Kearney is back today with Episode 6 of the BATCHman series.

Note   You can read all gripping, engaging, informative, and downright funny BATCHman episodes.

 

BATCHman and Cmdlet logo

Whenever trouble happens in systems and people will call,
and darkness rolls out causing your fall,
Creatures of bits roam in the night,
Shine to the sky, the bright bluish light,

and call to…BATCHman !

…and, oh yes, his sidekick Boy Blunder Cmdlet, too.

 

In Today’s Episode, BATCHman Encounters Cmdlet’s Bad Field Notes!

So after a long week of crusading, standing on Telephones, being blinded by overcharged BATCHsignals, and generally keeping those ne’er-do-well evildoers at bay, BATCHman and Cmdlet are now about to upload all of Cmdlet’s notes to the BATCHcomputer. (No, forget that thought. It does not BATCH process with COBOL!)

“So, Cmdlet, let’s get those notes off your local SQL Express database and merge them from the Batch applet I created for taking field notes. Time to compile some data!”

“Uhhhh,” is Cmdlet’s only response. “What’s a Batch applet?”

BATCHman stands there scratching thoughtfully on his chin. “Why that would be the application I provided to you to keep field notes about all those evil villains we planned to encounter. I wrote it specifically to just simply sync up with the SQL 2008 R2 batch database on the batch computer.”

Cmdlet shuffled his feet. “Sooooo, not the batch Excel sheet?”

A pause of realization. “No.”

More shuffling by our Boy Blunder. “So, not Notepad? Not the XML editor?”

BATCHman just stood there. His beautiful applet, enabled with Windows PowerShell cmdlets, the GUI written with PrimalScript with some tweaking from PowerBoots. And it has gone unused.

"Cmdlet, can I see your notes?”

Boy Blunder hands over his Slate PC to BATCHman. Quickly, BATCHman keys into Windows PowerShell.

GET-CHILDITEM $HOME\Documents\CmdletNotes

The output produces some dismay to the BATCHman.

BADGUYS.CSV

“A CSV file?”

“Well, we were in the heat of battle, and I just quickly grabbed what was available to me. I thought it would be easy to search and sort and I…I…OH PLEASE! PLEASE BE MERCIFUL! PLEASE DON’T DECOMPILE ME!!!” Cmdlet wailed

BATCHman shook his head with a smile and tapped his young friend gently on the shoulder. “It was a mistake and an honest one. But remember, we have Windows PowerShell. We just need to convert this data into something the batch database will recognize. So let’s take a quick look at your CSV file.”

IMPORT-CSV BADGUYS.CSV

Image of CSV file contents

“Well this isn’t so bad. All we need to do is match these fields with ones from the BATCHman database. Because Windows PowerShell will work with SQL, we could literally import your CSV file into the database. First, we’ll add the SQL snap-ins to allow us to work with SQL Server from Windows PowerShell.”

ADD-PSSNAPIN SqlServerProviderSnapin100
ADD-PSSNAPIN SqlServerCmdletSnapin100

“With these added we can use Invoke-SQLCmd, which will allow us to pass SQL queries directly from Windows PowerShell, returning the results as an object. As an example, here is our current query. Because this is a small database, we’ll just use this. This presumes that the user, me in this case, has access.”

Invoke-Sqlcmd -Query 'SELECT * FROM BATCHmanDB.dbo.BATCHmanCrimeTable' -ServerInstance localhost

Cmdlet looked as the output appeared on the screen. You could tell by the single row of data they were very early into the crime fighting game, and BATCHman started at an early age.

Image of query output

“But BATCHman? How does this help us? All this did was show us information in a SQL database! We need to take my data and place that in SQL! Oh please! Just let me retype it!”

BATCHman smiled. “Because little buddy, this is a Windows PowerShell object. I can do this, too.”

$DATA=(Invoke-Sqlcmd -Query 'SELECT * FROM BATCHman.dbo.BATCHmanCrimeTable' -ServerInstance localhost)

$DATA.CriminalName

Cmdlet looked and realized he was now looking at an object in Windows PowerShell as normal. A light lit in his eyes.

“Wait! Do you mean I could put Windows PowerShell objects in an SQL query and populate the BATCHman database?”

“Exactly!” decried BATCHman, nearly poking Cmdlet in the eye. So first, let’s look at your file. It appears Villain will be the same as Criminal Name, Appearance matches Description, but the other two are dead on the nose. There are some fields you don’t have, such as Solved, but these are not required in the database. We can edit these later if we choose. Our only challenge now is to build the query.”

BATCHman pulled open his slate and launched SQL Server Management Studio.

“A very simple SQL INSERT query to add a row to the BATCHman database would look like this.”

INSERT INTO [BATCHmanDB].[dbo].[BATCHmanCrimeTable]
([CriminalName],[Description],[Problem],[Solution])
VALUES
(‘SomeEvilGuy’,’Looks Awful Funny’,’Something bad’,’Something Good’)
GO

“We can easily make this a value in Windows PowerShell like this,” BATCHman noted.

$SQLQUERY=”INSERT INTO [BATCHmanDB].[dbo].[BATCHmanCrimeTable] ([CriminalName],[Description],[Problem],[Solution]) VALUES (‘SomeEvilGuy’,’Looks Awful Funny’,’Something bad’,’Something Good’)”

The eyes of Cmdlet opened wide. “BATCHman! That’s almost unreadable! Couldn’t I just do this?”

$SQLHEADER=”INSERT INTO [BATCHmanDB].[dbo].[BATCHmanCrimeTable] ([CriminalName],[Description],[Problem],[Solution])“

$SQLVALUES=”VALUES (‘SomeEvilGuy’,’Looks Awful Funny’,’Something bad’,’Something Good’)”

$SQLQUERY=$SQLHEADER+$SQLVALUES

BATCHman beamed. “Excellent! You’ve been learning well. Now let’s take your notes and begin importing them into the database. We could just write it in a single line, but let’s take it one piece at a time. Let’s store your CSV file in a variable.”

$DATA=IMPORT-CSV BADGUYS.CSV

“Now we’ll step through each line with a Foreach-Object.”

FOREACH ($LINE in $DATA)

{
$Criminal=$Line.Villain
$Description=$Line.Appearance
$Problem=$Line.Problem
$Solution=$Line.Solution

}

Cmdlet watched the CSV flow on the screen. “So, BATCHman, can we just substitute the Windows Powershell variables for the same samples in the query?”

BATCHman nodded. “With one exception: we need to make sure the single quotes are still surrounding the data because the SQL query is expecting them. So we will populate the values like this, remembering the quotation mark is a special character and will need an escape before each reference.”

$Criminal="`'"+$Line.Villain+"`'"
$Description="`'"+$Line.Appearance"`'"
$Problem="`'"+$Line.Problem"`'"
$Solution="`'"+$Line.Solution"`'"

“So we can now just replace them in the query? Do you mean like this?” Cmdlet quickly edits the query lines.

$SQLHEADER=”INSERT INTO [BATCHmanDB].[dbo].[BATCHmanCrimeTable] ([CriminalName],[Description],[Problem],[Solution])“

$SQLVALUES=”VALUES ($Criminal,$Description,$Problem,$Solution)”

$SQLQUERY=$SQLHEADER+$SQLVALUES

“Correct, now we drop that into our script so that we end up with this.”

$DATA=IMPORT-CSV BADGUYS.CSV

FOREACH ($LINE in $DATA)

{
$Criminal="`'"+$Line.Villain+"`'"
$Description="`'"+$Line.Appearance+"`'"
$Problem="`'"+$Line.Problem+"`'"
$Solution="`'"+$Line.Solution+"`'"

$SQLHEADER=”INSERT INTO [BATCHmanDB].[dbo].[BATCHmanCrimeTable] ([CriminalName],[Description],[Problem],[Solution])“

$SQLVALUES=”VALUES ($Criminal,$Description,$Problem,$Solution)”

$SQLQUERY=$SQLHEADER+$SQLVALUES

Invoke-Sqlcmd –Query $SQLQuery -ServerInstance localhost

}

They executed the script, quickly populating the BATCHman database with the needed data. Cmdlet wiped the sweat off his face with a pair of Vibram shoes he found by the door.

“No sweat, little buddy. With Windows PowerShell, almost any problem can be eliminated. Now, let me show you the cool features for taking your notes.”

 

Sean, many thanks for sharing these episodes of BATCHman. Everyone, join us tomorrow as guest blogger Thiyagu talks about Exchange Best Practice Analyzer XML Parser.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

 

 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • You could just use the T-SQL BULK insert statement to directly load a CSV or any delimited file into SQL Server. In addition use here strings to avoid having to escape single quotes. Here's a completed script I've tested:

    $SQLQuery = @"

    BULK INSERT BATCHmanDB.dbo.BATCHmanCrimeTable FROM '$HOME\Documents\CmdletNotes\BADGUYS.CSV'

     WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

    "@

    Invoke-Sqlcmd –Query $SQLQuery -ServerInstance localhost

  • Love it!  Here at "Hey Scripting Guy" it's not just about a little learning and entertainment, it's about conversation and sharing ideas.  Thanks Chad! Separately we are strong.  Combined.  Our powers are MIGHTY! :)

    Cheers

    "The BATCHman"

  • You know? With all of these accolades I would be surprised if that wasn't Oscar talk we are hearing out here on the frontier.

    Is this true?

    Inquiring minds want to know....

  • Got stuck here... what's it all about?

    PS C:\Documents and Settings\ben> add-pssnapin sqlserverprovidersnapin100

    Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version 2.

    At line:1 char:13

    + add-pssnapin <<<<  sqlserverprovidersnapin100

       + CategoryInfo          : InvalidArgument: (sqlserverprovidersnapin100:String) [Add-PSSnapin], PSArgumentException

       + FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

    PS C:\Documents and Settings\ben> add-pssnapin sqlservercmdletsnapin100

    Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version 2.

    At line:1 char:13

    + add-pssnapin <<<<  sqlservercmdletsnapin100

       + CategoryInfo          : InvalidArgument: (sqlservercmdletsnapin100:String) [Add-PSSnapin], PSArgumentException

       + FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

  • @Ben

    In order to use the SQL Server Snapins you'll need the SQL Server Management 2008 Management Studio or higher installed on the computer in question.  Sorry I forgot to mention that.  I think Cmdlet got his fingers into my notes.

    BATCHman

  • Well done SQLman!

    @Chad: Brilliant idea! Even if I would have to use Oracle loader and look for a replacement for "invoke-sqlcmd" like "invoke-oraclecmd" ...

    @jrv: BATCHman will become more famous than any superhero before!

    @Sean: Did you already notice, that "honor" is always accompained with increasing work to do? ( I think the honor is Ed's trick to share his daily work with others :_)

    @Ben: I've been caught by the same problem! It took some minutes to get it right! And i ended up with another gotcha: I was running the 32 bit version of PS-ISE which won't work with the 64 bit SQl 2008R2 Express edition's snapins!

    One last thing: I chnged the solutionm to use a here string like chad did ... but without using the bulk load feature. If you are interested, it looks like that:

    $SqlCmd = @"

    INSERT INTO [BATCHmanDB].[dbo].[BATCHmanCrimeTable]

    ([CriminalName],[Description],[Problem],[Solution])

    VALUES ('{0}','{1}','{2}','{3}')

    "@

    import-csv i:\badguys.csv |

       ForEach-Object {

           Invoke-SqlCmd ($SqlCmd -f $_.Villain, $_.Appearance, $_.Problem, $_.Solution) -Server .\SqlExpress

       }

    Klaus.

  • @Klaus SQLMan! LOL

    Good work on the here string, I can't stress enough how here strings make working with SQL code in Powershell so much easier.

  • Could we have plain version of these batchman articles, without all the batman and robin stuff?

  • Everybody ... the BATCHman is simply a figure, you are the REAL heroes :)

    Jim... the BATCHman will never foresake an honest question.  Perhaps my good friend Sean might be so kind to paraphrase some of these later.  @Sean ?  Would you be so kind?  I'll buy you coffee.....

    The BATCHman

  • Batchman not the hero?  You are wrong!  BATCHman is a real SuperHero.  But be careful.  Super-heroism comes at a price.  Just wait until you have been up all night on the late night talk shows demonstrating your skills.  You will trade your cape for a pillow and blanky I am sure.

  • @jrv Nay good citizen.  Up all night?  That sounds very much like "The Energized Tech"

    BATCHman understands the value of "Sleep mode" :)

    BATCHman