Thread: Problems importing csv files

Problems importing csv files

From

Kindra Martinenko

Date:

22 June 2009, 20:05:23

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

Re: Problems importing csv files

From

Tom Lane

Date:

22 June 2009, 20:10:08

Kindra Martinenko <kindramart@yahoo.com> writes:
> I created a table that mimics the csv table I want to read from.  I set privleges to "All" and have been attempting
toexecute 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

Re: Problems importing csv files

From

Robert Schnabel

Date:

22 June 2009, 20:16:06

Kindra Martinenko wrote:
<blockquote cite="mid:249550.32069.qm@web30207.mail.mud.yahoo.com"
 type="cite">

  <div
 style="font-family: arial,helvetica,sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
  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:




  <font class="Apple-style-span"
 face="'Courier New', courier, monaco, monospace, sans-serif">COPY
BaxterCommercial WITH OIDS FROM 'H:\...\BaxterCommercial.csv'
  <font class="Apple-style-span"
 face="'Courier New', courier, monaco, monospace, sans-serif">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.
<font class="Apple-style-span"
 face="'Courier New', courier, monaco, monospace, sans-serif">COPY
BaxterCommercial WITH OIDS FROM E'H:\\...\\BaxterCommercial.csv'
<font class="Apple-style-span"
 face="'Courier New', courier, monaco, monospace, sans-serif">WITH CSV;

Bob

Re: Problems importing csv files

From

Kindra Martinenko

Date:

22 June 2009, 23:15:51

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

Re: Problems importing csv files

From

Robert Schnabel

Date:

22 June 2009, 23:29:36

Kindra Martinenko wrote:
<blockquote cite="mid:448995.80419.qm@web30207.mail.mud.yahoo.com"
 type="cite">


  I tried:
  <font class="Apple-style-span"
 face="'Courier New', courier, monaco, monospace, sans-serif">


  <font class="Apple-style-span"
 face="'Courier New', courier, monaco, monospace, sans-serif">COPY
BaxterCommercial 
  <font class="Apple-style-span"
 face="'Courier New', courier, monaco, monospace, sans-serif">FROM
E'H:\\transpor\\Traffic Counts\\TMS\\MarApr09\\BaxterCommercial.csv'
  <font class="Apple-style-span"
 face="'Courier New', courier, monaco, monospace, sans-serif">WITH CSV;


  <font class="Apple-style-span"
 face="arial, helvetica, sans-serif">And it returned with this error:


  <font class="Apple-style-span"
 face="'Courier New', courier, monaco, monospace, sans-serif">


  <font
 class="Apple-style-span"
 face="'Courier New', courier, monaco, monospace, sans-serif">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.

Re: Problems importing csv files

From

Tim Ryan

Date:

25 June 2009, 13:36:59

On Jun 22, 2009, at 19:29, Robert Schnabel <schnabelr@missouri.edu> 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.

Re: Problems importing csv files

From

Tim Ryan

Date:

25 June 2009, 13:53:21

On Jun 22, 2009, at 19:29, Robert Schnabel <schnabelr@missouri.edu> 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

Re: Problems importing csv files

From

Kindra Martinenko

Date:

26 June 2009, 04:38:55

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 <schnabelr@missouri.edu> 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?

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

Re: Problems importing csv files

From

Guy Flaherty

Date:

26 June 2009, 06:12:43

On Fri, Jun 26, 2009 at 2:38 PM, Kindra Martinenko <kindramart@yahoo.com> 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

Problems using psql--Fatal password authentication failed

From

Kindra Martinenko

Date:

26 June 2009, 19:14:37

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 <kindramart@yahoo.com> 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

Re: Problems using psql--Fatal password authentication failed

From

Guy Flaherty

Date:

26 June 2009, 22:31:43

On Sat, Jun 27, 2009 at 5:14 AM, Kindra Martinenko <kindramart@yahoo.com> 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.

Re: Problems using psql--Fatal password authentication failed

From

Tom Lane

Date:

26 June 2009, 22:35:15

Guy Flaherty <naoshika@gmail.com> writes:
> On Sat, Jun 27, 2009 at 5:14 AM, Kindra Martinenko <kindramart@yahoo.com>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

Re: Problems using psql--Fatal password authentication failed

From

Tguru

Date:

06 July 2009, 04:49:20

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
>> <kindramart@yahoo.com>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.