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.
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();
}
}
}
