none
Use c# to change xls to csv in Data Lake Storage

    Question

  • We have lots of xls with multiple worksheets. Having to change them manually to csvs is time consuming

    Is there any way to say, create some c# code to create csvs from the xls and then possibly add the function to a data Lake Analytics account.

    Then run some u-sql to create the csvs?

    Its a long shot but we are looking at some way of automating this using Azure

    Debbie


    Debbie

    Tuesday, January 8, 2019 10:36 AM

All replies

  • Hi Debbie,

    There's a library to do so, please check it out here :

    https://github.com/ExcelDataReader/ExcelDataReader#how-to-use

    Also, I found an article explaining how exactly you can convert xls files to csvs :

    https://www.codeproject.com/Articles/246772/%2FArticles%2F246772%2FConvert-xlsx-xls-to-csv

    Hope this helps.


    MSDN

    Tuesday, January 8, 2019 12:38 PM
    Moderator
  • Thanks for that,

    This info is way over my head unfortunately. the 2nd article really lost me. Is Excel Data Reader definitely the way to go? And can you automate it in Azure?

    Maybe there are some more how to guides with more information for people with less knowledge? 


    Debbie


    Tuesday, January 8, 2019 4:35 PM
  • Hi Debbie,

    Using Excel Data Reader is not the only way to convert xls to csv but it's one of the most popular and easy ways to do so. There are a few more ways as mentioned in the following threads :

    https://stackoverflow.com/questions/2536181/is-there-any-simple-way-to-convert-xls-file-to-csv-file-excel

    https://stackoverflow.com/questions/42926710/converting-xlsx-file-using-to-a-csv-file

    https://stackoverflow.com/questions/31316551/convert-excel-file-xls-or-xlsx-to-csv-file

    Also, you can run your C sharp code as an Azure function. Please refer the following doc for more information on the same :

    https://docs.microsoft.com/en-us/azure/azure-functions/functions-dotnet-class-library


    MSDN

    Wednesday, January 9, 2019 10:47 AM
    Moderator
  • Hi Debbie,

    There is an easy and straightforward way to convert xls to csv using c# code:

    using Spire.Xls;
    namespace xls2csv
    {
        class Program
        {
            static void Main(string[] args)
            {
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"..\ExceltoCSV.xls");
                Worksheet sheet = workbook.Worksheets[0];
                sheet.SaveToFile("sample.csv", ",", Encoding.UTF8);
            }
        }
    }
    Code copy from Easiest Solution to Convert Excel to CSV in C#, VB.NET. Hope it can help you.

    Thursday, January 10, 2019 8:03 AM