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"


} 

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 ;'

Hi Chen,

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

Kind regards,

Andr

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.

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.

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

I will keep you informed.

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
}

This is the export-csv format

Hi,

please use -NoTypeInformation parameter with Export-csv cmdlet.

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:

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

#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 ";"
}		

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.

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?

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 "," 

}

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.

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

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.

The first one had problems with the query variable. This works now indeed. I typed the script like above. The delimiter is ","

The result in Excel is:

Database_Name,"Recovery Model","Compatibility Level","Collation","Owner","Auto CLOSE","Auto Create Statistics","Auto Shrink","Auto Update Statistics Asynchronously","Auto Update Statistics","Checksum"

In one column

Why are you saying that the delimiter is a comma.  Clearly it isn't or you have altered you settings incorrectly.

Pleas. Post the exact script you are using.  I am sure you are either alteringit insomeway causing the column to collapse or you have other issues.

This is done all of the time for years.  You have to be cretinf an issue without realizing it.

 I typed the script like above. The delimiter is ","

I'm using this code:

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

And these are my settings and output:

Even with this option.

 - I open Excel

- I open the file

- The output stays the same. Very frustrating

Just open the file by pasting its name at the prompt.  Just like this.  Let Excel open the file.

D:\Scripts\MSSql\test.csv

I suspect that youmanually changed the list separator at sometime because most of Europe uses the ';' for a sep.  If that is the case then you need to se the delimiter specifically as a ';'.

I did open it via prompt, I know what you mean, it's like somewhere a settings is changed. But we only use regional settings.

And I was reading my post later then your post, so that's the mis communication

Even with this option.

 - I open Excel

- I open the file

- The output stays the same. Very frustrating

Does that mean you changed the setting.  Remember that Excel also defines these settings and if you start changing things it can get horribly confused.

You should never need to change the system settings.  Doing so cause issues that are hard to detect.

I did open it via prompt, I know what you mean, it's like somewhere a settings is changed. But we only use regional settings.

And I was reading my post later then your post, so that's the mis communication

Open a good excel file.  Export it as a CSV.  Open in notepad and look at the delimiter.  I bet it is some odd character like a pipe.

Nope, I didn't change the settings. I only looked at the settings, see screenshots above

Excel and culture gets them from regional settings.

You may have a corrupt Excel or you may have encoding issues.  Start by exporting a file and inspect it for delimiter and encoding.  If the verion of PowerShell is not correctly set up for your locale then it may be creating a mismatch here but that should not happen if nothing has been altered.

It is clear that we are creating a correct CSV file so the issue you are having really has little to do with scripting.  It is really about your system.  You might want to try testing on a different system to see if that works.

Thanks for working with me on this issue. After testing a new CSV the delimiter was different from the regional settings. I have changed that in the script and it works.

Thanks for working with me on this issue. After testing a new CSV the delimiter was different from the regional settings. I have changed that in the script and it works.

That is because someone changed the regional setting to a comma but Excel does not see that as it sees the original. If you go into language settings in Excel you can force them to be the same.

Well good. You are working and the rest is cosmetic.

And in that your right too. Whe use the dutch regional settings, it's got a "," as separator.

So in combination with excel this is a problem.

And in that your right too. Whe use the dutch regional settings, it's got a "," as separator.

So in combination with excel this is a problem.

Then be sure that Excel is optioned to use the Windows settings.  I believe this can be enforced via a GPO.