First, this file comes from a government agency already formatted, so
it's obviously not up to me whether it comes with the quotes or not.

Secondly, I could do a find/replace so that there are NO quotation marks
at all, yet I am working on a process that will download these files
from one webserver directly to mine, then COPY them into my PG db.

Unless there is syntax that I can use, I will have to find a script of
some kind to run on my dedicated server after dloading the file before
using the copy command.

I am trying to automate this process as it will have to be done on
multiple files weekly. I can't spend the time using a text editor by
hand to remove the quotation marks.

I am looking into contacting those who produce the files and aking if
they can stop putting in the quotation marks.

But any other suggestions are appreciated.

Thanks,

Walter


<><

...."I think not," said Descartes, and promptly disappeared.






Wes Williams wrote:

>How about opening your .csv file in a text editor of your choice and using a
>`find & replace` for " " to "null" ?
>
>>From one noob to the next. Perhaps you'll get a better response on the
>[General] list as it seems more of the guru's are on that list.
>
>-----Original Message-----
>From: pgsql-novice-owner@postgresql.org
>[mailtogsql-novice-owner@postgresql.org]On Behalf Of Walter
>Sent: Tuesday, December 06, 2005 12:01 PM
>To: pgsql-novice@postgresql.org
>Subject: [NOVICE] CSV file - Using COPY Command - Double-Quotes
>
>
>I have a problem using the copy command on a CSV file.
>
>All of the values within the CSV are surrounded with quotation marks. So
>a null value for a field looks like ""
>
>I am trying to import the file and no matter what I do, it hits a null
>date field and says
>
>invalid input syntax for type date : ""
>
>How do I get the copy command to simply enter a null value for the "" it
>finds for the date field in the CSV file?
>
>BTW - None of my fields are set as "NOT NULL". They can all accept null
>values.
>
>Please help.
>
>Walter
>PG Noob
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: don't forget to increase your free space map settings
>
>
>
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Found this in the archives:


http://archives.postgresql.org/pgsql...9/msg00818.php

btw -- I am guessing they are using Filemaker - which I believe has a
number of export options. May want to ask them to take a look at their
options.

Mike E.



Walter wrote:

> First, this file comes from a government agency already formatted, so
> it's obviously not up to me whether it comes with the quotes or not.
>
> Secondly, I could do a find/replace so that there are NO quotation
> marks at all, yet I am working on a process that will download these
> files from one webserver directly to mine, then COPY them into my PG db.
>
> Unless there is syntax that I can use, I will have to find a script of
> some kind to run on my dedicated server after dloading the file before
> using the copy command.
>
> I am trying to automate this process as it will have to be done on
> multiple files weekly. I can't spend the time using a text editor by
> hand to remove the quotation marks.
>
> I am looking into contacting those who produce the files and aking if
> they can stop putting in the quotation marks.
>
> But any other suggestions are appreciated.
>
> Thanks,
>
> Walter
>
>
> <><
>
> ..."I think not," said Descartes, and promptly disappeared.
>
>
>
>
>
>
> Wes Williams wrote:
>
>> How about opening your .csv file in a text editor of your choice and
>> using a
>> `find & replace` for " " to "null" ?
>>
>>> From one noob to the next. Perhaps you'll get a better response on the
>>
>> [General] list as it seems more of the guru's are on that list.
>>
>> -----Original Message-----
>> From: pgsql-novice-owner@postgresql.org
>> [mailtogsql-novice-owner@postgresql.org]On Behalf Of Walter
>> Sent: Tuesday, December 06, 2005 12:01 PM
>> To: pgsql-novice@postgresql.org
>> Subject: [NOVICE] CSV file - Using COPY Command - Double-Quotes
>>
>>
>> I have a problem using the copy command on a CSV file.
>>
>> All of the values within the CSV are surrounded with quotation marks. So
>> a null value for a field looks like ""
>>
>> I am trying to import the file and no matter what I do, it hits a null
>> date field and says
>>
>> invalid input syntax for type date : ""
>>
>> How do I get the copy command to simply enter a null value for the "" it
>> finds for the date field in the CSV file?
>>
>> BTW - None of my fields are set as "NOT NULL". They can all accept null
>> values.
>>
>> Please help.
>>
>> Walter
>> PG Noob
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>>
>>
>>
>>
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq