将SSMS .rpt输出文件转换为.txt / .csv [英] Convert SSMS .rpt output file to .txt/.csv

问题描述

我想将我的大型SSMS（SQL Server Management Studio）查询结果（2.5m行，9个字段）导出为.csv或逗号分隔的.txt（带有标题）。 （MS SQL Server 2005 Management Studio。）

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

这样我就可以将其逐行读取到VBA程序中（对数据进行某些计算）或在Excel中进行查询（例如，使用Microsoft Query）。计算很复杂，我更喜欢在SSMS之外的其他地方进行。

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.

如果我在SSMS中选择文本查询结果和一个小的答案（几行，例如最多200k），我当然可以简单地复制并粘贴到文本编辑器中。对于这里的大答案，我当然可以一次将20万行左右的行复制和粘贴10次，并粘贴到类似Ultra-Edit的文本编辑器中。 （当我一次尝试全部2.5m时，我会在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.

对于'query结果到文件，SSMS始终会写入.rpt文件。 （当您在结果窗口中右键单击并选择另存为时，将出现与上面相同的内存错误。）

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文件，然后再将.rpt转换为.txt。

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

我认为此.rpt是Crystal报告文件？还是不是我的PC上没有Crystal Reports，因此无法使用它来转换文件。

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.

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

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

当我只是阅读&使用VBA编写.rpt文件大小减半。 （330到180兆）。在Microsoft Query中，标题确实显示了（尽管第一个字段名称有一个有趣的前导字符，在其他完全不同的情况下，这是我以前遇到的情况）。我似乎确实可以在Excel中对其进行有意义的数据透视表。

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.

但是，当我在Ultra-Edit中打开这个新文件时，它会显示汉字！

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

->某处可能有免费（且简单/安全）的转换器应用程序。还是我应该相信这个.txt文件适合阅读我的VBA程序。

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

谢谢

推荐答案

在一个朋友的帮助下，我找到了解决方案：Rpt文件是在MS SQL Server Management Studio中生成的纯文本文件，但是使用UCS-2 Little Endian编码而不是ANSI。

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.

->在Ultra-Edit选项中，文件，转换选项， unicode到ASCII 选项就可以了。文本文件从330兆减少为180兆，Excel中的Microsoft Query现在可以看到列，而VBA可以读取文件&

--> 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。另一个选择是使用MS Access（可以处理较大的结果），并使用ODBC连接到数据库。但是，那时我将不得不使用Jet-SQL，它的命令比MS SQL Server Management Studio的T-SQL少。显然，可以在MS Access 2007中将新文件创建为.adp，然后使用T-SQL连接到SQL Server后端。但是在MS Access 2010中（在我的PC上），该选项似乎不再存在。

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.

这篇关于将SSMS .rpt输出文件转换为.txt / .csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！