c# help required in excluding folder

Question

I have a directories - \abc\xyz\details\2023-01, \abc\xyz\details\2023-02, \abc\xyz\details\2023-03....\abc\xyz\details\2023-12

Also, i have other folder \abc\xyz\details\Archive. I want to exclude this "Archive" folder and it subfolders while reading the excel files.

folderpath=\abc\xyz\details\2023-01

var directory= new DirectoryInfo(folderpath);
FileInfo files = directory.Getfiles("*.*",SearchOption.AllDirectories);

foreach (FileInfo file in files)
{
string direcotryname=file.Directory.Name;

filefullpath = folderpath + "\\" + directoryname + "\\" + file.name;
string filename="";
filename = file.name;

...
...
} 

Answer 1

You can make a filter function to exclude archive folders, for example:

using System;
using System.IO;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace SSIS_CSharpScriptTask
{
    public class ScriptMain
    {
        public void Main()
        {
            string folderPath = @"C:\abc\xyz\details\2023-01";
            string archivePath = @"C:\abc\xyz\details\Archive";

            // Loop through all Excel files in the folder
            foreach (string file in Directory.GetFiles(folderPath, "*.xlsx"))
            {
                // Get the parent directory of the current file
                string parentDirectory = Path.GetDirectoryName(file);

                // Check if the parent directory is part of the "Archive" folder or not
                if (!IsInArchiveFolder(parentDirectory, archivePath))
                {
                    // Process the Excel file here (e.g., read data using Interop.Excel)
                    ProcessExcelFile(file);
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        private bool IsInArchiveFolder(string directory, string archivePath)
        {
            // Check if the given directory or any of its ancestors is the "Archive" folder
            while (!string.IsNullOrEmpty(directory))
            {
                if (directory.Equals(archivePath, StringComparison.OrdinalIgnoreCase))
                    return true;

                directory = Path.GetDirectoryName(directory);
            }

            return false;
        }

        private void ProcessExcelFile(string filePath)
        {
            // Your Excel processing logic goes here
            // For example, using Interop.Excel to read data
            Application excelApp = new Application();
            Workbook workbook = excelApp.Workbooks.Open(filePath);
            Worksheet worksheet = workbook.Sheets[1] as Worksheet;
            // ... read data from the worksheet ...
            workbook.Close(false);
            excelApp.Quit();

            // Release COM objects to avoid memory leaks
            Marshal.ReleaseComObject(worksheet);
            Marshal.ReleaseComObject(workbook);
            Marshal.ReleaseComObject(excelApp);
        }
    }
}

Answer 2

Hi @Dinesh Kalva,

Please try the following solution in the SSIS Script Task.

void Main()
{
	const string folderpath = @"e:\Temp\SubDirectories";
	string filefullpath = string.Empty;

	DirectoryInfo directory = new DirectoryInfo(folderpath);
	IEnumerable<FileInfo> files = directory.GetFiles("*.*", SearchOption.AllDirectories)
		.Where(d => !(d.DirectoryName.Contains("Archive")));

	foreach (FileInfo file in files)
	{
		filefullpath = file.FullName;
		Console.WriteLine(filefullpath);
	}
}

Answer 3

You can make a filter function to exclude archive folders, for example:

using System;
using System.IO;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace SSIS_CSharpScriptTask
{
    public class ScriptMain
    {
        public void Main()
        {
            string folderPath = @"C:\abc\xyz\details\2023-01";
            string archivePath = @"C:\abc\xyz\details\Archive";

            // Loop through all Excel files in the folder
            foreach (string file in Directory.GetFiles(folderPath, "*.xlsx"))
            {
                // Get the parent directory of the current file
                string parentDirectory = Path.GetDirectoryName(file);

                // Check if the parent directory is part of the "Archive" folder or not
                if (!IsInArchiveFolder(parentDirectory, archivePath))
                {
                    // Process the Excel file here (e.g., read data using Interop.Excel)
                    ProcessExcelFile(file);
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        private bool IsInArchiveFolder(string directory, string archivePath)
        {
            // Check if the given directory or any of its ancestors is the "Archive" folder
            while (!string.IsNullOrEmpty(directory))
            {
                if (directory.Equals(archivePath, StringComparison.OrdinalIgnoreCase))
                    return true;

                directory = Path.GetDirectoryName(directory);
            }

            return false;
        }

        private void ProcessExcelFile(string filePath)
        {
            // Your Excel processing logic goes here
            // For example, using Interop.Excel to read data
            Application excelApp = new Application();
            Workbook workbook = excelApp.Workbooks.Open(filePath);
            Worksheet worksheet = workbook.Sheets[1] as Worksheet;
            // ... read data from the worksheet ...
            workbook.Close(false);
            excelApp.Quit();

            // Release COM objects to avoid memory leaks
            Marshal.ReleaseComObject(worksheet);
            Marshal.ReleaseComObject(workbook);
            Marshal.ReleaseComObject(excelApp);
        }
    }
}