How to import database in google cloud big query?

Question

I'm trying to import excel database sheet in big query from my download folder or google drive but I unable to import. Please reply how to import database sheet from google drive, if any method available.

Answer 1

XLSX is not included in the supported data for batch loading in Bigquery. A workaround is to convert XLSX to CSV then load to Bigquery from your local data source. I both achieved it using Bigquery Python API.

See working code below:

import pandas as pd
from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
table_id = "<your-project>.<your-dataset>.<your-table>"
file_path = "<your-path>/20220621.xlsx"
coverted_path = "<your-path>/20220621.csv"

# Converting XLSX to CSV
data_xls = pd.read_excel(file_path, index_col=None)
data_xls.to_csv(coverted_path, encoding='utf-8', index=False)

job_config = bigquery.LoadJobConfig(
    source_format=bigquery.SourceFormat.CSV, skip_leading_rows=1, autodetect=True,
)

with open(coverted_path, "rb") as source_file:
    job = client.load_table_from_file(source_file, table_id, job_config=job_config)

job.result()  # Waits for the job to complete.

table = client.get_table(table_id)  # Make an API request.
print(
    "Loaded {} rows and {} columns to {}".format(
        table.num_rows, len(table.schema), table_id
    )
)

.XLSX file:

Converted to CSV:

Loaded to Bigquery: