How do i copy the output of a powershell script (with multiple return values) into postgresql table?

Question

i have a small powershell script that returns 3 values:

test.ps1

return '2020-01-01', 'test1', 'test2'

and i want to copy these 3 values into a postgresql table with the copy from program command:

copy mytable FROM PROGRAM 'powershell -executionpolicy remotesigned -File test.ps1'

the problem is that powershell seems to output the values in three lines:

2020-02-01

test1

test2

and the copy command is giving me the error:

"missing data for column col1" SQL-State: 22007

(sorry for not including the complete message but it isn't in english)

how do i solve this?

I tried this:

test.ps1

return '2020-02-01, test1, test2'

but this returns only one value and fails with the same message.

I guess i could write the output of the powershell script to a textfile and copy it from there but this doesn't seem to be a good solution because i still need to call the script from the sql script somehow.

Thank You

edit: mytable has 3 three columns: (date, col1, col2)

IjonTichy · Accepted Answer · 2021-02-17 15:36:47Z

2

ok found the solution:

test.ps1

return '2020-02-01, test1, test2'

sql:

copy mytable FROM PROGRAM 'powershell -executionpolicy remotesigned -File test.ps1' DELIMITER ',' CSV;

answered Feb 17, 2021 at 15:36

IjonTichy

3611 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sami Kuhmonen Over a year ago

Probably better would be to return values separated by tab, now you risk a situation where having a comma in the data will break things, but if it never does then it will work.

IjonTichy Over a year ago

thanks @SamiKuhmonen for the tip, test1 and test2 will only be alphanumeric characters so it isn't an issue.

Collectives™ on Stack Overflow

How do i copy the output of a powershell script (with multiple return values) into postgresql table?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related