Request for TSV data export.

577 views
Skip to first unread message

Hayato Tomoda

unread,
Apr 18, 2012, 12:15:11 PM4/18/12
to bigquery...@googlegroups.com
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

unread,
Apr 18, 2012, 12:32:58 PM4/18/12
to bigquery...@googlegroups.com
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

unread,
Apr 18, 2012, 1:36:43 PM4/18/12
to bigquery...@googlegroups.com
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

unread,
Apr 19, 2012, 6:06:22 PM4/19/12
to bigquery...@googlegroups.com
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

unread,
Apr 19, 2012, 6:29:36 PM4/19/12
to bigquery...@googlegroups.com
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

unread,
Apr 22, 2012, 4:06:30 AM4/22/12
to bigquery...@googlegroups.com
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

unread,
Apr 22, 2012, 4:08:25 AM4/22/12
to bigquery...@googlegroups.com
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
Reply all
Reply to author
Forward
0 new messages