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

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.

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

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.

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