Re: CSV file - Using COPY Command - Double-Quotes

Found this in the archives:


http://archives.postgresql.org/pgsql-general/2000-09/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
>> [mailto:pgsql-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
>