Thread: 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
Walter wrote: > 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 "" It should be ,, for NULL. > 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 : "" Have you tried WITH NULL AS ''? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Walter wrote:
>> 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 ""
> It should be ,, for NULL.
> Have you tried WITH NULL AS ''?
Won't help --- that will match a field that is empty and *doesn't* have
quotes.
It seems like the only way to read broken "CSV" files like this would be
to have some option that's sort of the opposite of FORCE NOT NULL ---
that is, force an empty-string data value to be treated as NULL even if
quoted. I'm not in favor of adding that particular frammish, but if
there are enough broken applications out there putting out this sort
of file, we may not have much choice...
Exactly where did this not-really-a-CSV-file come from?
regards, tom lane
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 > > > > > >
--- Walter <pgsql@thejohnsonplace.com> wrote: > 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 Walter, why not import the file into OOo Calc (or a spreadsheet of your choice) and remove all the " characters? you can then export as a CSV and it should work just fine. best of luck. __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
On Tue, December 6, 2005 12:01 pm, Walter said: > 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 : "" sed 's/,"",/,,/g' filename > filename.new Should do the trick... (That is: Replace ever occurance of ,"", with ,, in the file 'filename' and place the result in 'filename.new'. If you aren't on a Unix machine, you may have to find another tool to do the same.) Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
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 >