Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Loading Data With Powershell Expand / Collapse
Author
Message
Posted Thursday, December 11, 2008 5:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701
Comments posted to this topic are about the item Loading Data With Powershell


Post #617767
Posted Monday, December 15, 2008 11:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:26 AM
Points: 155, Visits: 359
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...
Post #620186
Posted Tuesday, December 16, 2008 8:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:42 PM
Points: 2,677, Visits: 19,269
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #620466
Posted Tuesday, December 16, 2008 10:40 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,363, Visits: 1,391
Good Article ...


Post #620627
Posted Tuesday, December 16, 2008 10:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 1:16 PM
Points: 43, Visits: 287
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)

Post #620631
Posted Tuesday, December 16, 2008 11:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701
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.



Post #620647
Posted Tuesday, December 16, 2008 5:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 1:18 AM
Points: 8, Visits: 444
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
Post #620835
Posted Wednesday, December 17, 2008 6:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701
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.



Post #621144
Posted Thursday, January 8, 2009 8:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 17, 2012 8:35 AM
Points: 6, Visits: 22
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
Post #632470
Posted Thursday, January 8, 2009 4:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701
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



Post #632967
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse