[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

COPY file from the SAME DIRECTORY LOCATION as the SQL Script File

I am using this copy function and want to make it part of a script deployment.  Is there a way to reference the current directory the script is in as the path.  For instance, I could place this copy script anywhere on a machine and then the FROM line of the COPY script would look like:  FROM '.../TestFile.txt', where the .../ represents the current file location of the script...I just don't want to have to manage changing this, but allow it to be copied to any folder and ran from any location...

COPY pbs."TestFile_Data"
FROM 'C:/MyFolder/TestFile.txt'
WITH DELIMITER E'\t'
CSV HEADER
QUOTE AS E'\t'
NULL AS '';
0
FreightTrain
Asked:
FreightTrain
  • 2
1 Solution
 
earth man2Commented:
with postgresql group privileges you can issue a psql command in a script.
psql -c "COPY pbs.\"TestFile_Data\"
FROM 'C:/MyFolder/TestFile.txt'
WITH DELIMITER E'\t'
CSV HEADER
QUOTE AS E'\t'
NULL AS '';" your_database_name
0
 
FreightTrainAuthor Commented:
earthman2,

You have been a tremendous help in many of my questions lately...Thank YOU!

What I am looking for is a way to run the script without knowing the drive letter or even the parent folder that the data file is in.  I want to have a Import.sql file that has the COPY command in it and it be in the same folder as the data file...

I am a ASP.NET guy (unfortunately), so if I were to do something like this in ASP.NET I would access the server.mappath('TestFile.txt') and it would return me that file as long as it is in the same location as my web page code is that calls it...

Hope this makes sense about what I am looking for...
0
 
adrpoCommented:

You could try to use ./TestFile.txt instead of 'C:/MyFolder/TestFile.txt'.
It might work, even if i'm not very sure. It says here:
http://www.postgresql.org/docs/8.1/static/sql-copy.html
filename
The absolute path name of the input or output file. Windows users might need to double backslashes used as path separators.


psql -c "COPY pbs.\"TestFile_Data\"
FROM './TestFile.txt'
WITH DELIMITER E'\t'
CSV HEADER
QUOTE AS E'\t'
NULL AS '';" your_database_name

You could also try to do this via ASP.NET.
Use path = server.mappath('TestFile.txt') to get the path, then assemble
the SQL command dynamically:

sql = "psql -c \"COPY pbs.\\\"TestFile_Data\\\" FROM " + path + "WITH DELIMITER E'\t'
CSV HEADER QUOTE AS E'\t' NULL AS \'';\" your_database_name";

Then send the <<sql>> string to the server.

Cheers,
za-k/



0
 
FreightTrainAuthor Commented:
Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now