Use Azure Functions to automate saving Excel to CSV

Question

Trying to use Azure Functions to automate saving Excel file out to blob as a CSV so I can consume in either Logic Apps or Azure Data Factory. Am looking to use ExcelDataReader C# library and I can get the NuGet package to download to my Function but am stuck after that.

Currently it appears I am stuck because the File.Open command looks for a file at a local path and I get the following error:

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))

The filename, directory name, or volume label syntax is incorrect : 'D:\Program Files (x86)\SiteExtensions\Functions\2.0.12507\64bit....'

Any suggestions you all have about saving out XLSX via Azure Functions?

user_2156200 · Answer

You don't have to open a stream manually, Azure Functions Binding can do that for you for both reading and writing.

ex:

[FunctionName("ConvertExcelToCSV")]
    public static async Task RunAsync(
        [BlobTrigger("excel-files/{blobName}")] Stream excelFileInput,
        [Blob("csv-files/{blobName}", FileAccess.Write)] Stream csvFileOutput,
        CancellationToken token,
        ILogger log)
    {
        log.LogInformation($"Do your processing on the excelFileInput file here.");
        //Do your processing on another steam. Maybe, MemoryStream
        await memoryStream.CopyToAsync(csvFileOutput, 4096, token);
    }

user_10383250 · Answer

If you use Environment.CurrentDirectory to get the executing directory, it will response the directory you displaying. And this directory in the azure kudu, it's not allowed to create files so your excel file is not there. You could use context.FunctionDirectory to get the current function directory (e.g. when running on Azure)

ex:

public static void Run([TimerTrigger("0 */1 * * * *")]TimerInfo myTimer, ILogger log, ExecutionContext context)
        {
            var excelFilePath = context.FunctionDirectory + @"\Book.xlsx";
            var destinationCsvFilePath = context.FunctionDirectory + @"	est.csv";

            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

            var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

            IExcelDataReader reader = null;

            reader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = false
                }
            });

            var csvContent = string.Empty;
            int row_no = 0;
            while (row_no < ds.Tables[0].Rows.Count)
            {
                var arr = new List();
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    arr.Add(ds.Tables[0].Rows[row_no][i].ToString());
                }
                row_no++;
                csvContent += string.Join(",", arr) + "
";
            }
            StreamWriter csv = new StreamWriter(destinationCsvFilePath, false);
            csv.Write(csvContent);
            csv.Close();

            log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
        }

And just update your excel here:

Use Azure Functions to automate saving Excel to CSV

2 Answers