Ver:SQLServer2008
想通过fmt导出csv文件中的数据
试了好久还是导不出数据
请大家指点一下
谢谢表
USE [A];
CREATE TABLE [S].[T]
(
[ID1] CHAR(4),
[NAME1] NVARCHAR(20),
[PASSWORD1] NVARCHAR(100),
[DATE1] DATE
);csv
"12","我","我1","2028/09/26"
"2","和","8222","2028/09/29"fmt
10.0
4
1 SQLCHAR 0 4 "","" 1 ID1 ""
2 SQLNCHAR 0 20 "","" 2 NAME1 ""
3 SQLNCHAR 0 100 "","" 3 PASSWORD1 ""
4 SQLDATETIME 0 100 ""\r\n" 4 DATE1 ""
想通过fmt导出csv文件中的数据
试了好久还是导不出数据
请大家指点一下
谢谢表
USE [A];
CREATE TABLE [S].[T]
(
[ID1] CHAR(4),
[NAME1] NVARCHAR(20),
[PASSWORD1] NVARCHAR(100),
[DATE1] DATE
);csv
"12","我","我1","2028/09/26"
"2","和","8222","2028/09/29"fmt
10.0
4
1 SQLCHAR 0 4 "","" 1 ID1 ""
2 SQLNCHAR 0 20 "","" 2 NAME1 ""
3 SQLNCHAR 0 100 "","" 3 PASSWORD1 ""
4 SQLDATETIME 0 100 ""\r\n" 4 DATE1 ""
只是在SQLSERVER2005以后可用
兼容性的问题上
fmt也要会写才好
格式化文件可以通过bcp导出的,必要时,会改改就行。
我要以","为分隔符
并不是逗号,应该怎么写?
还有最后的Chinese_PRC_CI_AS
不加也应该可以的,对吗
所以格式化文件的版本是90G:\format.fmt <!--格式化文件-->9.0
5
1 SQLCHAR 0 4 "\"" 0 ID0 Chinese_PRC_CI_AS
2 SQLCHAR 0 4 "\",\"" 1 ID1 Chinese_PRC_CI_AS
3 SQLCHAR 0 40 "\",\"" 2 NAME1 Chinese_PRC_CI_AS
4 SQLCHAR 0 200 "\",\"" 3 PASSWORD1 Chinese_PRC_CI_AS
5 SQLCHAR 0 24 "\"\r\n" 4 DATE1 ""
G:\data.txt --数据文件"12","我","我1","2028/09/26"
"2","和","8222","2028/09/29"
--SQLBULK INSERT T
FROM 'g:\aaa.txt'
WITH
(
FORMATFILE = 'G:\format.fmt'
);SELECT * FROM T
/*
ID1 NAME1 PASSWORD1 DATE1
12 我 我1 2028-09-26 00:00:00.000
2 和 8222 2028-09-29 00:00:00.000
*/
所以要在格式化文件里做些处理才行。
是"\",\""的问题
liangCK,谢了
以后还请多多关照