connect sql, get data, add column, export csv
Hi, I need to connect to SQL Server 2005 via remote powershell with SQL Authentication, run a query and export it to csv with different column headers. Bu I also want add one or two more columns with static information. For example the CSV should look like this: Column1,Column2,Column3,Column4 test@test.com,Password,mail.test.com,username1 johndoe@test.com,Password2,mail.test.com,username2 Column1, 2 and 4 is in sql and can be retrieved with sql query but Column3 must be added before exporting this query to csv. How can I do this? Those who are skilled in combat do not become angered, those who are skilled at winning do not become afraid. Thus the wise win before the fight, while the ignorant fight to win.
May 17th, 2013 11:52am

Column1, 2 and 4 is in sql and can be retrieved with sql query but Column3 must be added before exporting this query to csv. Hello, Instead of adding an additional column just for the export (note: you can add a new column only at the "end" of the existing column), you can query fix values in combination with a column alias for the export, for example: SELECT Column1 ,Column2 ,'' AS Column3 ,Column1 FROM yourTable Olaf Helper Blog Xing
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2013 12:12pm

Something like this should work: Function SQLQuery{ param( [string]$Server, [string]$User, [string]$Pass, [string]$Database, [string]$Query ) $connString = "Server=$Server;Database=$Database;User=$User;Password=$Pass;" $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($Query,$connString) $dt = New-Object "System.Data.DataTable" [void]$da.fill($dt) $dt } $outputdata=@() SQLQuery '<server>,<port>' <user> <pass> <database> "<query>" | ForEach-Object{ $outputdata+=New-Object PSObject -Property @{ 'Column1'=$_.Column1_from_query 'Column2'=$_.Column2_from_query 'Column3'='Static information' 'Column4'=$_.Column3_from_query } } $outputdata | Export-CSV 'c:\temp\mydata.csv' -notypeinformation cat 'c:\temp\mydata.csv' # check the result I've just used a generic function to query the database.Inspired by Carlsberg.
May 17th, 2013 12:48pm

Hi, Just checking in to see if the suggestions were helpful. Please let us know if you would like further assistance. If you have any feedback on our support, please click here . Cataleya Li TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
May 20th, 2013 2:56am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics