Convert SSMS .rpt output file to .txt/.csv

问题:

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

回答1:

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 :).

回答2:

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)

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

• 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).

回答3:

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.

--> In Ultra-Edit the option ‘file, conversion options, unicode to ASCII’ did the trick. 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.

回答4:

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.

回答5:

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.

回答6:

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.