powershell copy data from csv to postgres

Question

hitting an issue where you hopefully can help me with: I want to import a csv file to postgres with powershell. For this have written following code:

$ConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$SQLServer;Port=$SLQPort;Database=$SQLDB;Uid=$SQLUser;Pwd=$SQLPass;"
$connection = New-Object System.Data.ODBC.ODBCConnection
$connection.ConnectionString = $ConnectionString

$connection.Open()

$query= "copy sensorhistory from 'C:\Powershell_Scripts\Postgres\Eurogate\data\export.csv' Delimiter ',' csv header;"

$DBCmd = $connection.CreateCommand();
$DBCmd.CommandText   = $query
$DBCmd.ExecuteNonQuery() | Out-Null
$DBCmd.ExecuteReader();

But I am getting the error "No such file or directory" the link is correct and already made changes to allow access to everybody, but still not working. Any help is welcome

Did you copy the CSV file to \\$SQLServer\C$\Powershell_Scripts\Postgres\Eurogate\data\export.csv first? — Mathias R. Jessen
– Mathias R. Jessen, Commented Mar 3, 2022 at 11:27
I am using a Windows machine for PostgresSQL which is in the same network. and try to read from the local machine and store the Data on the postgres Server — cyberoner1
– cyberoner1, Commented Mar 3, 2022 at 11:33
Right, but how would the DB engine access the local file system on the client machine? You either need to specify an accessible network path in the query (eg ... from '\\cyberoner1sLaptop\c$\...\export.csv' ...), or copy the CSV file to the machine that hosts Postgres before executing the query. — Mathias R. Jessen
– Mathias R. Jessen, Commented Mar 3, 2022 at 11:38
You are absolutly right..... thanks a lot, this helped. I changed the path to a network path and now access is working. — cyberoner1
– cyberoner1, Commented Mar 3, 2022 at 11:45
That's great, you're welcome! I've posted a proper answer below, consider marking it "accepted" by clicking the checkmark on the left of it :) — Mathias R. Jessen
– Mathias R. Jessen, Commented Mar 3, 2022 at 11:58

Mathias R. Jessen · Accepted Answer · 2022-03-03 11:55:16Z

You're pointing to the wrong file system!

From the $SQLServer argument in the connection string, it appears that Postgres might be hosted on a remote machine.

When encountering a file path like C:\[...]\export.csv in a query, the database engine is going to ask the operating system to open the corresponding file, and the operating system goes "C:\? That's a local drive, let me find that file for you" - and fails to locate the file because you actually meant the C:\ drive on your computer.

Two ways to fix this:

1. Pass a network path in the query:

Update the query so the file path explicitly points to a file share on a different machine:

$query = "copy sensorhistory from '\\ClientComputer01\ShareName\path\to\data\export.csv' Delimiter ',' csv header;"

2. Copy the file to the server that hosts Postgres

Of course, opening the file from the local filesystem wouldn't fail if the file also existed there. Assuming WinRM is configured in the environment and you have admin access on the DB server, copy the file ahead of time:

$remoteSession = New-PSSession -ComputerName $SQLServer
Copy-Item C:\Powershell_Scripts\Postgres\Eurogate\data\export.csv -Destination C:\Powershell_Scripts\Postgres\Eurogate\data\ -ToSession $remoteSession

Alternatively, copy the file over SMB if you have write access to a file share:

Copy-Item C:\Powershell_Scripts\Postgres\Eurogate\data\export.csv \\$SQLServer\ShareName\path\to\data\

After which the DB engine will be able to open the new local copy of the file and you can keep the local file path in the query.

Collectives™ on Stack Overflow

powershell copy data from csv to postgres

1 Answer 1

You're pointing to the wrong file system!

1. Pass a network path in the query:

2. Copy the file to the server that hosts Postgres

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

You're pointing to the wrong file system!

1. Pass a network path in the query:

2. Copy the file to the server that hosts Postgres

Comments

Your Answer

Sign up or log in

Post as a guest

Related