Excel Data Provider - multiple files in folder

If you have office installed on your tx server you can write a c# or python script to open the XLSX files and save them to disk as flat file.

This c# script can be executed using a custom SSIS package which is scheduled as first step in your tx execution package.  

If you don't have visual studio you can also compile C# from the command line; if you prefer python you can write the same script (using MSO and win32com.client package).

The code appends the filename in the flatfiles as well if you need some filename parts such as years, months, customer codes. 
It takes into account all .xls files in the current directory.

Code : 

using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Text.RegularExpressions;
using Microsoft.Office.Interop.Excel;

namespace ExcelToTxt
{
    class Program
    {


        private string currentDirectory = Directory.GetCurrentDirectory();
        public void SaveExcelAsTxt()
        {

            var files = Directory.GetFiles(currentDirectory, "*.xls?");
            Regex pattern = new Regex("[\\/:*?\"<>|]");

            Console.WriteLine($"Found {files.Length} Excel files.");

            var app = new Excel.Application();

            foreach (string file in files){

                var wb = app.Workbooks.Open(file);
                var wbName = wb.Name.Replace(".xlsx", "").Replace(".xls", "");

                Console.WriteLine($"Opened Excel {wbName}");


                foreach(Excel.Worksheet ws in wb.Worksheets) {

                    var newFile = $"{wbName}_{pattern.Replace(ws.Name, "")}.txt";
                    Console.WriteLine($"\tSaving sheet {ws.Name} as {newFile}.");

                    ws.SaveAs($"{currentDirectory}\\{newFile}", Excel.XlFileFormat.xlTextWindows, null, null, null, null, null, null, null, null);
                }

                Console.WriteLine("");
                wb.Close();

            }




        }

        public void AppendFileNameToTxt()
        {
            var files = Directory.GetFiles(currentDirectory, "*.txt?");

            Console.WriteLine($"Found {files.Length} Excel files.");

            foreach (string file in files)
            {
                var fileModified = $"{file.Replace(".txt", "")}_modified.txt";

                try
                {
                    StreamReader sr = new StreamReader(file);
                    StreamWriter sw = new StreamWriter(fileModified);
                    var line = sr.ReadLine();
                    bool firstLine = true;
                    while (line != null)
                    {
                        //write the lie to console window
                        if (firstLine)
                        {
                            sw.WriteLine($"{line}\tfilename");
                        } else
                        {
                            sw.WriteLine($"{line}\t{file}");
                        }

                        line = sr.ReadLine();
                        firstLine = false;
                    }
                    //close the file
                    sr.Close();
                    sw.Close();


                    Console.WriteLine("Deleting old file.");
                    File.Delete(file);

                    Console.WriteLine("Renaming file.");
                    File.Move(fileModified, file);
                }
                catch (Exception e)
                {
                    Console.WriteLine("Exception: " + e.Message);
                }
                finally
                {
                    Console.WriteLine("finished modifying file.");
                }


            }
        }

        static void Main(string[] args)
        {
            Program p = new Program();
            p.SaveExcelAsTxt();
            p.AppendFileNameToTxt();
        }
    }
}

Hi Emil

Thanks for submitting your idea. Your continued feedback on our product is very important to us. And so is your engagement in our community.

I have passed your input to our R&D department for further evaluation. 

At the time of this posting, the current product roadmap is focused on a major evolution in foundational pieces of our software including splitting the development into ODX, MDW, Semantic Layer, and User Portal. With this splitting and evolution, we will continue to see incredible innovation in the product that may exceed your expectations or make the need for this feature obsolete.

Don't hesitate to let me know if you have any questions or would like to discuss further.

Thanks!