Thread: COPY table to file missing quotation marks
I'd like to understand what I'm missing when I COPY a table to a .csv file but the text is not quoted. Here's an example of a command: copy chemistry to '/home/postgres/emapchem.csv' with (format CSV, header True, quote '"', delimiter '|'); and here're part of the header and following row of the output: siteid|sampdate|analdate|ph|cond|turb|tss|doc|dic|p|se|ca|mg|na|k|nh4|so4|no3 1993SBOIA049|2004-06-28|2004-06-28|7.67|117|4.21|6.1|1.13|11.96|40|0.2|503.5|206.2 I'm sure it's something simple in the syntax I keep getting wrong, but I don't see it. Rich
On 01/31/2013 08:37 AM, Rich Shepard wrote: > I'd like to understand what I'm missing when I COPY a table to a .csv > file > but the text is not quoted. > > Here's an example of a command: > > copy chemistry to '/home/postgres/emapchem.csv' with (format CSV, header > True, quote '"', delimiter '|'); > > and here're part of the header and following row of the output: > > siteid|sampdate|analdate|ph|cond|turb|tss|doc|dic|p|se|ca|mg|na|k|nh4|so4|no3 > > 1993SBOIA049|2004-06-28|2004-06-28|7.67|117|4.21|6.1|1.13|11.96|40|0.2|503.5|206.2 > > > I'm sure it's something simple in the syntax I keep getting wrong, but I > don't see it. http://www.postgresql.org/docs/9.2/interactive/sql-copy.html CSV Format ... "The values in each record are separated by the DELIMITER character. If the value contains the delimiter character, the QUOTE character, the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character. You can also use FORCE_QUOTE to force quotes when outputting non-NULL values in specific columns." ... FORCE_QUOTE Forces quoting to be used for all non-NULL values in each specified column. NULL output is never quoted. If * is specified, non-NULL values will be quoted in all columns. This option is allowed only in COPY TO, and only when using CSV format. > > Rich > > > -- Adrian Klaver adrian.klaver@gmail.com
On Thu, 31 Jan 2013, Adrian Klaver wrote: > http://www.postgresql.org/docs/9.2/interactive/sql-copy.html Adrian, I've read this. > CSV Format > ... > "The values in each record are separated by the DELIMITER character. If > the value contains the delimiter character, the QUOTE character, the NULL > string, a carriage return, or line feed character, then the whole value is > prefixed and suffixed by the QUOTE character, and any occurrence within > the value of a QUOTE character or the ESCAPE character is preceded by the > escape character. You can also use FORCE_QUOTE to force quotes when > outputting non-NULL values in specific columns." ... So, if the column attribute is text of some flavor I still need to force quotes column-by-column? Guess I still need to import the output into a spreadsheet, format each text and date column, then write it out again as a .csv file ... or, better yet, write a one-line awk program that quotes the appropriate tokens! That's what I'll do. Thanks, Rich
On 01/31/2013 08:46 AM, Adrian Klaver wrote: > On 01/31/2013 08:37 AM, Rich Shepard wrote: >> I'd like to understand what I'm missing when I COPY a table to a .csv >> file >> but the text is not quoted. >> ... > http://www.postgresql.org/docs/9.2/interactive/sql-copy.html > > CSV Format > > ... > "The values in each record are separated by the DELIMITER character. > If the value contains the delimiter character, the QUOTE character, > the NULL string, a carriage return, or line feed character, then the > whole value is prefixed and suffixed by the QUOTE character, and any > occurrence within the value of a QUOTE character or the ESCAPE > character is preceded by the escape character. You can also use > FORCE_QUOTE to force quotes when outputting non-NULL values in > specific columns." ... > > > FORCE_QUOTE > Forces quoting to be used for all non-NULL values in each specified > column. NULL output is never quoted. If * is specified, non-NULL > values will be quoted in all columns. This option is allowed only in > COPY TO, and only when using CSV format. > Adrian beat me to answering so I'll just add that different systems generate and require slightly different CSV formats and though RFC4180 attempts to offer guidance, even that document states: Definition of the CSV Format: While there are various specifications and implementations for the CSV format...there is no formal specification in existence, which allows for a wide variety of interpretations of CSV files. Cheers, Steve
On Thu, 31 Jan 2013, Steve Crawford wrote: > Definition of the CSV Format: > > While there are various specifications and implementations for the CSV > format...there is no formal specification in existence, which allows for a > wide variety of interpretations of CSV files. Steve, It's been this way since the early 1980s. Rich
On 01/31/2013 09:03 AM, Rich Shepard wrote: > On Thu, 31 Jan 2013, Steve Crawford wrote: > >> Definition of the CSV Format: >> >> While there are various specifications and implementations for the CSV >> format...there is no formal specification in existence, which allows >> for a >> wide variety of interpretations of CSV files. > > Steve, > > It's been this way since the early 1980s. > I know. I just find it an amusing contrast to the usual liberal sprinkling of SHALL, MUST, SHOULD, ... that pepper most RFCs. And I've encountered too many people who believed there actually *was* a solid definition. Cheers, Steve
On 01/31/2013 08:53 AM, Rich Shepard wrote: > On Thu, 31 Jan 2013, Adrian Klaver wrote: > >> http://www.postgresql.org/docs/9.2/interactive/sql-copy.html > > Adrian, > > I've read this. > >> CSV Format >> ... >> "The values in each record are separated by the DELIMITER character. If >> the value contains the delimiter character, the QUOTE character, the NULL >> string, a carriage return, or line feed character, then the whole >> value is >> prefixed and suffixed by the QUOTE character, and any occurrence within >> the value of a QUOTE character or the ESCAPE character is preceded by the >> escape character. You can also use FORCE_QUOTE to force quotes when >> outputting non-NULL values in specific columns." ... > > So, if the column attribute is text of some flavor I still need to force > quotes column-by-column? Or from: FORCE_QUOTE Forces quoting to be used for all non-NULL values in each specified column. NULL output is never quoted. If * is specified, non-NULL values will be quoted in all columns. This option is allowed only in COPY TO, and only when using CSV format. Use FORCE_QUOTE * to quote all columns. > > Guess I still need to import the output into a spreadsheet, format each > text and date column, then write it out again as a .csv file ... or, better > yet, write a one-line awk program that quotes the appropriate tokens! > That's > what I'll do. > > Thanks, > > Rich > > > -- Adrian Klaver adrian.klaver@gmail.com
On Thu, 31 Jan 2013, Adrian Klaver wrote: > FORCE_QUOTE Forces quoting to be used for all non-NULL values in each > specified column. NULL output is never quoted. If * is specified, > non-NULL values will be quoted in all columns. This option is allowed only > in COPY TO, and only when using CSV format. > Use FORCE_QUOTE * to quote all columns. Adrian, That's correct. Most of the columns are numeric so each text column needs to be specified. Thanks, Rich
On 01/31/2013 10:17 AM, Rich Shepard wrote: > On Thu, 31 Jan 2013, Adrian Klaver wrote: > >> FORCE_QUOTE Forces quoting to be used for all non-NULL values in each >> specified column. NULL output is never quoted. If * is specified, >> non-NULL values will be quoted in all columns. This option is allowed >> only >> in COPY TO, and only when using CSV format. > >> Use FORCE_QUOTE * to quote all columns. > > Adrian, > > That's correct. Most of the columns are numeric so each text column > needs > to be specified. Any particular reason why? > > Thanks, > > Rich > > > -- Adrian Klaver adrian.klaver@gmail.com
On 2013-01-31, Rich Shepard <rshepard@appl-ecosys.com> wrote: > I'd like to understand what I'm missing when I COPY a table to a .csv file > but the text is not quoted. > > Here's an example of a command: > > copy chemistry to '/home/postgres/emapchem.csv' with (format CSV, header True, quote '"', delimiter '|'); > > and here're part of the header and following row of the output: > > siteid|sampdate|analdate|ph|cond|turb|tss|doc|dic|p|se|ca|mg|na|k|nh4|so4|no3 > 1993SBOIA049|2004-06-28|2004-06-28|7.67|117|4.21|6.1|1.13|11.96|40|0.2|503.5|206.2 > > I'm sure it's something simple in the syntax I keep getting wrong, but I > don't see it. Quotes are optional in csv except for strings containg linebreaks, quotes, or delimiters where they are required. -- ââ 100% natural
On Sat, Feb 2, 2013 at 10:03 PM, Jasen Betts <jasen@xnet.co.nz> wrote: > Quotes are optional in csv except for strings containg linebreaks, quotes, > or delimiters where they are required. > It really depends on whose definition of CSV you use. Please just don't use Excel's output as your example/standard.