Hi,
I'm having a project from which I need to load multiple Excel files from a folder (stored in .xlsx format and not CSV).
Would be nice to have a data provider which allowed for appending all those workbooks in a specific folder, obviously given file structure is identical.
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!
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();
}
}
}
