• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 674
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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