export to CSV
Hi Aborgeld - I don't have environment to test this = Could you please show the example of your csv file format?
February 16th, 2015 7:23am

The output of your SQL is not a table but is a formatted list.  Post in SQL forum to learn how to write a SQL statement that returns a table.

Here is one method:

invoke-sqlcmd -ServerInstance  $instance -Query 'SELECT name, description, is_advanced FROM sys.configurations ;'

Free Windows Admin Tool Kit Click here and download it now
February 16th, 2015 9:08am

Hi Chen,

Thanks. I'll send you the ouput tomorrow i have it on my work.

Kind regards,

Andr

February 16th, 2015 10:47am

I'm sorry maybe my question is not clear. In the file i use SQL code that returns a table.

My question is about the export-csv that puts everyting in one column instead of my 8 colums.

I have this working in batch with sqlcmd. Only when i replace this to powershell with invoke-sqlcmd i get this issue.

Free Windows Admin Tool Kit Click here and download it now
February 16th, 2015 10:49am

Your report is not returning a table.  It is returning text.  The code I posted returns a dataset that can be exported to a CSV.  Text cannot be exported as more than one column.

Post the SQL and I will show you why.  It has to do with the usual way options are extracted.  My post extracts the configuration table as a dataset.

February 16th, 2015 11:00am

Ok thanks i will test this tomorrow morning in the environment. Thanks for the tip.

I will keep you informed.

Free Windows Admin Tool Kit Click here and download it now
February 16th, 2015 11:03am

I have made the following script (see below), it gives me back database settings on multiple SQL servers. And it works fine. But the problem is the rows in the *.csv are using one column instead of multiple columns like the table format. Even though I use the export-csv format.

Does anybody know how to solve this?

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

foreach ($instance in Get-Content "D:\Scripts\MSSql\InputMSSqlServersProductionBorgy.txt" ){
 
 $instance 
 Invoke-Sqlcmd -ServerInstance $instance.ToString() -Database "MASTER" -inputfile "D:\Scripts\MSSql\DatabaseProperties.sql" | export-csv -path data.csv
 "D:\Scripts\MSSql\TestSQLCmd.csv"


} 

February 16th, 2015 1:38pm

Hi jrv,

I have changed the query like this, but I cannot do an export-csv because I get the powershell format and when I do format-table It puts the table format in one column.

Any suggestions?

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100


foreach ($Instance in Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt)
{
$Instance;
Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT  name AS 'Database_Name' ,
        recovery_model_desc AS 'Recovery Model' ,
        compatibility_level AS 'Compatibility Level' ,
        collation_name AS 'Collation' ,
        owner_sid AS 'Owner' ,
        is_auto_close_on AS 'Auto CLOSE' ,
        is_auto_create_stats_on AS 'Auto Create Statistics' ,
        is_auto_shrink_on AS 'Auto Shrink' ,
        is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
        is_auto_update_stats_on AS 'Auto Update Statistics' ,
        page_verify_option_desc AS 'Checksum'        
FROM    sys.databases ;" | Format-Table >> D:\Scripts\MSSql\test.csv
}
Kind regards, Andr
 
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2015 5:33am

This is the export-csv format

February 17th, 2015 5:43am

This is the format-table format
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2015 5:44am

Hi Chen, See the format in this post.
February 17th, 2015 5:45am

Hi,

please use -NoTypeInformation parameter with Export-csv cmdlet.

Free Windows Admin Tool Kit Click here and download it now
February 17th, 2015 5:47am

Even with the notype information the output stays in one column, I changed the script to the following:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100


foreach ($Instance in Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt)
{
$Instance;
Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT  name AS 'Database_Name' ,
        recovery_model_desc AS 'Recovery Model' ,
        compatibility_level AS 'Compatibility Level' ,
        collation_name AS 'Collation' ,
        owner_sid AS 'Owner' ,
        is_auto_close_on AS 'Auto CLOSE' ,
        is_auto_create_stats_on AS 'Auto Create Statistics' ,
        is_auto_shrink_on AS 'Auto Shrink' ,
        is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
        is_auto_update_stats_on AS 'Auto Update Statistics' ,
        page_verify_option_desc AS 'Checksum'        
FROM    sys.databases ;" | Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation 
}

And the output is this:

February 17th, 2015 5:51am

Hi Aborgeld,

whether Excel automatically detects your columns is dependent on the Delimiter you use (which varies depending on which region you are in).

Try adding -Delimiter ";" to your export-Csv call.

Cheers,
Fred

Free Windows Admin Tool Kit Click here and download it now
February 17th, 2015 5:58am

