65

I want to export my big SSMS (SQL Server Management Studio) query result (2.5m lines, 9 fields) as .csv or comma-delimited .txt (with headings). (MS SQL Server 2005 Management Studio.)

So that I can then either read it line-by-line into VBA program (to do certain calculations on the data) or do queries on it in Excel (e.g. with Microsoft Query). The calculations are complicated and I prefer to do it somewhere else than SSMS.

If I choose ‘query result to text’ in SSMS and a small answer (few lines e.g. up to 200k) I could of course simply copy and paste to a text editor. For my large answer here I could of course copy and paste 200k or so lines at a time, 10 times, into a text editor like Ultra-Edit. (When I try all 2.5m at once, I get a memory warning inside SSMS.) But for the future I’d like a more elegant solution.

For ‘query result to file’, SSMS writes to an .rpt file always. (When you right-click in the results window and choose ‘save as’, it gives a memory error just like above.)

--> So it looks like my only option is to have SSMS output its result to a file i.e. .rpt and then afterwards, convert the .rpt to .txt.

I assume this .rpt is a Crystal Reports file? Or isn't it. I don’t have Crystal Reports on my PC, so I cannot use that to convert the file.

When opening the .rpt in Ultra-Edit it looks fine. However in Microsoft Query in Excel, the headings doesn’t want to show.

When I simply read & write the .rpt using VBA, the file halves in size. (330meg to 180meg). In Microsoft Query the headings do show now (though the first field name has a funny leading character, which has happened to me before in other totally different situations). I do seem to be able to do meaningful pivot tables on it in Excel.

However when I open this new file in Ultra-Edit, it shows Chinese characters! Could there still be some funny characters in it somewhere?

--> Is there perhaps a free (and simple/ safe) converter app available somewhere. Or should I just trust that this .txt is fine for reading into my VBA program.

Thanks

Relaxed1
  • 983
  • 3
  • 13
  • 21
  • 3
    I have to wonder why you want to output so many lines when all you wish to do is further query the data? There is no need to output text to work with Access or Excel and SQL Server. Also, SQL Server if pretty good at queries itself. – Fionnuala May 10 '12 at 16:28
  • 1
    Hi, it's because I want to run the data through program code which is more "processing" styled than "query" styled. In other words, read multiple input files (of which this file is but one), execute multiple procedures/ functions, and then write multiple output files. I'm simply using VBA because I like the spreadsheet interface for entering my run parameters. I could substitute other programming languages for it and my problem would be the same. – Relaxed1 May 11 '12 at 07:11
  • I know T-SQL can do procedures etc. itself nicely, I just prefer a programming language which is a bit more different in nature. P.s. I've actually now realised, what I could do is: instead of SSMS, paste the T-SQL into Excel and execute it from the same database, but using VBA code instead. Then I can write the output more easily directly to a text file (in the end)… – Relaxed1 May 11 '12 at 07:17
  • ...The pivot tables etc. were just to do initial checks on the nature of the data in total, before doing the actual main run on it. – Relaxed1 May 11 '12 at 07:19
  • 1
    (Just for new readers of this thread ... It is just by coincidence that the .rpt extension is the same as Crystal Reports files. The .rpt written by SMSS here is its own extension for these text files) – Relaxed1 Oct 27 '18 at 10:05
  • In case you need to just load the .rpt file instead of figure out how to export as .csv, here are the steps: https://stackoverflow.com/a/56188149/5070440 – GregGalloway May 17 '19 at 14:10

8 Answers8

82

Simple way: In SQL Server Management Studio, go to the "Query" menu & select "Query Options…" > Results > Text > Change "Output Format" to "Comma Delimited". Now, run your query to export to a file, and once done rename the file from .rpt to .csv and it will open in Excel :).

Sae1962
  • 1,122
  • 15
  • 31
nevetsvsx
  • 821
  • 6
  • 2
  • Hi thanks, what I saw later was - with menu > tools > options > query results 1. to text, and 2. > output format > comma delimited: A. without SQLCMD, it still writes little Endian, double the size, you have to convert unicode to ASCII still. B. with sqlcmd, it writes directly to the desired ASCII. – Relaxed1 Jul 22 '15 at 13:58
  • Here is an example of the SQLCMD that works. To be put inside your .sql script: `:setvar mypth "F:\work\y temp\SSMS TEST\with sqlcmd\" :out $(mypth)"2 result.txt". ` . And of course also first: menu > query > SQLCMD mode – Relaxed1 Jul 22 '15 at 14:04
  • P.s. The latter (SQLCMD inside the .sql script) is now my preferred method. Even then, though, the large-text-file editor I use still gives a 'unicode to ASCII' option. But when executing that option, the size remains unchanged. – Relaxed1 Jul 22 '15 at 15:01
  • 2
    Note that you need to open a new query tab for the changes to take effect. – Sal Sep 14 '18 at 14:18
