'BigQuery fails on parsing dates in M/D/YYYY format from CSV file

Problem

I'm attempting to create a BigQuery table from a CSV file in Google Cloud Storage.

I'm explicitly defining the schema for the load job (below) and set header rows to skip = 1.

Data

$ cat date_formatting_test.csv
id,shipped,name
0,1/10/2019,ryan
1,2/1/2019,blah
2,10/1/2013,asdf

Schema

id:INTEGER,
shipped:DATE,
name:STRING

Error

BigQuery produces the following error:

Error while reading data, error message: Could not parse '1/10/2019' as date for field shipped (position 1) starting at location 17

Questions

I understand that this date isn't in ISO format (2019-01-10), which I'm assuming will work.

However, I'm trying to define a more flexible input configuration whereby BigQuery will correctly load any date that the average American would consider valid.

  1. Is there a way to specify the expected date format(s)?
  2. Is there a separate configuration / setting to allow me to successfully load the provided CSV in with the schema defined as-is?


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source