I have been trying to find a solution to my problem for many hours on the Azure Data Factory and Azure SQL Server.
I can't believe how it is complicated to do that 'simple task'. Well its probably myself which i just start on azure.
I have a Google Drive Sheet which I would like to be uploaded in a data table in Microsoft Azure SQL Server. This file will be updated every hour, so the procedure will be run every hour.
So far what I tried.
1: I've created an excel file which connect to https://www.excell-en.com/link-google-sheets-to-excel-tutorial to my Google sheet. I was hoping I will be able to connect azure to excel, convert to CSV and send it to Microsoft SQL Server. Unsuccessful
I did in my Google Sheets an automatic CSV export which I would be able to import external data from my drive cloud https://docs.microsoft.com/en-us/azure/storage/common/storage-import-export-data-to-blobs?toc=/azure/storage/blobs/toc.json. Unsuccessful
I tried to run python script inside azure. I had difficulties to import the libraries and was Unsuccessful.
import pandas as pd import CSV datagooglesheets = "https://docs.google.com/spreadsheets/d/11wCTgaHMM/export?format=csv&gid=1659475835" datadictionnaryfull = "https://docs.google.com/spreadsheets/d/1Mv8hE/export?format=csv&gid870497447" datagooglesheets=pd.read_csv(datagooglesheets, sep=",", error_bad_lines=False, index_col=False, dtype="unicode",low_memory=False, na_values=["NA"]) datadictionnaryfull=pd.read_csv(datadictionnaryfull, sep=",", error_bad_lines=False, index_col=False, dtype="unicode",low_memory=False, na_values=["NA"]) datadictionnaryfull.rename(columns = {"drug_name":"all_drug_name"}, inplace = True)
Dint try yet, can I load an external google CSV file this way? https://abouconde.com/2019/03/03/attach-or-detach-an-external-storage-account-microsoft-azure-storage-explorer/
Any free solutions will be really appreciated!
The workflow is
- Load in Azure Excel sheet / or google sheet / Or Csv from Google Drive / or CSV on Microsoft Drive
- Import the data to Azure (Schedule every hour)
- Return in Azure SQL Server Data table (new table updated every hour)