将 SSMS .rpt 输出文件转换为 .txt/.csv
我想将我的大型 SSMS (SQL Server Management Studio) 查询结果(2.5m 行，9 个字段)导出为 .csv 或逗号分隔的 .txt(带标题).(MS SQL Server 2005 管理工作室.)
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 中选择query result to text"和一个小答案(几行，例如最多 200k)，我当然可以简单地复制并粘贴到文本编辑器.对于我在这里的大答案，我当然可以一次将 20 万行左右的行复制并粘贴到像 Ultra-Edit 这样的文本编辑器中，10 次.(当我一次尝试所有 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.
对于查询结果到文件"，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 Reports 文件?或者不是.我的 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，文件大小减半.(330meg 到 180meg).在 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 to 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*.
附言另一种选择是使用 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.
相关文章