c# help required in excluding folder

Dinesh Kalva 100 Reputation points
2023-08-02T18:31:54.84+00:00

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;

...
...
} 


SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,409 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
9,947 questions
{count} votes

Accepted answer
  1. Vahid Ghafarpour 14,695 Reputation points
    2023-08-02T18:40:40.48+00:00

    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);
            }
        }
    }
    
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,561 Reputation points
    2023-08-02T19:07:24.3233333+00:00

    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);
    	}
    }
    
    
    
    1 person found this answer helpful.
    0 comments No comments

  2. Vahid Ghafarpour 14,695 Reputation points
    2023-08-02T18:40:25.2866667+00:00

    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);
            }
        }
    }
    
    
    0 comments No comments