将SSMS .rpt输出文件转换为.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.)

我想将我的大型SSMS (SQL Server Management Studio)查询结果(2.5m行，9个字段)导出为.csv或以逗号分隔的.txt(带有标题)。(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.

这样我就可以逐行读取它到VBA程序中(对数据进行某些计算)，或者在Excel中查询它(例如使用Microsoft Query)。计算起来很复杂，我宁愿在别的地方做，而不是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.

如果我在SSMS中选择“查询结果到文本”和一个小的答案(比如几行，最多200k)，我当然可以简单地复制并粘贴到文本编辑器中。对于我的大答案，我当然可以一次复制并粘贴200k左右的行，每次10次，到一个文本编辑器，比如Ultra-Edit。(当我一次尝试所有250米时，我在ssm中得到了一个内存警告。)但未来我想要一个更优雅的解决方案。

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

对于“查询结果到文件”，SSMS总是写入.rpt文件。(当您在结果窗口中右键单击并选择“save as”时，就会出现如上所示的内存错误。)

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

>看起来我唯一的选择是让SSMS将结果输出到一个文件，也就是。rpt，然后将。rpt转换成。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.

我假设这个。rpt是一个水晶报告文件?或者不是吗。我的PC上没有水晶报告，所以我不能用它来转换文件。

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

当在Ultra-Edit中打开.rpt时，它看起来很好。但是在Excel中的Microsoft Query中，标题并不想显示。

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.

当我使用VBA简单地读和写.rpt时，文件的大小减半。梅格梅格(330 - 180)。在Microsoft查询中，标题现在确实显示出来了(尽管第一个字段名有一个有趣的主角，在其他完全不同的情况下我也曾遇到过这种情况)。我似乎可以用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.

——>是否有免费(简单/安全)的转换器应用。或者我应该相信这个。txt对于读取我的VBA程序是没问题的。

Thanks

谢谢

6 个解决方案

#1

Simple way: In SQL Server Management Studio Go to the Query menu > 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 :).

简单方法:在SQL Server Management Studio中，转到查询菜单>查询选项>结果>文本>将“输出格式”更改为“逗号分隔”。现在运行您的查询导出到一个文件，一旦完成，将该文件从.rpt重命名为.csv，它将在excel中打开:)。

#2

Here is my solution.

这是我的解决方案。

Use Microsoft SQL Server Management Studio
使用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)
将其配置为save Tab delimited .rpt文件:点击“查询”>“查询选项”>“结果”>“文本”>“输出格式”，并选择“Tab delimited”(按下OK)

Now, when you create a report, use the 'Save With Encoding...' menu, and select 'Unicode' (by default, it's 'UTF8')
现在，当您创建一个报告时，使用“保存与编码……”' menu，选择'Unicode'(默认为'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).
现在，您可以使用Excel打开文件，所有内容都将以列形式显示，没有转义，也不会出现外部字符问题(注意，由于unicode编码，文件可能更大)。

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

在一位朋友的帮助下，我找到了我的解决方案:Rpt文件是在MS SQL Server Management Studio中生成的纯文本文件，但是使用UCS-2小Endian编码而不是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*.

——>在超编辑选项“文件、转换选项、unicode到ASCII”中实现了这一功能。文本文件从330meg减少到180 meg, Excel中的Microsoft Query现在可以看到列，VBA可以读取文件& 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.

另一种选择是使用MS Access(可以处理大结果)并与ODBC连接到数据库。但是，我必须使用Jet-SQL，它的命令比MS SQL Server Management Studio的T-SQL少。显然，可以在MS Access 2007中创建一个名为.adp的新文件，然后将T-SQL用于SQL服务器的后端。但是在MS Access 2010(在我的电脑上)，这个选项似乎已经不存在了。

#4

You can use BCP (http://msdn.microsoft.com/en-us/library/ms162802.aspx)

您可以使用BCP (http://msdn.microsoft.com/en-us/library/ms162802.aspx)

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.
您可以使用-U -P(而不是-T)进行SQL身份验证。
Your app have a problem with UNICODE. You can force a code page using -C {code page} if in doubt, try 850.

你的应用程序在UNICODE上有问题。您可以使用-C {code page}强制一个代码页，如果有疑问，请尝试850。
-t will force tab as field delimitor, you can change it for comma -t,

-t将强制选项卡作为字段分隔符，你可以改变它为逗号-t，

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

好处是您可以直接从VBA运行Shell命令调用它。

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

选择“结果到网格”，然后右键单击网格并选择“将结果保存为…”这将保存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.

实际上，如果某些值包含逗号，就会出现问题——生成的CSV没有正确转义。RPT文件实际上相当不错，因为它包含固定宽度的列。如果您有Excel，将结果转换为CSV的一个相对简单的方法是在Excel中打开RPT文件。这将打开文本导入向导，而Excel将在猜测列方面做得很好。遍历向导，然后将结果保存为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.

就我个人而言,我地对地导弹上执行一个查询(在此之前按CTRL + SHIFT + F)结果打开窗户将其保存为一个rpt文件,我简直´t读它(没有水晶报表安装在我的电脑)所以…下次运行查询时，我将它保存为(所有文件)设置为扩展名*。txt,´s它我能读文本文件。

#1

#2