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