Ingesting data from Google Drive folder

[bao_phan]

Have anyone done this before ? I am just playing around, trying to ingest data (multiple csv flat files) into Microsoft Fabric. I tried to use HTTP protocol in Pipeline item, but it did not work and returned the error message below when I tried to preview the data, but somehow the test connection worked (really dunno why). For the url in source in copy action, I basically clicked on sharing file in Google Drive then chose the Copy link url option (I already set it as Anyone with the link can access it).

Bad data is found at line 1 in source 698114d7-e4bb-49a6-ac35-198eca16bbae. You can ignore bad data by setting BadDataFound to null. IReader state: ColumnCount: 7255 CurrentIndex: 0 HeaderRecord: IParser state: ByteCount: 0 CharCount: 94853 Row: 1 RawRow: 1 Count: 7255 RawRecord: Hidden because ExceptionMessagesContainRawData is false. Activity ID: 06a3df44-14b7-4575-9540-4109372d9552

Please let me know if you know any things that could help!!! Thank you.

[deborshi_nag · Accepted solution]

Hello @bao_phan 

The copy link gives you a URL that points to a web page, not the file that you're after. The HTTP activity finds it as a huge first row and throws that error. This is not an issue with connectivity, but the content of the URL being deciphered as a row, hence test connection succeeds!  

The workaround is to change the URL into a downloadable direct file URL, so Fabric can download. Change the URL as per this -

Original (share) link looks like:

https://drive.google.com/file/d/<FILE_ID>/view?usp=sharing

Convert to direct download:

https://drive.google.com/uc?export=download&id=<FILE_ID>

Use this in the HTTP connectors source. This should return the file content. This is explained here: 

How to Make a Direct Download Link for Google Drive Files

[bao_phan]

oh, so we need to change the url like the format you mentioned to allow Fabric to be able to download the files and read that file. Thank you!!! Your solution works.