none
紧急求助,关于把查询结果导出到excel中的问题。谢谢各位XDJM先。。o(∩_∩)o... RRS feed

  • 问题

  • 我想把以下查询语句查询出的结果导出到excel表中,用什么命令可以实现?(比如写个脚本,然后执行一下这.bat脚本就可以导出到excel中)
    具体查询语句如下
    SELECT (STR(DATEPART(month,t1.collecttime),2)+'-' +STR(DATEPART(day,t1.collecttime),2)+'-'+STR(DATEPART(year,t1.collecttime),4)) as DATE,
          t1.IpAddress,
         sum(t1.G826PM_EB) as EB,
         sum(t1.G826PM_ES) as ES,
         sum(t1.G826PM_SES) as SES,
         sum(t1.G826PM_UAS) as UAS,
         sum(t1.G826PM_BBE) as BBE,
         sum(t1.G828PM_EFS) as EFS,
         max(t2.SYSNAME) as SYSNAME
    FROM [UEP_PM].[dbo].[A_PR10_G828PM] t1,[uep].[dbo].[PR10]t2
    where t1.IpAddress=t2.IpAddress
    group by ((STR(DATEPART(month,t1.collecttime),2)+'-' +STR(DATEPART(day,t1.collecttime),2)+'-'+STR(DATEPART(year,t1.collecttime),4) )), t1.ipaddress
    order by ((STR(DATEPART(month,t1.collecttime),2)+'-' +STR(DATEPART(day,t1.collecttime),2)+'-'+STR(DAT

    2009年7月2日 1:33

答案

  • 你在一个数据库里面吧你需要查询的sql 建立成一个试图(或者存储过程) 这个会吧?

    然后吧bcp 里面的sql 替换为 select *from 你的试图名称  即可。


    你上面的错误是你的用户没有数据库的访问权限,加一下权限就好了。
    family as water
    2009年7月2日 3:42
  • 呵呵,你有没有试过在excel里面直接连接引用数据库。用excel执行你的sql语句就没有倒入问题了!:)
    2009年7月3日 16:45

全部回复

  • 使用bcp即可

    保存格式为cvs,excel就可以打开,大致的命令语法如下:

    bcp "您的sql语句" queryout "C:\您导出的文件.cvs" -c -S 服务器名称 -U 用户名称 -P 密码

    将这个命名保存为bat格式文件,即可。

    有问题可以查bcp的msdn文档。

    family as water
    2009年7月2日 1:48
  • 我就是用这个语句(我把这语句编辑后保存为bat文件,放在C盘,然后双击运行之),好像出现好多错误哦。等会我再运行一次,然后把错误给贴上来。让大家帮忙分析分析。非常感谢的说。
    2009年7月2日 1:54

  • C:\Documents and Settings\Administrator>bcp "SELECT (STR(DATEPART(month,t1.collecttime),2)+'-' +STR(DATEPART(day,t1.collecttime),2)+'-'+STR(DATEPART(year,t1.collecttime),4)) as D
    usage: bcp {dbtable | query} {in | out | queryout | format} datafile
      [-m maxerrors]            [-f formatfile]          [-e errfile]
      [-F firstrow]             [-L lastrow]             [-b batchsize]
      [-n native type]          [-c character type]      [-w wide character type]
      [-N keep non-text native] [-V file format version] [-q quoted identifier]
      [-C code page specifier]  [-t field terminator]    [-r row terminator]
      [-i inputfile]            [-o outfile]             [-a packetsize]
      [-S server name]          [-U username]            [-P password]
      [-T trusted connection]   [-v version]             [-R regional enable]
      [-k keep null values]     [-E keep identity values]
      [-h "load hints"]         [-x generate xml format file]

    C:\Documents and Settings\Administrator>t1.IpAddress,
    't1.IpAddress' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>sum(t1.G826PM_EB) as EB,
    'sum' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>sum(t1.G826PM_ES) as ES,
    'sum' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>sum(t1.G826PM_SES) as SES,
    'sum' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>sum(t1.G826PM_UAS) as UAS,
    'sum' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>sum(t1.G826PM_BBE) as BBE,
    'sum' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>sum(t1.G828PM_EFS) as EFS,
    'sum' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>max(t2.SYSNAME) as SYSNAME
    'max' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>FROM [UEP_PM].[dbo].[A_PR10_G828PM] t1,[uep].[dbo].[PR10]t2
    'FROM' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>where t1.IpAddress=t2.IpAddress
    INFO: Could not find files for the given pattern(s).

    C:\Documents and Settings\Administrator>group by ((STR(DATEPART(month,t1.collecttime),2)+'-' +STR(DATEPART(day,t1.collecttime),2)+'-'+STR(DATEPART(year,t1.collecttime),4) )), t1.
    'group' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>order by ((STR(DATEPART(month,t1.collecttime),2)+'-' +STR(DATEPART(day,t1.collecttime),2)+'-'+STR(DATEPART(year,t1.collecttime),4))),t1.ip
    'order' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Documents and Settings\Administrator>

     

     

    一堆的错误
    大家帮忙看看

     

     

     

    2009年7月2日 2:48
  • 是不是不能跨表查询的?奇怪了
    2009年7月2日 2:51
  • 我就是用这个语句(我把这语句编辑后保存为bat文件,放在C盘,然后双击运行之),好像出现好多错误哦。等会我再运行一次,然后把错误给贴上来。让大家帮忙分析分析。非常感谢的说。
    Save it as .sql file then call with sqlcmd in dos prompt or in .bat file. Run sqlcmd /? in dos prompt for syntax.
    2009年7月2日 2:53
  • 你必须吧这个sql写在一行上,bat文件保存把这个作为多行命名执行了,所以报告了这些错误


    一个简单的办法,是写成视图,然后一句 select * from viewname 就ok啦。
    family as water
    2009年7月2日 3:04
  • 非常感谢呢。我把所有的语句写在了一行,执行终于没啥错误了。不过还是有点错误。具体信息为:
    SQLState = 08004, NativeError = 916
    Error = [Microsoft][SQL Native Client][SQL Server]The server principal "ueppm" is not able to access the database "uep" under the current security context.

    因为这次查询我到的是两个数据库中的两张表,看提示说我没权限访问uep(用到数据库uep中的一张表)。我想是不是应该把
    -S"ZTE-2WIV99R0IZG" -U"ueppm" -P"ZTE_pm_2008"这句话给改一下
    改成
    -S"ZTE-2WIV99R0IZG" -U"uep,ueppm" -P"ZTE_uep_2008,ZTE_pm_2008"(增加一个数据库名一个用户名)


    另外对stone Z说的写成视图的方法不是太明白,请明示。o(∩_∩)o...
    2009年7月2日 3:17
  • Is ueppm a valid database user? Use 'create view' to convert your query to view, find syntax and sample in books online.

    2009年7月2日 3:35
  • 你在一个数据库里面吧你需要查询的sql 建立成一个试图(或者存储过程) 这个会吧?

    然后吧bcp 里面的sql 替换为 select *from 你的试图名称  即可。


    你上面的错误是你的用户没有数据库的访问权限,加一下权限就好了。
    family as water
    2009年7月2日 3:42
  • 非常感谢
    呵呵
    OK了

    有点缺陷,不带表头。。在研究中
    忘各位XDJM继续指教
    :-)
    2009年7月2日 4:51
  • 呵呵,你有没有试过在excel里面直接连接引用数据库。用excel执行你的sql语句就没有倒入问题了!:)
    2009年7月3日 16:45