csvgoogle-bigquerycreate-table

Upload and Create a table from a csv file in BigQuery

I have tried to create a table within a dataset that I already created and saved, the first csv file was uploaded successfully, but the second one I tried it shows the following error when I clicked the Create table: Failed to create table: Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 100; errors: 100. Please look into the errors[] collection for more details. Looking at the details it shows many messages as the below:

Error while reading data, error message: Invalid time zone: AM; line_number: 2 byte_offset_to_start_of_line: 15 column_index: 1 column_name: "Time" column_type: TIMESTAMP value: "4/1/2016 7:54:00 AM"

Error while reading data, error message: Invalid time zone: AM; line_number: 3 byte_offset_to_start_of_line: 50 column_index: 1 column_name: "Time" column_type: TIMESTAMP value: "4/1/2016 7:54:05 AM" Etc

How can I fixed those errors in the file? it is too big for Excel to open it...and I am very new with BigQuery and also R! Any help would be appreciated! Thanks a lot!

Solution

  • The timestamp values in your CSV (4/1/2016 7:54:00 AM) don't match the format expected by BigQuery. See Loading CSV data from Cloud Storage:

    When you load JSON or CSV data, values in TIMESTAMP columns must use a dash (-) or slash (/) separator for the date portion of the timestamp, and the date must be in one of the following formats: YYYY-MM-DD (year-month-day) or YYYY/MM/DD (year/month/day). The hh:mm:ss (hour-minute-second) portion of the timestamp must use a colon (:) separator.

    You need to generate your data using a timestamp format that BigQuery understands, or preprocess it before trying to load it into BigQuery.