猫管

Question

我似乎不知道这里的失败。我打算从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替代方案？

Answer 1

传递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"

通过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是您已经拥有的字段值，但是使用了上面讨论过的单引号(或非引号)。

举一个简单的文件示例：

"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/$/);/'

(在一行上)获取脚本文件：

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');

然后将其传递到psql以执行命令。