Request for TSV data export.

[Hayato Tomoda]

I am processing a data containing many commas inside single column, and it seems that BigQuery is only providing CSV downloads which brakes up column structures when I import it to Excel. Strongly request a download option of tab-separated values. 

[Michael Sheldon]

Hello Hayato Tomoda,

BigQuery supports user-specified field delimiters.

If you are using the bq command line tool, see this recent post on bigquery-discuss: https://groups.google.com/group/bigquery-discuss/browse_thread/thread/4cabec354e746d74/8d8cd53ccd2c2471

The web ui also allows specifying field delimiters. During Create Table, press "click to see advanced options" and you will see several options you can choose, one of which is "Tab".

For those writing custom code to talk to the BigQuery Api directly, within the load job configuration setting, you can specify the field delimiter via the "fieldDelimiter" key.

Standard CSV quoting rules apply: if your strings contain a field delimiter in them, you must quote the string in the input data. 

Cheers,

--Michael Sheldon

[Michael Sheldon]

Hello Hayato Tomoda,

My apologies: I mis-read this as a request for field delimiters for the load operation.

You are asking about custom field delimiters for export requests.

We currently do not support this functionality. Thank you for the suggestion: we have added this to our feature requests.

Thank you,

--Michael Sheldon

[Jim Caputo]

Hi Hayato Tomoda,

I tested an export from BigQuery with embedded commas in a column.  We wrap these columns in double quotes, which should be sufficient for opening in Excel.  Can you explain exactly what's happening, or pass along a sample?

Thanks

Jim

On Wed, Apr 18, 2012 at 9:15 AM, Hayato Tomoda <tomo...@gmail.com> wrote:

[Michael Sheldon]

This might be Excel following the Locale setting setting for "column separator", which I suspect in Hayato Tomoda's case is not ",".

I've found several references to this issue on the web. Apparently the work around is to set the column separator explicitly to "," in Excel before the import.

The second page of Excel's Text Import Wizard allows you to select the column separator.

I hope this helps,

--Michael Sheldon

[Hayato Tomoda]

Hi Jim,

Thank you for the reply.

The export function certainly wraps embedded commas in a column and I can open correctly in Excel, but when parsing the csv file with programming languages like Python or Ruby, it goes tricky because I need to write regular expressions to search for outer double quotes.

For example, 

id, message, comment

1, "Parsing this line is pretty simple", "because this line does not contain any comma"

2, "But when lines like this, when commas are embedded in column", "it becomes a little complicated, because I need additional code for parsing the line"

3, "Also, this line is a "nightmare", because containing not only commas, but also "double quotes".", "some record has "doubled-double-quotes" like """HI GUYS""""

Well, this problem is related on developer skills, but I think BigQuery is a tool to manage and filter the big data as easy as possible (with enormous speed), and it would be really nice for you to support exporting in TSV.

Regards,

2012年4月20日金曜日 7時06分22秒 UTC+9 Jim Caputo:

Hi Hayato Tomoda,

I tested an export from BigQuery with embedded commas in a column.  We wrap these columns in double quotes, which should be sufficient for opening in Excel.  Can you explain exactly what's happening, or pass along a sample?

Thanks

Jim

[Hayato Tomoda]

Hi Micheal, 

No problem at all :)

2012年4月19日木曜日 2時36分43秒 UTC+9 Michael Sheldon:

Hello Hayato Tomoda,

My apologies: I mis-read this as a request for field delimiters for the load operation.

You are asking about custom field delimiters for export requests.

We currently do not support this functionality. Thank you for the suggestion: we have added this to our feature requests.

Thank you,

--Michael Sheldon