25

Here is my solution.

  • Use Microsoft SQL Server Management Studio
  • Configure it to save Tab delimited .rpt files: Go to 'Query' > 'Query Options' > 'Results' > 'Text' > 'Output Format' and choose 'Tab delimited' (press OK)

enter image description here

  • Now, when you create a report, use the 'Save With Encoding...' menu, and select 'Unicode' (by default, it's 'UTF8')

enter image description here

  • You can now open the file with Excel, and everything will be in columns, with no escaping nor foreign characters issues (note the file may be bigger due to unicode encoding).
Simon Mourier
  • 132,049
  • 21
  • 248
  • 298
  • 1
    Hi thanks - my files are so large though (e.g. 2 gig), I have to write them directly to the hard drive ... :) – Relaxed1 Jun 14 '16 at 09:16
9

Well with the help of a friend I found my solution: Rpt files are plain text files generated in MS SQL Server Management Studio, but with UCS-2 Little Endian encoding instead of ANSI.

I opened the exported file in my text editor and converted from unicode to ASCII. The text file reduces from 330meg to 180 meg, Microsoft Query in Excel can now see the columns, and VBA can read the file & process lines*.

P.s. Another alternative would have been to use MS Access (which can handle big results) and connect with ODBC to the database. However then I would have to use Jet-SQL which has fewer commands than the T-SQL of MS SQL Server Management Studio. Apparently one can create a new file as .adp in MS Access 2007 and then use T-SQL to a SQL Server back end. But in MS Access 2010 (on my PC) this option seems not to exist anymore.

Relaxed1
  • 983
  • 3
  • 13
  • 21
  • 1
    P.s. In Notepad++ it is: menu > encoding > convert to ANSI. In gVim, type:write ++enc=latin1 newfilename.txt (Note path before the filename. And note the editor will still be busy with the old file afterward.) – Relaxed1 Mar 29 '18 at 08:38
8

You can use BCP

Open a command prompt, then type this:

SET Q="select * from user1.dbo.table1"
BCP.EXE %Q% queryout query.out -S ServerName -T -c -t
  • You can use -U -P (instead of -T) for SQL Authentication.

  • Your app have a problem with UNICODE. You can force a code page using -C {code page}. If in doubt, try 850.

  • -t will force tab as field delimiter, you can change it for comma -t,

The nice thing is you can call this directly from your VBA running shell command.

Sae1962
  • 1,122
  • 15
  • 31
PollusB
  • 1,726
  • 2
  • 22
  • 31
  • Interesting! Been wanting to play around with cmd for a while. – Relaxed1 Oct 09 '12 at 07:06
  • Does not work for me, are you sure you can pass the query as an input file ? As far as I know it is not supported, you have to write the full query as string. – Yann39 Jan 28 '14 at 15:13
  • @Yann39: Yes I am sure. But your query might need a little modification to work with this. The query result should be persistant. Try to SELECT INTO a #TEMPTABLE then SELECT * FROM #TEMPTABLE it worked for me in the past. – PollusB Jan 29 '14 at 16:20
  • @Yann39, you can run C:\>BCP -? to get the list of parameters – PollusB Jan 29 '14 at 16:32
  • 1
    @PollusB Are you sure you don't speak about sqlcmd utility ? I get "Copy direction must be either 'in', 'out' or 'format'" when running `BCP -i join.sql -o test.csv -c -t -S MYSERVER -T`. And see : http://stackoverflow.com/questions/10568975/can-i-specify-an-input-sql-file-with-bcp – Yann39 Jan 29 '14 at 17:45
  • Thanks guys. I'm not familiar with BCP, but I do want to learn about it. I'm going to latch my latest S.S.M.Studio realisation onto your sub-thread here, though. What I see now is: My original question of 2 years ago, above, was when I chose 'output results to file' in SMSS. But now I see: When i use SQLCMD inside SMSS (menu, query, SQLCMD mode = on), and write directly to a text file, it is already ASCII! With possibly some unicode remnants, since my text editor still gives me the 'convert unicode to ASCII' option.... – Relaxed1 Nov 25 '14 at 08:28
  • BCP and SQLCMD serves 2 different purposes. BCP is more a table export utility that was tweaked to work with queries. As SQLCMD will run any queries and you can tweek a resultset. @Yann39, you are right my BCP command is not well written. – PollusB Nov 27 '14 at 05:26
  • @PollusB said "you are right my BCP command is not well written. – PollusB" Actually, it's invalid for both `bcp` and `sqlcmd`... Which I didn't know ten minutes ago... Downvoted. Let's get this answer edited or removed. – daveloyall Dec 13 '16 at 22:10
  • @daveloyall, I've edited my answer so the command works. This approach is still a valid answer to the question. – PollusB Dec 14 '16 at 13:24
