CODEWITHSUNDEEP
csvgoogle-bigquery
Philippe Hebert
Philippe Hebert

Reputation: 2028

googleapis / python-bigquery: BadRequest: Could not parse as DATE with message 'Unable to parse'

Given the following code:

with io.StringIO() as buf:
    buf.write(df_data.to_csv(header=True, index=False, quoting=csv.QUOTE_NONNUMERIC))
    buf.seek(0)
    try:
        job = self.client.load_table_from_file(buf, dest_table)
        job.result()
    except:
        buf.seek(0)
        LOG.error("Failed to upload dataframe as csv: \n\n%s\n", buf.read())
        raise

I am trying to load a pandas DataFrame to a bigquery table via converting to a CSV first. The problem I am faced with is that the BigQuery API fails with

google.api_core.exceptions.BadRequest: 400 Error while reading data, error message: Could not parse 'date_key' as DATE for field date_key (position 3) starting at location 0 with message 'Unable to parse'

I looked at this other issue, and there's seem to be a limitation on the accepted formats for DATEs when loading a CSV file.

This being said, the prints from the above except block results in the following:

ERROR    utils.database._bigquery:_bigquery.py:255 Failed to upload dataframe as csv:

"clinic_key","schedule_template_time_interval_key","schedule_template_key","date_key","schedule_owner_key","schedule_template_schedule_track_key","schedule_content_label_key","start_time_key","end_time_key","priority"
"clitest11111111111111111111111","1","1","2021-01-01","1","1","1","19:00:00","21:00:00",1
"clitest11111111111111111111111","1","1","2021-01-01","1","1","2","20:00:00","20:30:00",2
"clitest11111111111111111111111","1","1","2021-01-01","1","1","3","20:20:00","20:30:00",3

Which to me seems to be a clearly well-formatted CSV file.

So my question is: How can I make BigQuery accept my CSV? What do I have to change?

N.B: I know there's a load_dataframe_to_table method on the bigquery.client.Client object, but I faced another issue that forced me to attempt the CSV method instead. See link to other issue here.

Upvotes: 0

Views: 1711

Answers (1)

DazWilkin
DazWilkin

Reputation: 40081

You need to drop the header row.

That location 0 suggests it dislikes the first row.

Your other date values look correct (YYYY-MM-DD).

Because column ordering is important with CSV, BigQuery can assume the mapping to its table.

Upvotes: 2

Related Questions