CODEWITHSUNDEEP
postgresqlpowershell
IjonTichy
IjonTichy

Reputation: 343

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

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)

Upvotes: 0

Views: 202

Answers (1)

IjonTichy
IjonTichy

Reputation: 343

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;

Upvotes: 2

Related Questions