#As fred suggested try the below code

Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 foreach ($Instance in Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt) { $Instance; Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT name AS 'Database_Name' , recovery_model_desc AS 'Recovery Model' , compatibility_level AS 'Compatibility Level' , collation_name AS 'Collation' , owner_sid AS 'Owner' , is_auto_close_on AS 'Auto CLOSE' , is_auto_create_stats_on AS 'Auto Create Statistics' , is_auto_shrink_on AS 'Auto Shrink' , is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' , is_auto_update_stats_on AS 'Auto Update Statistics' , page_verify_option_desc AS 'Checksum' FROM sys.databases ;" | Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation -Delimeter ";" }

February 17th, 2015 6:07am

Indeed I have the same issue in CSV - It's based on my excel settings. Let us know if -Delimeter fails - I don't have environment now to test this but I can try
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2015 6:12am

Pay attention.

Look closely at this code as posted:

Use this:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

@query=@'
SELECT  name AS 'Database_Name' ,
        recovery_model_desc AS 'Recovery Model' ,
        compatibility_level AS 'Compatibility Level' ,
        collation_name AS 'Collation' ,
        owner_sid AS 'Owner' ,
        is_auto_close_on AS 'Auto CLOSE' ,
        is_auto_create_stats_on AS 'Auto Create Statistics' ,
        is_auto_shrink_on AS 'Auto Shrink' ,
        is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
        is_auto_update_stats_on AS 'Auto Update Statistics' ,
        page_verify_option_desc AS 'Checksum'        
FROM    sys.databases ; 
'@

Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt |
    ForEach-Object{Invoke-Sqlcmd -ServerInstance $_ -Database master -Query} |
    Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation

Your biggest issue was this:

FROM    sys.databases ;" | Format-Table >> D:\Scripts\MSSql\test.csv

You cannot use Format-Table into an Export.

February 17th, 2015 6:32am

This works, but when I open excel I must use the text to column option - separate to get the result I want.

Is there any option to do this?

Free Windows Admin Tool Kit Click here and download it now
February 17th, 2015 6:57am

What did you do that you say works? Please post the script you used.
February 17th, 2015 6:59am

This, only I have to choose "Tekst to Colums" -> Separate change the delimiter.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100


foreach ($Instance in Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt)
{
$Instance;
Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT  name AS 'Database_Name' ,
        recovery_model_desc AS 'Recovery Model' ,
        compatibility_level AS 'Compatibility Level' ,
        collation_name AS 'Collation' ,
        owner_sid AS 'Owner' ,
        is_auto_close_on AS 'Auto CLOSE' ,
        is_auto_create_stats_on AS 'Auto Create Statistics' ,
        is_auto_shrink_on AS 'Auto Shrink' ,
        is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
        is_auto_update_stats_on AS 'Auto Update Statistics' ,
        page_verify_option_desc AS 'Checksum'        
FROM    sys.databases ;" | Export-CSV D:\Scripts\MSSql\test.txt -NoTypeInformation -Delimiter "," 

}

Free Windows Admin Tool Kit Click here and download it now
February 17th, 2015 7:04am

I tried to fix that for you but you are stubbornly inserting the same errors:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

$query=@'
SELECT  name AS 'Database_Name' ,
        recovery_model_desc AS 'Recovery Model' ,
        compatibility_level AS 'Compatibility Level' ,
        collation_name AS 'Collation' ,
        owner_sid AS 'Owner' ,
        is_auto_close_on AS 'Auto CLOSE' ,
        is_auto_create_stats_on AS 'Auto Create Statistics' ,
        is_auto_shrink_on AS 'Auto Shrink' ,
        is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
        is_auto_update_stats_on AS 'Auto Update Statistics' ,
        page_verify_option_desc AS 'Checksum'        
FROM    sys.databases ; 
'@

Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt |
    ForEach-Object{Invoke-Sqlcmd -ServerInstance $_ -Database master -Query  $query} |
    Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation

The next thing to look at if this still gives a single column what is you"kist separator" set to in Windows? Run the above first then check list char.

Do not add helpful looking items into the loop as it breaks the CSV.

February 17th, 2015 7:10am

sorry I typed -

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

$query=@'
SELECT  name AS 'Database_Name' ,
        recovery_model_desc AS 'Recovery Model' ,
        compatibility_level AS 'Compatibility Level' ,
        collation_name AS 'Collation' ,
        owner_sid AS 'Owner' ,
        is_auto_close_on AS 'Auto CLOSE' ,
        is_auto_create_stats_on AS 'Auto Create Statistics' ,
        is_auto_shrink_on AS 'Auto Shrink' ,
        is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
        is_auto_update_stats_on AS 'Auto Update Statistics' ,
        page_verify_option_desc AS 'Checksum'        
FROM    sys.databases ; 
'@

Get-Content D:\Scripts\MSSql\InputMSSqlServersTest.txt |
    ForEach-Object{Invoke-Sqlcmd -ServerInstance $_ -Database master -Query  $query} |
    Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation -UseCulture
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2015 7:14am

We can also always add:

 Export-CSV D:\Scripts\MSSql\test.csv -NoTypeInformation -UseCulture

To the export to adjust for culture.  This works in PowerShell v3 and later.

February 17th, 2015 7:18am

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

Other recent topics Other recent topics