Loading Data With Powershell

Question

Loading Data With Powershell

cmille19

SSCertifiable

Points: 5970
More actions
December 11, 2008 at 5:01 am

#125749

Comments posted to this topic are about the item Loading Data With Powershell

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

corey lawson Hall of Fame Points: 3773 More actions · Answer 1

That's a good article, except for a couple of things.

Why do people still literally use commas as field separators (unless they have to, because the tool/function/utility doesn't support anything else)? Gaaaahhh! The example even uses the field separator parameter...

And, well, it's PowerShell. I'd rather do this stuff in INTERCAL, but to each their own...

Of course, BCP does this stuff as well...

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

corey lawson (12/15/2008)
That's a good article, except for a couple of things.
Why do people still literally use commas as field separators (unless they have to, because the tool/function/utility doesn't support anything else)? Gaaaahhh! The example even uses the field separator parameter...
And, well, it's PowerShell. I'd rather do this stuff in INTERCAL, but to each their own...
Of course, BCP does this stuff as well...

In search of enlightenment, what should we be using as field separators?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Anipaul SSC-Insane Points: 24681 More actions · Answer 3

Anipaul

SSC-Insane

Points: 24681

December 16, 2008 at 10:40 am

#913327

Good Article ...

thermanson Mr or Mrs. 500 Points: 523 More actions · Answer 4

I have found that TAB separated files seem to cause the least number of problems. Unfortunately TABs aren't always possible, if you have to work with a file created by a mainframe or a file that you receive by FTP then TABs probably aren't an option.

It is possible to Bulk Insert a file starting at the "nth" row without creating a format file. Simply use the firstrow option.

Bulk Insert #temp

From 'c:\data.txt'

With (firstrow = 2)

cmille19 SSCertifiable Points: 5970 More actions · Answer 5

thermanson (12/16/2008)
I have found that TAB seperated files seem to cause the least number of problems. Unfortunately TABs aren't always possible, if you have to work with a file created by a mainframe or a file that you receive by FTP then TABs probably aren't an option.
It is possible to Bulk Insert a file starting at the "nth" row with out creating a format file. Simply use the firstrow option.
Bulk Insert #temp
From 'c:\data.txt'
With (firstrow = 2)

Thanks for the tip about about firstrow. Unfortunatley the built in Powershell cmdlet, Export-CSV only supports a comma as a separator.

David Ly-381060 SSC Enthusiast Points: 118 More actions · Answer 6

when i ran:

./diskusage.ps1 sydw1255|export-CSV -path ./diskusage.csv -noTypeInformat

I get this error:

An empty pipe element is not permitted.

At F:\Working Folders\PowerShell\diskusage.ps1:4 char:2

cmille19 SSCertifiable Points: 5970 More actions · Answer 7

David Ly (12/16/2008)
when i ran:
./diskusage.ps1 sydw1255|export-CSV -path ./diskusage.csv -noTypeInformat
I get this error:
An empty pipe element is not permitted.
At F:\Working Folders\PowerShell\diskusage.ps1:4 char:2

David,

Try executing just ./diskusage.ps1 by itself and see if you get any output then add space pipe space export-csv -path ./diskusage.csv -noTypeInformat as shown in the article.

Verify your execution policy in Powershell also by running get-executionpolicy. Set your policy to remotesigned by running set-executionpolicy remotesigned if it is not already.

NevJam SSC Veteran Points: 202 More actions · Answer 8

David Ly (12/16/2008)
when i ran:
./diskusage.ps1 sydw1255|export-CSV -path ./diskusage.csv -noTypeInformat
I get this error:
An empty pipe element is not permitted.
At F:\Working Folders\PowerShell\diskusage.ps1:4 char:2

I get the same error - I believe it's due to the the following pipe (bold)?

Get-WmiObject -computername "$ComputerName" Win32_LogicalDisk -filter "DriveType=3"

|

foreach { add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))

I can't use Get-DiskUsage.ps1 if I run it on it's own. Any help appreciated. I would really like to get this working. I'm hoping to use it for all manner of wmi queries so I can store them in a db. However I haven't managed to get any of the examples listed working at all.

Cheers

cmille19 SSCertifiable Points: 5970 More actions · Answer 9

There are some extra returns in the code in the article. Try this instead (notice the line breaks are different):

param ( [string]$ComputerName = "." )

Get-WmiObject -computername "$ComputerName" Win32_LogicalDisk -filter "DriveType=3" |

foreach { add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))

add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Size/1GB),2))

add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2))

add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Size) * 100),2)) -passThru } |

Select UsageDT, SystemName, DeviceID, VolumeName, SizeGB, FreeGB, PercentFree

NevJam SSC Veteran Points: 202 More actions · Answer 10

Great thanks that works now - I'm having trouble getting sqlcmd to work correctly so I've started looking at BCP.exe

I've managed to rewrite your script so that my wmi results are output to a .xls file

Does anyone know the BCP command to get a local xls file loaded into a remote sql table?

I can manage to get the command working if I copy the data out of the .xls and into a .txt:

db = TEST

table = LogMemory

server = lonwintel01

This bcp command works for .txt file - does anyone know the correct switches for a .xls file:

bcp TEST..LogMemory in "c:\ps\scripts\import.txt" -S lonwintel01 -T

cmille19 SSCertifiable Points: 5970 More actions · Answer 11

NevJam (1/14/2009)
Great thanks that works now - I'm having trouble getting sqlcmd to work correctly so I've started looking at BCP.exe
I've managed to rewrite your script so that my wmi results are output to a .xls file
Does anyone know the BCP command to get a local xls file loaded into a remote sql table?
I can manage to get the command working if I copy the data out of the .xls and into a .txt:
db = TEST
table = LogMemory
server = lonwintel01
This bcp command works for .txt file - does anyone know the correct switches for a .xls file:
bcp TEST..LogMemory in "c:\ps\scripts\import.txt" -S lonwintel01 -T

BCP does not work with Excel files. Keep in mind the T-SQL shown in the article for sqlcmd could have been executed with Query Analyzer, SSMS or osql instead of sqlcmd

BULK INSERT dbautility.dbo.DiskUsage FROM 'C:\Users\u00\bin\diskusage.csv'

WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = ''

You can use DTS or SSIS with Excel files.

cmille19 SSCertifiable Points: 5970 More actions · Answer 12

Blogged about Executing Powershell in SQL Server . Using the technique described in the blog post you have a fourth method to load Powershell data into SQL Server. The approach uses xp_cmdshell and XQUERY to return the result set.

cmille19 SSCertifiable Points: 5970 More actions · Answer 13

Created a Powershell script to import Excel 2007 or 2003 to a SQL table, Import-ExcelToSQL

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 14

cmille19 (1/8/2009)
There are some extra returns in the code in the article. Try this instead (notice the line breaks are different):
param ( [string]$ComputerName = "." )
Get-WmiObject -computername "$ComputerName" Win32_LogicalDisk -filter "DriveType=3" |
foreach { add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))
add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Size/1GB),2))
add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2))
add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Size) * 100),2)) -passThru } |
Select UsageDT, SystemName, DeviceID, VolumeName, SizeGB, FreeGB, PercentFree

Thanks...though its so silly this article is still not updated, to get a 5 start rating known issue should have been fixed and a full set of files should have been attached for anyone to try quickly...

So i am able to export, lets see how does import do...

Prakash

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA