FAQ
Hello,

I am having problems importing csv files into postgresql. I am hoping someone has had a similar problem and could help me troubleshoot.

I created a table that mimics the csv table I want to read from. I set privleges to "All" and have been attempting to execute the following procedure:

COPY BaxterCommercial WITH OIDS FROM 'H:\...\BaxterCommercial.csv'
USING DELIMITERS ',';

However, I keep getting the following error:

WARNING: nonstandard use of escape in a string literal
LINE 1: COPY BaxterCommercial WITH OIDS FROM 'H:\transpor\Traffic Co...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR: relation "baxtercommercial" does not exist

Obviously I am missing something, but I am not sure what it wants from me in terms of "escape string syntax"? I tried putting an \r at the end as well as a '\r\n'

Example:
COPY BaxterCommercial WITH OIDS FROM 'H:\...\BaxterCommercial.csv'
USING DELIMITERS ','
WITH NULL AS '\r\n';

Doing so resulted in the same error.

Thanks in advance for any help provided!

Kindra

Search Discussions

  • Tom Lane at Jun 22, 2009 at 8:10 pm

    Kindra Martinenko writes:
    I created a table that mimics the csv table I want to read from. I set privleges to "All" and have been attempting to execute the following procedure:
    COPY BaxterCommercial WITH OIDS FROM 'H:\...\BaxterCommercial.csv'
    USING DELIMITERS ',';
    You need to double the backslashes, or perhaps replace them with forward
    slashes. Read about string literal syntax here:
    http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

    regards, tom lane
  • Robert Schnabel at Jun 22, 2009 at 8:16 pm
    Kindra Martinenko wrote: Hello,
    I am having problems importing csv files into postgresql. I am hoping someone has had a similar
    problem and could help me troubleshoot.
    I created a table that mimics the csv table I want to read from. I set privleges to "All" and have
    been attempting to execute the following procedure:
    COPY BaxterCommercial WITH OIDS FROM 'H:\...\BaxterCommercial.csv' USING DELIMITERS
    ',';
    You need to escape the \ and maybe use the absolute path.
    http://www.postgresql.org/docs/8.3/interactive/sql-copy.html

    If you're using Windows try this but put the full path name instead of ... I use this frequently
    to load large files.
    COPY BaxterCommercial WITH OIDS FROM E'H:\\...\\BaxterCommercial.csv' WITH CSV;
    Bob
  • Kindra Martinenko at Jun 22, 2009 at 11:15 pm
    Thanks Robert & Tim, this was very helpful, however

    I tried:

    COPY BaxterCommercial
    FROM E'H:\\transpor\\Traffic Counts\\TMS\\MarApr09\\BaxterCommercial.csv'
    WITH CSV;

    And it returned with this error:


    ERROR: relation "baxtercommercial" does not exist

    Any ideas?

    Thanks,

    Kindra


    ________________________________
    From: Robert Schnabel <schnabelr@missouri.edu>
    To: Kindra Martinenko <kindramart@yahoo.com>
    Cc: pgsql-novice@postgresql.org
    Sent: Monday, June 22, 2009 1:15:57 PM
    Subject: Re: [NOVICE] Problems importing csv files


    Kindra Martinenko wrote:
    Hello,

    I am having problems importing csv files into postgresql. I am
    hoping someone has had a similar problem and could help me troubleshoot.


    I created a table that mimics the csv table I want to read from.
    I set privleges to "All" and have been attempting to execute the
    following procedure:


    COPY
    BaxterCommercial WITH OIDS FROM 'H:\...\BaxterCommercial.csv'
    USING
    DELIMITERS ',';
    You need to escape the \ and maybe use the absolute path.
    http://www.postgresql.org/docs/8.3/interactive/sql-copy.html

    If you're using Windows try this but put the full path name instead of
    ... I use this frequently to load large files.

    COPY
    BaxterCommercial WITH OIDS FROM E'H:\\...\\BaxterCommercial.csv'
    WITH CSV;
    Bob
  • Robert Schnabel at Jun 22, 2009 at 11:29 pm
    Kindra Martinenko wrote:
    I tried:
    COPY BaxterCommercial FROM E'H:\\transpor\\Traffic
    Counts\\TMS\\MarApr09\\BaxterCommercial.csv' WITH CSV;
    And it returned with this error:

    ERROR: relation "baxtercommercial" does not exist
    Any ideas? I'm by no stretch an expert, I'd consider myself a novice, but I ran into the same
    problem when I started. I assume you're using Windows right? Does your table name contain
    uppercase the way you have it written above? If it does, try changing the table name to all
    lowercase and change your COPY statement to lowercase. That's what worked for me. Scroll to
    the bottom of this link http://www.postgresql.org/docs/8.0/interactive/ddl.html I have
    resorted to just making sure all of my table names are lowercase.
  • Tim Ryan at Jun 25, 2009 at 1:36 pm
    On Jun 22, 2009, at 19:29, Robert Schnabel wrote:
    Kindra Martinenko wrote:

    I tried:

    COPY BaxterCommercial
    FROM E'H:\\transpor\\Traffic Counts\\TMS\\MarApr09\
    \BaxterCommercial.csv'
    WITH CSV;

    And it returned with this error:


    ERROR: relation "baxtercommercial" does not exist

    Any ideas?
    I'm by no stretch an expert, I'd consider myself a novice, but I ran
    into the same problem when I started. I assume you're using Windows
    right? Does your table name contain uppercase the way you have it
    written above? If it does, try changing the table name to all
    lowercase and change your COPY statement to lowercase. That's what
    worked for me. Scroll to the bottom of this link http://www.postgresql.org/docs/8.0/interactive/ddl.html
    I have resorted to just making sure all of my table names are
    lowercase.
  • Tim Ryan at Jun 25, 2009 at 1:53 pm

    On Jun 22, 2009, at 19:29, Robert Schnabel wrote:
    Kindra Martinenko wrote:

    I tried:

    COPY BaxterCommercial
    FROM E'H:\\transpor\\Traffic Counts\\TMS\\MarApr09\
    \BaxterCommercial.csv'
    WITH CSV;

    And it returned with this error:


    ERROR: relation "baxtercommercial" does not exist

    Any ideas?
    I do a lot of csv importing and exporting on Windows. On my setup I
    find it much easier to use forward slashes in the file path. I
    struggled with escaping the normal Windows backslashes but never got
    it to work.

    As for the error message, when you use the COPY query construct, the
    postgres service is trying to access the file as the postgres user
    that was created during install (assuming you didn't change the
    defaults), not the local or network Windows user you are logged in as.
    This can lead to some subtle permission denied errors, and as you can
    see, the error isn't properly descriptive in this case.

    A few ways to work around this: 1) use the psql client's \copy command
    instead, which will run as the local Windows user as you would expect.
    2) change the permissions on the folder or file in question so that
    the postgres user has permission to access the file using the SQL COPY
    command.

    There are a few other ways to work around the problem, but they go
    against best practices and create problems of their own.

    Hope this helps.

    Tim Ryan
  • Kindra Martinenko at Jun 26, 2009 at 4:38 am
    Thanks to Tim, Robert, et. al. for helping with the import csv problem. Using your suggestions, I believe I was able to rectify that particular issue.

    However, now I am having the problem of using the proper DATE and TIME syntax. I have looked in the Postgres manual, and it gives a whole long list of different date and time functions, but I am not sure which one I should use and how to integrate it into a "create table as" command.

    the csv file was created in MS Excel (yes I use Windows).

    At the moment, the easy work-around is to simply define each column as "text". The csv imported perfectly doing it this way, however, I want to use the proper syntax and formatting whenever possible:

    convert date as text (mm/dd/yyyy) to yyyy-mm-dd

    convert time as text (hh:mm) to time without timezone hh:mm:ss


    So, my question is, what do I need to do to my query language to ensure that posgresql processes the query successfully using the specified formats? I'm using v. 8.3 of PostgreSQL.

    thanks in advance,

    Kindra

    ________________________________
    From: Tim Ryan <twilight28@gmail.com>
    To: pgsql-novice@postgresql.org
    Cc: Kindra Martinenko <kindramart@yahoo.com>
    Sent: Thursday, June 25, 2009 6:53:03 AM
    Subject: Re: [NOVICE] Problems importing csv files




    On Jun 22, 2009, at 19:29, Robert Schnabel wrote:


    Kindra Martinenko wrote:
    I tried:


    COPY
    BaxterCommercial
    FROM
    E'H:\\transpor\\Traffic Counts\\TMS\\MarApr09\\BaxterCommercial.csv'
    WITH CSV;


    And it returned with this error:




    ERROR:
    relation "baxtercommercial" does not exist


    Any
    ideas?
    I do a lot of csv importing and exporting on Windows. On my setup I find it much easier to use forward slashes in the file path. I struggled with escaping the normal Windows backslashes but never got it to work.

    As for the error message, when you use the COPY query construct, the postgres service is trying to access the file as the postgres user that was created during install (assuming you didn't change the defaults), not the local or network Windows user you are logged in as. This can lead to some subtle permission denied errors, and as you can see, the error isn't properly descriptive in this case.

    A few ways to work around this: 1) use the psql client's \copy command instead, which will run as the local Windows user as you would expect. 2) change the permissions on the folder or file in question so that the postgres user has permission to access the file using the SQL COPY command.

    There are a few other ways to work around the problem, but they go against best practices and create problems of their own.

    Hope this helps.

    Tim Ryan
  • Guy Flaherty at Jun 26, 2009 at 6:12 am

    On Fri, Jun 26, 2009 at 2:38 PM, Kindra Martinenko wrote:
    Thanks to Tim, Robert, et. al. for helping with the import csv problem.
    Using your suggestions, I believe I was able to rectify that particular
    issue.

    However, now I am having the problem of using the proper DATE and TIME
    syntax. I have looked in the Postgres manual, and it gives a whole long
    list of different date and time functions, but I am not sure which one I
    should use and how to integrate it into a "create table as" command.

    the csv file was created in MS Excel (yes I use Windows).

    At the moment, the easy work-around is to simply define each column as
    "text". The csv imported perfectly doing it this way, however, I want to
    use the proper syntax and formatting whenever possible:

    convert date as text (mm/dd/yyyy) to yyyy-mm-dd

    convert time as text (hh:mm) to time without timezone hh:mm:ss


    So, my question is, what do I need to do to my query language to ensure
    that posgresql processes the query successfully using the specified formats?
    I'm using v. 8.3 of PostgreSQL.

    thanks in advance,

    Kindra
    You should be able to use the copy command but you may need to change the
    'DATESTYLE' configuration first. Usually this is set to ISO,MDY. If the
    data in your csv file is in the format of mm/dd/yyyy this should copy
    straight into a column that uses date as its type. The same should work fine
    for the times.

    You can check the value of datestyle before you run the copy command by
    using 'SHOW DATESTYLE;' If it doesn't come back with 'ISO,MDY' you can set
    it with 'SET datestyle = 'ISO,MDY' and then run your copy command again.

    Guy Flaherty
  • Kindra Martinenko at Jun 26, 2009 at 7:14 pm
    Thanks Guy--I'll try the datestyle thing, but ...

    Now, yet another problem I am having is being able to launch the psql command line. It gives me a "FATAL: password authentication failed for User "Kindra".

    This is the first time I have even touched psql, I usually work straight from PGAdmin III. When I type this after the prompt, it just keeps returning the same error, although I can access PGAdmin III with this password? I am confused.

    [command prompt line:\]psql U-Kindra
    password (trust level is set at md5): ******
    FATAL: password authentication failed for User "Kindra


    I launched the pg_hba.conf file, and attempted to set the trust level to "trust", however, Windows (being Windows)...would not allow me to save the .conf file? Does anyone have a workaround for FATAL password authentication errors and .conf file modification in a Windows environment?

    Thanks!

    Kindra



    ________________________________
    From: Guy Flaherty <naoshika@gmail.com>
    To: Kindra Martinenko <kindramart@yahoo.com>
    Cc: pgsql-novice@postgresql.org
    Sent: Thursday, June 25, 2009 11:12:39 PM
    Subject: Re: [NOVICE] Problems importing csv files




    On Fri, Jun 26, 2009 at 2:38 PM, Kindra Martinenko wrote:

    Thanks to Tim, Robert, et. al. for helping with the import csv problem. Using your suggestions, I believe I was able to rectify that particular issue.


    However, now I am having the problem of using the proper DATE and TIME syntax. I have looked in the Postgres manual, and it gives a whole long list of different date and time functions, but I am not sure which one I should use and how to integrate it into a "create table as" command.


    the csv file was created in MS Excel (yes I use Windows).


    At the moment, the easy work-around is to simply define each column as "text". The csv imported perfectly doing it this way, however, I want to use the proper syntax and formatting whenever possible:


    convert date as text (mm/dd/yyyy) to yyyy-mm-dd


    convert time as text (hh:mm) to time without timezone hh:mm:ss




    So, my question is, what do I need to do to my query language to ensure that posgresql processes the query successfully using the specified formats? I'm using v. 8.3 of PostgreSQL.


    thanks in advance,


    Kindra
    You should be able to use the copy command but you may need to change the 'DATESTYLE' configuration first. Usually this is set to ISO,MDY. If the data in your csv file is in the format of mm/dd/yyyy this should copy straight into a column that uses date as its type. The same should work fine for the times.

    You can check the value of datestyle before you run the copy command by using 'SHOW DATESTYLE;' If it doesn't come back with 'ISO,MDY' you can set it with 'SET datestyle = 'ISO,MDY' and then run your copy command again.

    Guy Flaherty
  • Guy Flaherty at Jun 26, 2009 at 10:31 pm

    On Sat, Jun 27, 2009 at 5:14 AM, Kindra Martinenko wrote:

    Thanks Guy--I'll try the datestyle thing, but ...

    Now, yet another problem I am having is being able to launch the psql
    command line. It gives me a "FATAL: password authentication failed for User
    "Kindra".

    This is the first time I have even touched psql, I usually work straight
    from PGAdmin III. When I type this after the prompt, it just keeps returning
    the same error, although I can access PGAdmin III with this password? I am
    confused.

    [command prompt line:\]psql U-Kindra
    password (trust level is set at md5): ******
    FATAL: password authentication failed for User "Kindra


    I launched the pg_hba.conf file, and attempted to set the trust level to
    "trust", however, Windows (being Windows)...would not allow me to save the
    .conf file? Does anyone have a workaround for FATAL password authentication
    errors and .conf file modification in a Windows environment?

    Thanks!

    Kindra
    Hmm, I have no experience with psql on windows. I am assuming you are using
    pgAdmin III on the server and not connecting to another machine. Perhaps you
    need to specify the actual database you are connecting to with psql? Maybe
    try with -D to specify the database.
  • Tom Lane at Jun 26, 2009 at 10:35 pm

    Guy Flaherty writes:
    On Sat, Jun 27, 2009 at 5:14 AM, Kindra Martinenko wrote:
    Now, yet another problem I am having is being able to launch the psql
    command line. It gives me a "FATAL: password authentication failed for User
    "Kindra".

    [command prompt line:\]psql U-Kindra
    password (trust level is set at md5): ******
    FATAL: password authentication failed for User "Kindra
    Hmm, I have no experience with psql on windows. I am assuming you are using
    pgAdmin III on the server and not connecting to another machine. Perhaps you
    need to specify the actual database you are connecting to with psql? Maybe
    try with -D to specify the database.
    I'm wondering what the *actual* username is (we see three different
    renderings of it above), and whether it contains any funny characters
    that pgAdmin will quote for you but cause trouble on a shell command
    line.

    regards, tom lane
  • Tguru at Jun 29, 2009 at 12:39 pm
    What you could fo is use an ETL tool. There are open source ETL tools that
    are free to download.

    Talend Open Studio is an open source ETL tool for data integration and
    migration experts. It's easy to learn for a non-technical user. What
    distinguishes Talend, when it comes to business users, is the tMap
    component. It allows the user to get a graphical and functional view of
    integration processes.
    For more information: http://www.talend.com/



    Tom Lane-2 wrote:
    Guy Flaherty <naoshika@gmail.com> writes:
    On Sat, Jun 27, 2009 at 5:14 AM, Kindra Martinenko
    wrote:
    Now, yet another problem I am having is being able to launch the psql
    command line. It gives me a "FATAL: password authentication failed for
    User
    "Kindra".

    [command prompt line:\]psql U-Kindra
    password (trust level is set at md5): ******
    FATAL: password authentication failed for User "Kindra
    Hmm, I have no experience with psql on windows. I am assuming you are
    using
    pgAdmin III on the server and not connecting to another machine. Perhaps
    you
    need to specify the actual database you are connecting to with psql?
    Maybe
    try with -D to specify the database.
    I'm wondering what the *actual* username is (we see three different
    renderings of it above), and whether it contains any funny characters
    that pgAdmin will quote for you but cause trouble on a shell command
    line.

    regards, tom lane

    --
    Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-novice
    --
    View this message in context: http://www.nabble.com/Problems-importing-csv-files-tp24154431p24253387.html
    Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
  • Ms swati chande at Jun 30, 2009 at 7:56 am
    I too am facing the same/ similar problem and have not been able to move ahead.

    As far as modifying pg_hba.conf is concerned, you may do it using edit from the command prompt. You may have to do away with the '.sample' part of it, I think.

    Swati

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 22, '09 at 8:05p
activeJun 30, '09 at 7:56a
posts14
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase