Read/Upload file (csv/excel) - Azure Storage D365

           Read/Upload file (csv/excel) from 

                Azure blob container - D365



Read CSV file from Azure blob container.



1) Create a method that downloads the file in to memory stream. 

We can create connection with Azure using different approaches(Go to link below)

public System.IO.Stream downloadFileFromAzureBlobContainer(System.IO.MemoryStream fileStream, str _fileName)
{
    try
    {
        fileStream = new System.IO.MemoryStream();
            
        blobClient = storageAccount.CreateCloudBlobClient();
        blobContainer = blobClient.GetContainerReference("YourcontainerName");

        blobContainer.CreateIfNotExistsAsync();

        CloudBlockBlob blockblob = blobContainer.GetBlockBlobReference(_fileName);

        if (blockblob && blockblob.Exists(null, null))
        {
           blockblob.DownloadToStream(fileStream, null, null, null);
           if (fileStream.Length > 0)
           {
               return fileStream;
           }
        }
        return null;
    }
    catch (Exception::CLRError)
    {
        CLRObject   exception       = CLRInterop::getLastException();
        CLRObject   innerException  = exception.get_InnerException();
        return null;
    }
}


2) Read the downloaded stream of file
 
public void readCSVFileFromAzure()
{
    System.IO.MemoryStream        streamFile, streamFileAzure;
    CommaStreamIo                       streamIO;

    streamFileAzure = this.downloadFileFromAzureBlobContainer(streamFile, "YourFileName");
        isHeaderRow = true;
       
    if (streamFileAzure != null && streamFileAzure.Length > 0)
    {
         streamIO = commaStreamIo::constructForRead(streamFileAzure);
         streamIO.inFieldDelimiter(',');

         if(streamIO)
         {
              while(streamIO.status() == IO_Status::Ok)
              {
                  conFile = streamIO.read();
                  if (conLen(conFile) > 0 && !isHeaderRow)
                 {
                      this.insertRecord(conFile);
                  }                   
                  isHeaderRow = false;
              }
            
          }
     }
}

Upload CSV file to Azure blob container.


1) Create a method that uploads the file memory stream to Azure. 

We can create connection with Azure using different approaches(Go to link below)

public boolean uploadFileToAzureBlobContainer(System.IO.Stream _fileContentInStream, str _fileName)
{
    try
    {
        blobContainer.CreateIfNotExistsAsync();
        CloudBlockBlob blockblob = blobContainer.GetBlockBlobReference(_fileName);
            
        if (blockblob && !blockblob.Exists(null, null))
        {
           _fileContentInStream.Position = 0; 
           blockblob.UploadFromStreamAsync(_fileContentInStream,                                                                _fileContentInStream.Length);
           return true;
        }
        return false;
    }
    catch
    {
        return false;
    }
}


2) Create memory stream of CSV file to upload.
 
public void readCSVFileFromAzure()
{      
    CommaStreamIo                       streamIO;
     
    System.IO.MemoryStream        streamFileAzure;
    boolean                                     isFileUploaded, isLineAdded;

    streamIO = commaStreamIo::constructForWrite();
    container header = ["Name", "Id", "Gender"];

    streamIO.writeExp(header);

    container line =  ["Saeed", "1", "Male"];
    streamIO.writeExp(line);
    isLineAdded = true;       

    if (isLineAdded)
    {
        streamFileAzure = streamIO.getStream();
        isFileUploaded = this.uploadFileToAzureBlobContainer(streamFileAzure, "FileName.csv");
            
        if (isFileUploaded)
        {
           info("file uploaded");
        }

    }
}


Read Excel file from Azure blob container.


1) Create a method that downloads the file memory stream from Azure. 

We can create connection with Azure using different approaches(Go to link below)

