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


««12

Loading Data With Powershell Expand / Collapse
Author
Message
Posted Wednesday, January 14, 2009 3:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 27, 2009 2:15 AM
Points: 6, Visits: 21
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


Post #636100
Posted Wednesday, January 14, 2009 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 15, 2010 9:38 AM
Points: 236, Visits: 534
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.



Post #636170
Posted Saturday, April 25, 2009 8:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 15, 2010 9:38 AM
Points: 236, Visits: 534
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.


Post #704455
Posted Sunday, April 26, 2009 1:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 15, 2010 9:38 AM
Points: 236, Visits: 534
Created a Powershell script to import Excel 2007 or 2003 to a SQL table, Import-ExcelToSQL


Post #704645
Posted Monday, February 15, 2010 4:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 01, 2010 3:17 PM
Points: 339, Visits: 216
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
Sr Database Infrastructure Consultant
Post #865785
« Prev Topic | Next Topic »

««12

Permissions Expand / Collapse