Load external data in Azure Blob Storage (Or Other Services )

Question

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)

400_bad_request 400_bad_request · Accepted Answer · 2020-04-14T11:32:36

So for the workflow you mentioned you should try to use Azure Logic Apps, it provides a minimal coding solution as well as less hassle approach. It was cheaper for me too when I switched to it. https://azure.microsoft.com/en-us/services/logic-apps/#overview

Load external data in Azure Blob Storage (Or Other Services )

1 Answers