4

This is the recommended way I see you can do it.

My Source (Answer from DavidAir)

Pick "results to grid" then then right-click on the grid and select "Save Results As..." This will save a CSV.

Actually, there is a problem with that if some values contain commas - the resulting CSV is not properly escaped. The RPT file is actually quite nice as it contains fixed-width columns. If you have Excel, a relatively easy way of converting the result to CSV is to open the RPT file in Excel. This will bring up the text import wizard and Excel would do a pretty good job at guessing the columns. Go through the wizard and then save the results as CSV.

Eon
  • 3,833
  • 10
  • 46
  • 75
  • 1
    Thanks Eon- this gave a memory error inside SSMS though. Otherwise I would prefer your method. Regards – Relaxed1 May 15 '12 at 13:36
  • ...No sorry, after testing again: No memory error for the 'grid' option you suggest. But the output is still unicode: (1) 'Output to text', then right-click, save: it only allows saving as .rpt. And the save does not complete because a memory error occurs. (2) 'Output to grid', then right-click, save: it only allows saving as .csv. --> No memory error! However the .csv is still in unicode just like the .rpt. – Relaxed1 May 15 '12 at 16:18
  • I know this is a little old, but I came across this issue and found this page. I used a slightly different method. If you select the entire grid, you can right click and copy with headers. Then you can just paste the entire grid into Excel. Seems like the simplest method to me. Remember, KISS (Keep It Simple, Stupid). – hyp3rg3om3tric Aug 13 '13 at 20:08
2

I recommend using the "SQL Server Import and Export Wizard" for a couple reasons:

  • The output file will not have a status message at the bottom like a .rpt file does (ie. "(100 rows affected)") which may mess up your data import
  • Ability to specify custom row and column delimiters of a length greater than 1 character
  • Ability to specify custom source to destination mapping (ie. column FirstName can be mapped to first_name in the CSV)
  • Ability to perform a direct transfer to any other database accessible from the SSMS machine
  • Ability to explicitly select your file encoding and locale

It can be accessed by right-clicking on your database in the management studio (you must right-click the database and not the table) and selecting Tasks > Export Data.

Selecting the data export tool from the SSMS object explorer

When asked for data source you can select the "SQL Server Native Client" and when asked to select a destination you can select "Flat File Destination".

You are then asked to specify a table or query to use.

You can find more info about the tool here:

https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard?view=sql-server-2017

Emeka
  • 782
  • 2
  • 9
  • 15
0

In my case, I execute a query on SSMS (before that press CTRL+SHIFT+F) the result open a window to save it as an rpt file, I couldn´t read it (no Crystal Report install in my computer) so...next time I runned the query I saved it as (all files) set with extension *.txt, and that´s it I was able to read it as text file.

user5293686
  • 1
  • 2
  • Thanks - yes me too. It is simple coincidence that the .rpt extension is the same as a crystal reports file, it is actually just a simple text file (albeit with different encoding - see above) and you can change the extension like you did. – Relaxed1 Jan 16 '18 at 06:50
0

First get your data in .rpt file by using any of above method.

Default .rpt with fixed space column. (262MB)

Comma delimited with Unicode. (52MB) - I used this.

Change file extension to .csv.

Open/Import it in excel and verify data. File type is 'Text Unicode'.

Save it as CSV (Comma Delimited), which reduced size to 25 MB.

Arvind Dhasmana
  • 1,396
  • 2
  • 9
  • 8