详解SQL Server导出表为csv或txt，postgresql导入csv或txt

 目录：

1、导出SQL Server 数据库的表为 .txt
2、源表数据记录中带有空白（对源表进行处理）
3、SQL Server 数据库中的记录去掉前后空白
4、txt 文件编码 GBK 转成 UTF-8
5、在 postgresql 新建 test111 数据库，新建表 ods.sl_account_00000000001 (字段与SQL Server对应，注意前后顺序一致)
　　5.1、启动 postgresql
　　5.2、powershell 连接 postgresql
　　5.3、postgresql 操作
　　5.4、postgresql 导入 .txt
6、中间遇到的问题

1、导出SQL Server 数据库的表为 .txt    <--返回目录

　　源表

CREATE TABLE [dbo].[SL_Account] (
  [id] bigint  IDENTITY(1,1) NOT NULL,
  [company_name] varchar(55) COLLATE Chinese_PRC_CI_AS  NULL,
  [company_code] varchar(11) COLLATE Chinese_PRC_CI_AS  NULL,
  [business_type] varchar(15) COLLATE Chinese_PRC_CI_AS  NULL,
  [merchant_code] varchar(11) COLLATE Chinese_PRC_CI_AS  NULL,
  [account_num] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [account_type] varchar(10) COLLATE Chinese_PRC_CI_AS  NULL,
  [create_datetime] datetime  NULL,
  [expire_datetime] datetime  NULL,
  [name] varchar(25) COLLATE Chinese_PRC_CI_AS  NULL,
  [phone_num] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [certificate_num] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [gender] varchar(10) COLLATE Chinese_PRC_CI_AS  NULL,
  CONSTRAINT [PK__SL_Accou__3213E83FA7F76B5A] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
GO

ALTER TABLE [dbo].[SL_Account] SET (LOCK_ESCALATION = TABLE)

　　新建 sl_account.txt 文件

　　开始 => SQL Server Management Studio

　　连接

 　　选中数据库 => 任务 => 导出数据

　　代码页若指定 utf-8，后面导出时会报错，（这个和源表的编码有关？）

 

　　导出速度和内存有关系（有次打开了之前导出的sl_account.txt, 再次导出的时候比较慢，关掉sl_account.txt后速度很快，170万数据不到20秒）

 　　导出数据到 sl_account.txt，格式为

2、源表数据记录中带有空白（对源表进行处理）    <--返回目录

　　结果导出的 txt中名字不对，后面也没有分隔符 "|"

 

 　　修改导出文件的分隔符为 ","，但是还是遇到其他的问题：

 　　查询数据库的对应的记录，发现有空白（可能是回车）

　　导出该条记录的查询结果看看：

"401244"    "十堰车城通"    "00000000001"    "IC卡"    "00000000001"    "10068263"    "员工卡"    "17/6/2009 08:40:47"    "1/4/2021 00:00:00"    "杨xx"        "8667480
"    "0"

3、SQL Server 数据库中的记录去掉前后空白    <--返回目录

sql server中查询删除含有回车换行制表符的记录
--   制表符             CHAR(9)    
--   换行符             CHAR(10)    
--   回车               CHAR(13)    
-- 在表 table 中过滤包含回车换行符的字段 filed 的记录
select * from table where charindex(char(10)+char(13),field)>0  
-- 在表 table 中把包含回车换行符的字段 filed 的记录的回车换行符去掉
update table set field=replace(field,char(10)+char(13),'')

　　查询 某个字段包含回车换行 的所有记录

select * from dbo.SL_Account where charindex(char(10),certificate_num)>0
select * from dbo.SL_Account where charindex(char(13),certificate_num)>0
select * from dbo.SL_Account where charindex(char(13)+char(10),certificate_num)>0

　　上面三条执行得到结果一致，所以空白是 回车换行符

　　解决：

update dbo.SL_Account set name=replace(name,char(13)+char(10),'') where id = 401881

update dbo.SL_Account set name=replace(name,char(13),'') where id = 401881
update dbo.SL_Account set name=replace(name,char(10),'') where id = 401881

update dbo.SL_Account set name=replace(name,char(9),'') where id = 401881
update dbo.SL_Account set name=trim(name) where id = 401881

update dbo.SL_Account set name=replace(name,char(13)+char(10),'')

　　其他字段同样处理。下面 sql 可以参考

-- 替换
update dbo.SL_Account set name=replace(name,char(13),'')
update dbo.SL_Account set name=replace(name,char(10),'')

update dbo.SL_Account set name=replace(name,char(9),'')
update dbo.SL_Account set name=trim(name)

  
-- 查询
select * from dbo.SL_Account where charindex(char(10),name)>0;
select * from dbo.SL_Account where charindex(char(10),phone_num)>0;
select * from dbo.SL_Account where charindex(char(10),certificate_num)>0;
select * from dbo.SL_Account where charindex(char(10),gender)>0;

select * from dbo.SL_Account where charindex(char(13),name)>0;
select * from dbo.SL_Account where charindex(char(13),phone_num)>0;
select * from dbo.SL_Account where charindex(char(13),certificate_num)>0;
select * from dbo.SL_Account where charindex(char(13),gender)>0;

select * from dbo.SL_Account where charindex(char(13)+char(10),name)>0;
select * from dbo.SL_Account where charindex(char(13)+char(10),phone_num)>0;
select * from dbo.SL_Account where charindex(char(13)+char(10),certificate_num)>0;
select * from dbo.SL_Account where charindex(char(13)+char(10),gender)>0;

4、txt 文件编码 GBK 转成 UTF-8    <--返回目录

　　参考：批量将.txt编码格式转化为utf8

　　170万条记录，GBK编码时 187 M, 转成 utf8 后 203 M

5、在 postgresql 新建 test111 数据库，新建表 ods.sl_account_00000000001 (字段与SQL Server对应，注意前后顺序一致)    <--返回目录

5.1、启动 postgresql    <--返回目录

　　自己写的脚本：.inpg_ctl -D D:/DevTools/pgsql/data -l logfile start

5.2、powershell 连接 postgresql    <--返回目录

5.3、postgresql 操作    <--返回目录

　　l: 查看所有库

　　c test111: 切换库

　　新建 test111 数据库，新建表 ods.sl_account_00000000001 (字段与SQL Server对应，注意前后顺序一致)

5.4、postgresql 导入 .txt     <--返回目录

　　命令：COPY ods.sl_account_00000000001 from 'D:DevToolspgsqlsl_account.txt' with  csv header  delimiter ',' ENCODING 'utf8';

　　COPY 命令的使用参考：http://www.postgres.cn/docs/9.3/sql-copy.html（在表和文件之间拷贝数据）

 

6、中间遇到的问题    <--返回目录

1）SQL Server 源数据中有空白（比如回车换行）等特殊字符，导致导出的数据换行；

　　解决：参考本文标题3

2）使用 "|" 作为分隔符，发现有一条记录某个字段中间没了分隔符；

　　解决：使用 "," 或其他作为分隔符；单独对这条数据进行操作：update dbo.SL_Account set name='刘昕' where id=129929，将原本的空白（也不是回车、换行、制表符、空格，不知道是啥）

3）导出的 txt 为GBK, 所以需要将txt 转换为 UTF-8；

　　解决：参考本文标题4

4）postgresql 导入 txt，使用 COPY 命令时，需要已经存在的表；

5）postgresql 导入 txt 时，txt 第一行时字段，此时需要使用 with  csv header

参考
　　1）sql server中查询删除含有回车换行制表符的记录

　　2）sqlserver数据库 去除字段中空格，换行符，回车符(使用replace语句)

　　3）http://www.postgres.cn/docs/9.3/sql-copy.html（在表和文件之间拷贝数据）