Results 1 to 5 of 5
  1. #1
    Join Date
    May 2013
    Posts
    17

    Unanswered: Load CSV file into database

    hello,

    I want to load a csv file into the postgresql database(encoding UTF8)

    I was able to do that when both the file and postgres were on my local system.
    I used this command from psql :

    \copy my_table FROM D:/Data/my_file.csv DELIMITER ',' csv escape ''

    however, now i have to copy this file to a table which is on remote server and am unable to do it with this command as postgres expects the file on the same server as postgres and my file is on local sytem.

    I have gone through COPY command in postgres docs but i did not quite get how exactly i can achieve this. Also COPY requires that it be executed by only postgres user and cannot be executed with any other user and hence i used \copy (earlier) as it does not have any such requirement.

    Is it possible that I can achieve this with \copy? or if not what can i do ?

    Many thanks,
    Zubi

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Are you sure you used \copy and not copy ?

    The difference is that \copy is a psql command that can deal with files stored on the client. Whereas copy (without the backslash) is a SQL command that indeed can only handle files that are located on the server where Postgres is running.

    An alternative would be the COPY statement using "from stdin".

    Check the documentation for details:
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    May 2013
    Posts
    17
    Thanks for the response Shammat.

    yes i used \copy and not copy as copy requires to execute as super user.

    if possible, could you please give me an example how i can use STDIN with COPY in my case. I have gone through the docs but am not clear enough on that.

    Sorry i have not so technical background so example would be great help.
    In my case, will my local machine act as client to the postgres database on remote machine ,so in that case do i require connection string or something?

    Sorry if it is too basic

    Thanks,
    Zubi

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You can also use PGAdmin's Import utility to get the data through the client.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    May 2013
    Posts
    17
    thanks for the response Lou and Shammat.

    I was able to do it through SQL shell(psql) tool which is a command line tool
    I gave all the details of the remote server which it prompted for like Server name, database, port, user and password and after successfully logging in, I used the following command where I gave the path of the file on my local machine, I was done


    \copy raw.mytable FROM D:\Data\myfile.txt WITH DELIMITER '|' CSV HEADER

    (I needed to skip headers from the file while import hence I used CSV syntax)

    Alternatively, if you don't have that requirement, you can simply use:

    \copy raw.mytable FROM D:\Data\myfile.txt DELIMITER '|'

    Just keeping this note here so that any newbie like me can refer and avoid frustration

    Regards,
    Zubi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •