CODEWITHSUNDEEP
c#asp.netazureazure-web-app-serviceazure-blob-storage
Kavitha Guruswamy
Kavitha Guruswamy

Reputation: 59

Need to read the excel file from azure storage blob container through oledb connection

I have hosted my web site into azure...I have uploaded the Excel file into azure storage blob container through an intermediate server since I am not able to directly access from website to azure.. so what I need is, I need to read the excel file from the azure storage blob container (or) intermediate server local path using "oledb connection" and store it into the data table for validating the excel..so can anybody help me to proceed further.

Upvotes: 2

Views: 4173

Answers (2)

David Makogon
David Makogon

Reputation: 71112

You never edited your question to show code, and the code you posted erroneously as an answer has been deleted, so I'm going on a guess here, but... I'm fairly certain the issue is that you're trying to open a blob as a file, via your OleDbConnection.

Azure Storage blobs are not open-able as files. If you're going to use a blob's contents like a file, you first need to download it and save it as a file (or use a memory stream, but in your case, you need a file). Then you can open the file as you normally would.

Since you're running your code in an Azure-based Web App, you'll ideally want your web app in the same region as the storage account. You'd be downloading to the web app's local disk (web apps give you local storage to work with).

Once done manipulating the file, you'll need to upload it back to the blob.

Upvotes: 1

Kavitha Guruswamy
Kavitha Guruswamy

Reputation: 59

public string[] GetExcelSheets(string filename)
        {
            String[] excelSheets = null;
            StorageCredentials creds = new StorageCredentials("<accountname>", "<key>");
            CloudStorageAccount account = new CloudStorageAccount(creds, useHttps: true);
            CloudBlobClient blobClient = account.CreateCloudBlobClient();
            CloudBlobContainer blobContainer = blobClient.GetContainerReference("documents");
            blobContainer.CreateIfNotExists();
           blobContainer.SetPermissions(new BlobContainerPermissions
{  PublicAccess = BlobContainerPublicAccessType.Blob
            });
            CloudBlockBlob blob1 = blobContainer.GetBlockBlobReference(filename);
            try
            {
                using (var stream = blob1.OpenRead())
                {
                    OleDbConnection connection = new OleDbConnection();
                    var localPath = @"https://xxyyyyyyyyyyy.blob.core.windows.net/";
                    var fileName = @"C:\xxx" + @"\" + "9370.XLS";
                    var fullPathToFile = System.IO.Path.Combine(localPath, fileName);
                    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties=Excel 8.0";
                   OleDbConnection oledbConn = new OleDbConnection(connString);
                    DataTable dt = new DataTable();
                    if (oledbConn.State == ConnectionState.Closed) oledbConn.Open();
                    // Get the data table containg the schema guid.
                    dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    oledbConn.Close();
                    if (dt == null) return null;
                    excelSheets = new String[dt.Rows.Count];
                    int i = 0;
                    // Add the sheet name to the string array.
                    foreach (DataRow row in dt.Rows)
                    {
                        excel Sheets[i] = row["TABLE_NAME"].ToString();
                        i++;
                    }
                }
            }
            catch (Exception ex)
            {
                HTTP Context.Current.Res`enter code here`ponse.Write(ex.Message);
            }
            return excel Sheets; }
this is the code i used to read the excel file but oledb data-source path is incorrect...can u suggest what path i should use to read the excel file in azure blob container

Upvotes: 0

Related Questions