bash - 带 cat的管道

  显示原文与译文双语对照的内容
0 0

我似乎不知道这里失败了。 我打算从csv文件读取一个文件到Postgres数据库。 我有这个


cat Master.csv.2014-06-04-13-18-52 | tr "  | psql -U dev test_db

这是我使用的CSV文件:


"9090","1000","5000","default","2014-06-02 23:38:39","2014-06-02 23:38:39","2014-06-02 23:38:42","3","3","NORMAL_CLEARING","c8e1c005-3d32-460d-8188-21c5a841f33a","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-02 23:39:59","2014-06-02 23:39:59","2014-06-02 23:40:09","10","10","NORMAL_CLEARING","f9248341-bbec-49ed-af93-a574861cca3b","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-02 23:40:22","2014-06-02 23:40:22","2014-06-02 23:40:52","30","30","NORMAL_CLEARING","49f88527-0d1e-4511-a8b8-52aac64205e9","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-02 23:40:59","2014-06-02 23:40:59","2014-06-02 23:41:08","9","9","NORMAL_CLEARING","a78879f8-5ab8-4eb2-99ff-b1f562d0756a","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-02 23:41:33","2014-06-02 23:41:33","2014-06-02 23:41:37","4","4","NORMAL_CLEARING","671a7114-a81f-4515-9953-ae28248bedc6","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-02 23:43:13","2014-06-02 23:43:14","2014-06-02 23:43:17","4","3","NORMAL_CLEARING","bbc51324-74d3-4000-8e0c-d4daeeee0ac5","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-03 00:06:41","2014-06-03 00:06:41","2014-06-03 00:06:48","7","7","NORMAL_CLEARING","5bb33949-116f-41a3-a264-c192dbb824e9","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-03 00:09:35","2014-06-03 00:09:35","2014-06-03 00:09:37","2","2","NORMAL_CLEARING","5fcbc6b7-a697-4855-b550-2b8af5de328a","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-03 00:13:35","2014-06-03 00:13:35","2014-06-03 00:13:40","5","5","NORMAL_CLEARING","770f28be-9355-4fe4-86a7-47d28048e022","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-03 00:13:45","2014-06-03 00:13:45","2014-06-03 00:13:54","9","9","NORMAL_CLEARING","ee8a5e90-1495-4f41-9d8e-3be9c9918437","","1000","GSM","GSM"

我得到了一个


ERROR: syntax error at or near"9090"

请注意我做了什么错误,是否有可行的Bash替代方案?

时间: 原作者:

0 0

在传递 CSV file: 之后


"9090","1000","5000","default","2014-06-02 23:38:39","2014-06-02 23:38:39","2014-06-02 23:38:42","3","3","NORMAL_CLEARING","c8e1c005-3d32-460d-8188-21c5a841f33a","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-02 23:39:59","2014-06-02 23:39:59","2014-06-02 23:40:09","10","10","NORMAL_CLEARING","f9248341-bbec-49ed-af93-a574861cca3b","","1000","GSM","GSM"
"1000","1000","5000","default","2014-06-02 23:40:22","2014-06-02 23:40:22","2014-06-02 23:40:52","30","30","NORMAL_CLEARING","49f88527-0d1e-4511-a8b8-52aac64205e9","","1000","GSM","GSM"

通过 tr 命令,你可以像引号中的引号一样结束:


 9090, 1000, 5000, default, 2014-06-02 23:38:39, 2014-06-02 23:38:39, 2014-06-02 23:38:42, 3, 3, NORMAL_CLEARING, c8e1c005-3d32-460d-8188-21c5a841f33a,, 1000, GSM, GSM 
 1000, 1000, 5000, default, 2014-06-02 23:39:59, 2014-06-02 23:39:59, 2014-06-02 23:40:09, 10, 10, NORMAL_CLEARING, f9248341-bbec-49ed-af93-a574861cca3b,, 1000, GSM, GSM 
 1000, 1000, 5000, default, 2014-06-02 23:40:22, 2014-06-02 23:40:22, 2014-06-02 23:40:52, 30, 30, NORMAL_CLEARING, 49f88527-0d1e-4511-a8b8-52aac64205e9,, 1000, GSM, GSM 

但是,psql 不知道如何处理这些行,它需要SQL命令。

如果你的意图是使用这些数据创建行,你需要做更多操作来将它们转换成正确的SQL语句。 这将意味着,将 " 字符替换为 ' ( 或者完全删除数字列的引号),并将它的转换为形式:


insert into sometable (column1, column2) values (X)

其中 X 是字段值,但是使用了上面讨论的单( 或者不) 引号。

以一个简单的file: 为例


"9090","1000","2000"
"3000","4000","5000"
"6000","7000","8000"

你可以使用以下内容:


cat Master.csv
 | tr '"'"'"
 | sed -e 's/^/insert into mytable(col1,col2,col3) values (/'
 -e 's/$/);/'

( 在一行中) 获取脚本 file:


insert into mytable(col1,col2,col3) values ('9090','1000','2000');
insert into mytable(col1,col2,col3) values ('3000','4000','5000');
insert into mytable(col1,col2,col3) values ('6000','7000','8000');

然后将传递给,执行命令。

原作者:
...