SQL 如何将数据从 iSQL 输出到带有_标题的 csv 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4355080/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to output data from iSQL to csv file _with_ headings?
提问by Oliver Jakoubek
I'm trying to query a Sybase ASA 8 database with the iSQL client and export the query results to a text file in CSV format. However the column headingsare not exported to the file. There is no special option to specify that, neither in the iSQL settings nor in the OUTPUT statement.
我正在尝试使用 iSQL 客户端查询 Sybase ASA 8 数据库并将查询结果导出到 CSV 格式的文本文件中。但是，列标题不会导出到文件中。在 iSQL 设置和 OUTPUT 语句中都没有指定的特殊选项。
The query and output statement looks like this:
查询和输出语句如下所示：
SELECT * FROM SomeTable;
OUTPUT TO 'C:\temp\sometable.csv' FORMAT ASCII DELIMITED BY ';' QUOTE ''
The result is a file like
结果是一个像
1;Miller;Steve;1980-06-28
2;Jones;Martha;1965-11-02
3;Waters;Richard;1979-10-15
while I'd like to have
虽然我想拥有
ID;LASTNAME;FIRSTNAME;DOB
1;Miller;Steve;1980-06-28
2;Jones;Martha;1965-11-02
3;Waters;Richard;1979-10-15
Any hints?
任何提示？
回答by pascal
I would have suggested to start with another statement:
我会建议从另一个声明开始：
SELECT 'ID;LASTNAME;FIRSTNAME;DOB' FROM dummy;
OUTPUT TO 'C:\temp\sometable.csv' FORMAT ASCII DELIMITED BY ';' QUOTE '';
and add the APPENDoption on your query... but I can't get APPENDto work (but I'm using a ASA 11 engine).
并APPEND在您的查询中添加选项...但我无法开始APPEND工作（但我使用的是 ASA 11 引擎）。
回答by Olzia
Try this one
试试这个
SELECT 'ID','LASTNAME','FIRSTNAME','DOB' union
SELECT string(ID),LASTNAME,FIRSTNAME,DOB FROM SomeTable;
OUTPUT TO 'C:\temp\sometable.csv' FORMAT ASCII DELIMITED BY ';' QUOTE '';
回答by eci
Simply add the option
只需添加选项
WITH COLUMN NAMES
to your statement and it adds a header line with the column names.
到您的语句中，它会添加一个带有列名称的标题行。
The complete statement is therefore:
因此，完整的声明是：
SELECT * FROM SomeTable; OUTPUT TO 'C:\temp\sometable.csv' FORMAT ASCII DELIMITED BY ';' QUOTE '' WITH COLUMN NAMES
See sybase documentation.
请参见sybase 文档。
回答by Sathiyadasan
isql -S<Server> -D<Database>-U<UserName> -s \; -P<password>$ -w 10000 -iname.sql > output.csv
回答by graysonwright
I am able to use the isqlcommand to output quoted CSV.
我可以使用该isql命令输出引用的 CSV。
Example
例子
$ isql $DATABASE $USERNAME $PASSWORD -b -d, -q -c
select username, fullname from users
gives the result:
给出结果：
username,fullname
"jdoe","Jane Doe"
"msmith","Mark Smith"
Command-line flags
命令行标志
（从手册页复制）
-b: Run isql in non-interactive batch mode. In this mode, the isql processes its standard input, expecting one SQL command per line.
-b: 在非交互式批处理模式下运行 isql。在此模式下，isql 处理其标准输入，预计每行一个 SQL 命令。
-dDELIMITER: Delimits columns with delimiter.
-dDELIMITER: 用分隔符分隔列。
-c: Output the names of the columns on the first row. Has any effect only with the -d or -x options.
-c: 输出第一行的列名。仅对 -d 或 -x 选项有任何影响。
-q: Wrap the character fields in double quotes.
-q: 用双引号将字符字段括起来。
Escaping Issue
逃避问题
You might run into problems if the query results contain double-quotes, though. The quotes aren't escaped properly, so they result in invalid CSV:
但是，如果查询结果包含双引号，您可能会遇到问题。引号未正确转义，因此会导致 CSV 无效：
> select 'string","with"quotes' as quoted_string
quoted_string
"string","with"quotes"
回答by Jeff Gibson
If you use the FORMAT EXCELoption, it will output the rows with the column name in the first row. Then once you get it into excel you can save it into another format if you need to.
如果您使用该FORMAT EXCEL选项，它将输出具有第一行列名的行。然后，一旦将其导入 excel，您可以根据需要将其保存为另一种格式。
SELECT * FROM SOMETABLE;
OUTPUT TO 'C:\temp\sometable.xls' FORMAT EXCEL DELIMITED BY ';' QUOTE ''
回答by PerformanceDBA
You are already familiar with the OUTPUT options. There is no option that gives you what you want.
您已经熟悉 OUTPUT 选项。没有任何选项可以满足您的需求。
Ok, the problem is the receiving end does not accept standard CSV files, it needs semi-colons.
好的，问题是接收端不接受标准的CSV文件，需要分号。
If you are scripting, then you are better off getting the output in the format that is closest to what you need, and then awk-ing the output file. Very fast and you can change anything you need. I think your best option is ASCII or default output format, which will provide Comma (not colon) Separated Values, in an ASCII character text file, and includes column Headers. Then use a single awk command to convert the commas to semi-colons.
如果您正在编写脚本，那么最好以最接近您需要的格式获取输出，然后对输出文件进行 awk 处理。非常快，您可以更改任何您需要的内容。我认为您最好的选择是 ASCII 或默认输出格式，它将在 ASCII 字符文本文件中提供逗号（而不是冒号）分隔值，并包括列标题。然后使用单个 awk 命令将逗号转换为分号。
回答by Zahir Khan
Found an easier solution, Place the headers in one file say header.txt ( it will contain a single line "col_1|col_2|col_3") then to combine the header file and your output file run:
找到了一个更简单的解决方案，将标题放在一个文件中，比如 header.txt（它将包含一行“col_1|col_2|col_3”），然后将头文件和您的输出文件结合起来运行：
cat header.txt my_table.txt > my_table_wth_head.txt
