DateTime convert error between spreadsheet and BigQuery

I got datetime convert error when I tried to select datetime column of spreadsheet.

BigQuery(BQ) is very useful for data analysis or processing.

It is good at handling huge data. It returns summary result in short time.

And we can use spreadsheet as data source of BigQuery.

But you may get such kind of message when you try to select spreadsheet data.

Error while reading table: test.datetime_sample, 
error message: Could not convert value to datetime. Row 1; Col 0.

It seems like datetime error.

But how can we solve it ?

So today I introduce about "DateTime convert error between spreadsheet and BigQuery".

TOC

Prepare data

First, we have to prepare data.

Today we use spreadsheet.

STEP
Prepare spreadsheet data

In order to make new spreadsheet, you should click "New" on Google Drive.

Google Drive "New"
Google Drive "New"

Then you can see file options.

So click "Google (Spread)Sheet".

GoogleDrive Spreadsheet
GoogleDrive Spreadsheet

After that input data like below.

Spreadsheet

In this case, we need datetime value. So input like this.

2021/09/08 20:00:00

STEP
Create table

Next create table.

In order to create table, click three dot mark  and open on right side of the dataset. Then you can see dataset space to he right.

And you see the link "CREATE TABLE". Click it.

SQL workspace CREATE TABLE

So detail window comes from right, and input details.

Settings are below.

parametervalue
SourceDrive
Select Drive URL(URL of the spreadsheet)
Schema
Name(anything is OK)
TypeDATETIME
Advanced options
Header rows to skip1

Then table that its data source is spreadsheet is created.

But is does not have its own data.

It is just a reference table like VIEW.

So every time you select table, spreadsheet will be read.

Google スプレッドシートで複雑な CSV ファイルを BigQuery に読み込む | Google Cloud Blog

SELECT

So try to select table that you created.

SELECT * FROM test.datetime_sample

Result is below.

DateTime convert error

It shows error and error message is written like this.

Error while reading table: test.datetime_sample, 
error message: Could not convert value to datetime. Row 1; Col 0.

It says that there is a datetime conversion error at column 0 row 1.

But on spreadsheet, it shows calendar.

So it is obviously date value.

Spreadsheet shows calendar.

How can we modify it ?

Test : Change datetime to string

BigQuery did not accept datetime value on spreadsheet.

Then we can try with string(text) value.

In order to change datetime value to string, we can add ' on its head like Excel.

String data on spreadsheet

Then try to select the data on BigQuery.

Even we import string data, it shows error.

Ooooops! It shows error again!

Error message is below.

Error while reading table: theta-bliss-258006.test.datetime_sample, 
error message: Could not convert value to datetime. 
Error: Invalid datetime string "2021/09/08 20:00:00". Row 1; Col 0.

It says "datetime convert error" too.

But this time it recognized input value as 2021/09/08 20:00:00.

Test : Change datetime format

So how about changing datetime format ?

Change delimiter from / to -like below.

20210908 20:00:00

Datetime delimiter is changed.

Then select table again.

We can select datetime value.

OK! It seems success!

Not format YYYY/MM/DD hh:mm:ss, but format YYYY-MM-DD hh:mm:ss could be converted to datetime.

Conclusion

Today I introduced about "DateTime convert error between spreadsheet and BigQuery".

The root cause of datetime conversion error between spreadsheet and BigQuery is datetime format.

Datetime value of spreadsheet can't be converted to BigQuery datetime value.

Error example

So add ' to change it to text. And change delimiter from / to - .
Then we can select it in BigQuery table.

Success example

I understood. But I'm not convinced.

I see. Spreadsheet's datetime should be acceptable in BigQuery because they are both on Google service.

If you like this article, please
Like or Follow !

If you like this article, please share !

Author

karasanのアバター karasan System engineer

Mid-career engineer (AI, Data science, Salesforce, etc.).
Good at Python and SQL.

TOC