DB

DateTime convert error between spreadsheet and BigQuery

Share this for your friends.

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".

Author


Mid-carieer engineer (AI, system). Good at Python and SQL.

Advantage to read

You can understand "DateTime convert error between spreadsheet and BigQuery". Then you will avoid this kind of error.


Data

First, we have to prepare data.

Today we use spreadsheet.


step
1
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
Spreadsheet

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

2021/09/08 20:0:00


step
2
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
SQL workspace CREATE TABLE


So detail window comes from right, and input details.

Settings are below.

parameter value
Source Drive
Select Drive URL (URL of the spreadsheet)
Schema -
Name (anything is OK)
Type DATETIME
Advanced options -
Header rows to skip 1


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.




SELECT

So try to select table that you created.

SELECT * FROM test.datetime_sample

Result is below.


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.

datetime on spreadsheet
datetime on spreadsheet


How can we modify it ?



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.

Change datetime to string
Change datetime to string


Then try to select it.


Ooooops! It's error!

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.



Change datetime format

So how about changing datetime format ?

Change delimiter from / to -.

2021-09-08 20:00:00


Then select table again.


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
Error example

So add ' to change it to text. Then we can select it in BigQuery table.

Success example
Success example


I understood. But I'm not convinced.
I see. Spredsheet's datetime should be acceptable in BigQuery because they are both on Google service.



There are some other articles about BigQuey.

If you interested in them, please read them.


Share this for your friends.

If you felt this article is useful, please share.

にほんブログ村 IT技術ブログへ

-DB
-,

© 2023 ITips