r/bigquery • • Jan 23 '22

Problem creating a table using CSV

I'm a beginner at BQ and have been struggling to create a table out of CSV. I'm using the console, added the Schema, then the error happens.

Failed to create table: Error while reading data, error message: Could not parse '2018/12/21' as DATE for field pg_start_dt (position 3) starting at location 0 with message 'Unable to parse'

For the date is there any rules that I need to follow? I couldn't find it anywhere.

0 Upvotes

4 comments sorted by

4

u/Financial_Pie_3624 Jan 23 '22

I had the same issue loading the table from the csv. You have two options:

  1. Format the csv column cells with this format YYYY-MM-DD…that’s it, as long as your table data type is date it will work.

  2. Create your table in BigQuery and make sure all columns are STRING data type…then use the PARSE_DATE function to cast the datetime column from string to date. DM me if you need more help.

2

u/buangakun3 Jan 23 '22

Thank you will DM you about it.

4

u/Woffice Jan 23 '22

It looks like the date format that Bigquery is looking for is YYYY-MM-DD according to this: https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv

2

u/usaussie Jan 23 '22 edited Jun 10 '23

In support of 3rd party apps, and in protest against reddit's handling of the situation with ApolloApp (r/apolloapp), I've used PowerDeleteSuite (https://github.com/j0be/PowerDeleteSuite/) to edit my posts and comments, before eventually removing my account from reddit. The more people that see how reddit is handling this situation the better.

https://www.reddit.com/r/Save3rdPartyApps

https://www.reddit.com/r/apolloapp/comments/144f6xm/apollo_will_close_down_on_june_30th_reddits/

https://www.reddit.com/r/Save3rdPartyApps/comments/13yh0jf/dont_let_reddit_kill_3rd_party_apps/