public System.IO.Stream downloadFileFromAzureBlobContainer(System.IO.MemoryStream fileStream, str _fileName)
{
    try
    {
        fileStream = new System.IO.MemoryStream();
            
        blobClient = storageAccount.CreateCloudBlobClient();
        blobContainer = blobClient.GetContainerReference("YourcontainerName");

        blobContainer.CreateIfNotExistsAsync();

        CloudBlockBlob blockblob= blobContainer.GetBlockBlobReference(_fileName);

        if (blockblob && blockblob.Exists(null, null))
        {
            blockblob.DownloadToStream(fileStream, null, null, null);
            if (fileStream.Length > 0)
           {
               return fileStream;
           }
       }
       return null;
    }
    catch (Exception::CLRError)
    {
        CLRObject   exception       = CLRInterop::getLastException();
        CLRObject   innerException  = exception.get_InnerException();
        return null;
    }
}


2) Read memory stream of Excel file.

public void importExcelFile(System.IO.Stream _streamFile)
{
   OfficeOpenXml.ExcelWorksheet        worksheet;
   OfficeOpenXml.ExcelRange            cells;
   System.IO.Stream                    stream, streamFile;
   int                                 totalRows, totalCells, rows, cellCount;   

   streamFile = this.downloadFileFromAzureBlobContainer(stream, "fileName");

   OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(streamfile);
       
   if(package)
   {
      worksheet       = package.get_Workbook().get_Worksheets().get_Item(1);
      cells           = worksheet.Cells;
      totalRows       = worksheet.Dimension.End.Row;
      totalCells      = worksheet.Dimension.End.Column;

      for (rows= 2; rows<= totalRows; rows++)
      {
          for (cellCount=1; cellCount<=totalCells; cellCount++)
          {
               //your logic goes here
              //cells.get_Item(rows, cellCounter).value;
          }
      }
   }
}


Comments

Post a Comment

Popular posts from this blog

Cross Company queries - X++

Image
When we create a query in X++, it fetches record only for the company that we are logged in. If we want to get records for multiple companies, we have the keyword in X++. i.e. crossCompany. Select query: For example, Let’s say we write the following query. SalesTable            salesTable; while select * from salesTable {     i nfo(strfmt(“%1”,salesTable.SalesId)); } It will return only the records from the company we are logged into. Now we want to get records from all companies. We will need to write following query. while select crossCompany salesTable {     info(strfmt(“%1”,salesTable.SalesId)); } This crossCompany keyword will return all the companies record. If we want to get specific companies records, we need to use container . For example, container specificComp = [‘USMF’,’USMR’]; while select crossCompany : specificComp salesTable {     info...
Read more

Create multi select company lookup in report dialogue using X++

Image
Create multi select company lookup in dialogue using X++ Let’s say we have a requirement to show multi select lookup on report dialogue box. Steps: 1) Create CONTRACT class with a company parameter in it. 2)   Create  UI BUILDER  Class to make multi select company lookup 3)   Get selected companies in Report DP class Let’s put some lights on the above steps. Assuming that we know the basic concepts of SysOperationFramework. Create CONTRACT class with a company parameter in it. Adding  SysOperationContractProcessingAttribute  in contract class will use controls modified in UI Builder class. Use LIST as a data type as we can have multiple selected companies. Code: [ DataContractAttribute,    SysOperationContractProcessingAttribute( classStr (TestReportUIBuilder)) ] class TestReporContract {      List     legalEntity; } Now add new method for company parame...
Read more

Positive pay file in D365

Image
Positive Pay File -  D365  Convert XSLT file to CSV format. Positive pay files are created using data entities. The input file format must be only of XML type. Therefore, we need to create an a file with the extension XSLT ( Extensible Style sheet Language Transformation ). Create an XSLT file. Note: We can use Notepad/Notepad++ and save it with extension .XSLT  Now, we need to do setup configurations to run positive pay file on bank. Step 1: Add positive pay formats. Go to Cash and bank management > Setup > Positive pay formats   Create/edit positive pay file format. Then click on Upload file used for transformation to upload XSLT file created above. Step 2: Assign file format to the bank. Go to  Cash and bank management > Bank accounts > Bank accounts. Select any bank. Go to  Manage Payments Tab > General > positive pay format. Select payment format. Now, before jumping to step ...
Read more