Piping with Cat

Question

I cant seem to know that failing here. I intend Reading a file from a csv file to a Postgres DB. I have this

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

This is the CSV file that I'm using:

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

I am getting a

ERROR:  syntax error at or near "9090"

Please what am i doing wrong and is there a viable Bash alternative for this?

Rather than simply showing the command also indicate what are you trying to achieve (particularly using `tr`). — devnull, Jun 04 '14 at 13:26
Moreover, `cat` isn't a shell-builtin. It's a binary and can be used with any shell, so `bash cat` doesn't make much sense. — devnull, Jun 04 '14 at 13:32
@devnull the tr is used to translate double quotes to single quotes for compatibility with PostgreSQL.The Log is fed to an Insert Query — dev, Jun 04 '14 at 13:32
If you need to translate double quotes into single, use `tr '"' "'"` — devnull, Jun 04 '14 at 13:33
Thanks for the correction. Made the changes, but still got same error — dev, Jun 04 '14 at 13:44
You are piping tabulated data to `psql`. `psql` expects SQL commands. You would need to transform this into a series of `INSERT` statements. — larsks, Jun 04 '14 at 13:50
@larsks Thanks, you comment made me look at the Script building the Insert statement, the Issue was from there. Issue fixed. Thanks devnull for your inputs also — dev, Jun 04 '14 at 14:01

score 0 · Accepted Answer · answered Sep 18 '14 at 11:00

After passing your 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"

through the tr command, you end up, as expected with the quotes turned into spaces:

 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

However, psql has no idea what to do with these lines, it expects SQL commands.

If your intent is to create rows with that data, you'll need to do more manipulation to turn them into proper SQL statements. That will mean, among other things, replacing the " characters with ' (or deleting the quotes altogether for numeric columns), and turning them into the form:

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

where X is the field values you already have, but using single (or no) quotes as discussed above.

Taking a simpler example of a file:

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

you can use something like:

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

(on one line) to get the script 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');

Then pass that into psql to execute the commands.

Thanks!, Though i had solved the issue. I've learnt so much from your answer. Marking it as correct for informational purposes!. Thank You — dev, Sep 24 '14 at 09:54

Piping with Cat

1 Answers1