SSMS and Excel



  • I had reason to find a way to copy data that included newlines from SSMS to Excel, and came across this question.

    What blows my mind is that the top-rated answer suggests removing the newlines, which destroys the data integrity. What's worse is that the answer has a community bounty on it!

    By itself that is worthy of posting here. But there's more! The second-highest rated answer doesn't work for queries; it simply says to export the whole table by bypassing SSMS.

    The other answers tend to be low-quality duplicates of each other, as usual on many SO questions.

    TRWTF, of course, is Microsoft for not making two of their own products work well together for what has to be a not uncommon situation.



  • It looks like he specifically wanted to remove the newlines.

    When i was using SQL Server 2008 this would copy and paste completely fine into Excel. But now copying pasting the same data create a new line/ carriage return on the data i have in excel.


  • Notification Spam Recipient

    @djls45 Yeah, at one point I had a small table of VS, SSMS, and other tools and whether they would export newlines, and how.
    It's disappeared amidst the other paper material I once had, but this was a rather important document when we started making webapps that accepted input from textarea elements...



  • My understanding was that he wanted to get rid of the newlines because it was breaking the spreadsheet. Currently, copying data that has a newline from SSMS to Excel will cause a new row to be entered at the newline in Excel. This means that there will be two rows of incomplete and unaligned data for every newline in the data.
    Apparently, SSMS 2008 (and older?) would automatically remove the newlines, which was "corrected" in 2012, but the bugfix didn't fully correct the issue. It made it worse. At least, removing newlines preserves the layout of your data.

    @LB_ said in SSMS and Excel:

    It looks like he specifically wanted to remove the newlines.

    When i was using SQL Server 2008 this would copy and paste completely fine into Excel. But now copying pasting the same data create a new line/ carriage return on the data i have in excel.



  • I should note that there is one answer that actually provides a working solution by following the CSV specifications, which permits copy-pasting directly from SSMS into Excel with the exact data from the database.

    SELECT '"' + REPLACE(ColumnName, '"', '""') + '"' AS ColumnName FROM TableName;


  • Notification Spam Recipient

    @djls45 Yes, well the proper solution is to set up a data connection in Excel to retrieve that data, which (if it so happened to have newlines in it) would make it in correctly no matter what.

    The problem is that it's so convoluted to do such a simple like "Connect to X SQL server and run Y query, spit the results into a table on Sheet1$" that nobody does it and it's not usually even a mentioned solution, especially for one-off work like it seems like they're doing.



  • It took me less than 15 minutes to write a console application that reads every cell from about 150+ excel files into a table with every field typed nvarchar(255).

    And then it took less than 5 minutes to write "SELECT ... INTO ..." statement with "CONVERT(datatype, expression)" to build the final table.



  • @cheong said in SSMS and Excel:

    It took me less than 15 minutes to write a console application that reads every cell from about 150+ excel files into a table with every field typed nvarchar(255).

    And then it took less than 5 minutes to write "SELECT ... INTO ..." statement with "CONVERT(datatype, expression)" to build the final table.

    I'm not sure what you're trying to show here. You're going from Excel into a database. The original asker on StackOverflow wanted to go from a database to Excel via SQL Server.



  • Yup, I've run into this recently too. There's no way to get SSMS to reliably write results to a CSV file. I ended up writing a Python script to do it.

    It frightens me how much software there is that manages to mess up CSV.



  • I use Aqua Data Studio, which natively will export a result set to Excel with a click of a button.

    If I wanted to generate a CSV without third party software, however, I'd use PowerShell:

    Add-PSSnapin -Name SqlServerCmdletSnapin100;
    # Import-Module -Name SQLPS -DisableNameChecking;
    $Query = @'
    SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
    FROM INFORMATION_SCHEMA.TABLES
    ORDER BY TABLE_SCHEMA, TABLE_NAME
    '@;
    $FileName = "$env:USERPROFILE\Documents\Tables.csv";
    
    Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $Query | Export-Csv -Path $FileName -NoTypeInformation;
    

    I still want to club the asshat who decided Export-Csv should include type information by default. I don't think I've ever run the cmdlet without that flag intentionally. Classic problem of a programmer writing tools for system administration. Beyond that, though, Import-Csv and Export-Csv have been very good.


  • Discourse touched me in a no-no place

    @Dragnslcr said in SSMS and Excel:

    Yup, I've run into this recently too. There's no way to get SSMS to reliably write results to a CSV file.

    It frightens me how much software there is that manages to mess up CSV.

    You should know that Excel can't reliably read data from a CSV either.

    https://what.thedailywtf.com/topic/6174/excel-csv-wtf

    https://what.thedailywtf.com/topic/15830/microsoft-office-excel-helpfulness-snarl-rant

    https://what.thedailywtf.com/topic/18889/csvs-ought-to-be-in-ascii

    I ended up writing a Python script to do it.

    That might actually be correct. Certainly a better chance of it than with that damn Microsoft product…



  • @djls45 said in SSMS and Excel:

    @cheong said in SSMS and Excel:

    It took me less than 15 minutes to write a console application that reads every cell from about 150+ excel files into a table with every field typed nvarchar(255).

    And then it took less than 5 minutes to write "SELECT ... INTO ..." statement with "CONVERT(datatype, expression)" to build the final table.

    I'm not sure what you're trying to show here. You're going from Excel into a database. The original asker on StackOverflow wanted to go from a database to Excel via SQL Server.

    Import data from Excel is much harder than export so solving the problem with code should be easy. There's no need to keep bashing SSMS when you can do it yourself.

    In fact, when doing data conversion, if I cannot figure out how to import data with SSMS within 5 minute, I write my code instead to save the time.


  • BINNED

    @Dragnslcr said in SSMS and Excel:

    There's no way to get SSMS to reliably write results to a CSV file.

    I run the query in SSMS (SQL 2012), right click on the results and save as a CSV file. It works fine and even adds quotes when required.



  • @antiquarian said in SSMS and Excel:

    I run the query in SSMS (SQL 2012), right click on the results and save as a CSV file. It works fine and even adds quotes when required.

    Does any of the data have CR/LF characters? That's what was broken the last time I tried it, but that's in SSMS 2008, so maybe they've fixed it in newer versions.


  • BINNED

    @Dragnslcr said in SSMS and Excel:

    Does any of the data have CR/LF characters? That's what was broken the last time I tried it, but that's in SSMS 2008, so maybe they've fixed it in newer versions.

    Still broken in 2012, but what should it be doing with the CR/LF characters?



  • @antiquarian said in SSMS and Excel:

    Still broken in 2012, but what should it be doing with the CR/LF characters?

    If I remember correctly, it didn't quote fields that had CR/LF characters in them.


  • BINNED

    @Dragnslcr I took another look at my test file. It is quoting fields that have CR/LF characters now.


Log in to reply