GetFiles, sorted by Creation DateTime, filtered by include file mask and exclude file mask, including subfolders in C#

The “GetFiles” function, returns all files in the given folder, including subfolders, that match the “includeMask”. The result is sorted by the file CreationTime. The function excludes all files that match the “excludeMask”

 

To call the function:

List<FileInfo> files = FileSystemHelper.GetFiles(@"C:\Windows","*.*", "*.exe", SortOrder.Ascending, SearchOption.AllDirectories);

 

The function:
/// <summary>
///
Get files in a folder sorted by creation datetime and filtered by include mask and exclude mask
/// </summary>
/// <param name=”folder”>
The folder containing the files</param>
/// <param name=”includeMask”>
Include files that meet the given mask</param>
/// <param name=”excludeMask”>
Exclude files that meet the given mask</param>
/// <param name=”sortOrder”>
Sort order, ascending or descending</param>
/// <param name=”searchOption”>
AllDirectories or TopDirectoryOnly</param>
/// <returns></returns>
public staticList<FileInfo> GetFiles(string folder, string includeMask, string excludeMask, SortOrder sortOrder, SearchOption searchOption)
{
    DirectoryInfo folderDirectoryInfo = newDirectoryInfo(folder);
    List<FileInfo> files = newList<FileInfo>(folderDirectoryInfo.GetFiles(includeMask, searchOption));
   
    stringexcludeMaskRegEx = FileSystemHelper.WildcardToRegex(excludeMask);

    // exclude files
  
files = (fromfile infiles
             where!Regex.IsMatch(file.Name, excludeMaskRegEx)
             selectfile).ToList<FileInfo>();

    if(files.Count > 0)
    {
        switch(sortOrder)
        {
            caseSortOrder.Ascending:
                // Sort files oldest first
              
files.Sort(
                    delegate(FileInfo firstFile, FileInfo secondFile)
                    {
                        returnfirstFile.CreationTime.CompareTo(secondFile.CreationTime);
                    }
                );
                break;
            caseSortOrder.Descending:
                // Sort files newest first
              
files.Sort(
                    delegate(FileInfo firstFile, FileInfo secondFile)
                    {
                        returnsecondFile.CreationTime.CompareTo(firstFile.CreationTime);
                    }
                );
                break;
        }

    }

    returnfiles;
}

 

/// <summary>
/// Convert a wildcard mask to regular expression
/// </summary>
/// <param name="pattern">wildcard string</param>
/// <returns>Regular expression string</returns>
public static string WildcardToRegex(string pattern)
{
return string.Format("^{0}$", Regex.Escape(pattern).Replace("\\*", ".*").Replace("\\?", "."));
}

public enum SortOrder
{
Ascending,
Descending
}

SSIS package configuration

We have chosen to use a windows environment variable to hold the path to the *.dtsConfig, which is used to configure the *.dtsx package.

– Close all open Microsoft Visual Studio 2005 instances (because changing windows environment variables will have nog effect)

– Add a Windows Environment Variable:

– start > Control Panel > System > Advanced system settings > Environment Variables > New…

– Variable name: SSISPackage_Configuration

– Variable value: C:\Temp\SSISPackage.dtsConfig

– Click on [OK]

– Click on [OK]

 

– Open the Microsoft Visual Studio 2005 solution containing the SSIS project.

– Open the *.dtsx

– Click in the top menu on SSIS > Package Configurations …

– Check the Enable package configurations

image

– Click on Add …

image

image

image

When you start debugging the SSIS package in Microsoft Visual Studio 2005, the configuration will be red from the C:\Temp\SSISPackage.dtsConfig.

In production you can change the path of the environment variable and the configuration will then be red from that location.

Remove ExpandedColunCount and ExpandedRowCount when using Microsoft Exel (*.xml) file as template

When you use a Microsoft Excel "*.xml" file as template for generating Microsoft Excel reports, remove the ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1"

from the <Table> tag under the <Worksheet> tag. If you don’t remove these counters a error will occur, because the row and or column count is higher then 1:

Problem During Load
Problems came up in the following areas during load:
Worksheet Setting
This file cannot be opened because of errors. Erros are listed in C:\Users……\Content.MSO\7BF935F6.log

 

<Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1">
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>

 

An other error that can occur is:

XML ERROR in Table
REASON:    Bad Value
FILE:    C:\Temp\MyTest.xml
GROUP:    Table
TAG:    Row
ATTRIB:    Index
VALUE:    128

 

This was caused by the line:

<ss:Row ss:Index="128" ss:AutoFitHeight="0" ss:Height="12.9375">

Cause and solution

The index was not correct, by removing the ss:Index="128" ss:AutoFitHeight="0" ss:Height="12.9375" from the Row tag, the file could be opened correctly again.