Click here to Skip to main content
15,891,689 members
Please Sign up or sign in to vote.
1.44/5 (3 votes)
See more:
I have created a file watcher processor which transforms xls file to csv file once the file gets dropped in the folder.

While doing so i get the error The process cannot access the file '\\somepath\someotherpath\user\FileName_2018.xls' because it is being used by another process.


What I have tried:

private static string sourcePath = ConfigurationManager.AppSettings["Excel"];
       private static string destination = ConfigurationManager.AppSettings["CSV"];
       private static string xlsFileName = string.Empty;
       static void Main(string[] args)
       {
           FileSystemWatcher watcher = new FileSystemWatcher
           {
               Filter = "*.xls",
               Path = sourcePath,
               EnableRaisingEvents = true
           };
           watcher.Created += new FileSystemEventHandler(Watcher_Created);
           Console.WriteLine($"FileSystemWatcher ready and listening to changes in :\n\n{sourcePath}");
           Console.Read();
       }
       static void Watcher_Created(object sender, FileSystemEventArgs e)
       {
           xlsFileName = e.Name;

           var filePath = sourcePath + xlsFileName;
           var extension = Path.GetExtension(filePath);

           if (extension.ToLower() == ".xls")
           {
               Console.WriteLine($"\n{xlsFileName} file is saved and ready to be processed.");
               PerformETL(xlsFileName);
           }
       }

       private static void PerformETL(string xlsFile)
       {
           try
           {
               var isCSVFileCreated = false;
               var filePath = sourcePath + xlsFile;
               var extension = Path.GetExtension(filePath);
               var fileNameWithoutExtension = Path.GetFileNameWithoutExtension(xlsFile);

               if (extension.ToLower() == ".xls")
               {
                   IExcelDataReader excelReader;
                   DataSet dataset;

                   using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
                   {
                       excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                       dataset = excelReader.AsDataSet();
                   }

                   DataTable dtTemplate = dataset.Tables[0];

                   using (var output = File.CreateText($"{destination}{fileNameWithoutExtension}.csv"))
                   {
                       for (int i = 5; i < dtTemplate.Rows.Count; i++)
                       {
                           // 7 spaces
                           output.WriteLine($"{dtTemplate.Rows[i][3].ToString().Trim()},{dtTemplate.Rows[i][4].ToString().Trim().PadLeft(9, '0')}");
                       }
                   }

                   isCSVFileCreated = true;
               }
               else
               {
                   isCSVFileCreated = false;
               }

               if (isCSVFileCreated)
               {
                   Console.WriteLine("File is extracted and transformed to space delimited file format");
               }
               else
               {
                   Console.WriteLine("File is extracted and transformed to space delimited file format");
               }

               DeleteExcelFile(filePath);
           }
           catch (Exception ex)
           {
               Console.WriteLine($"error: {ex.Message}");
           }
       }

       private static void DeleteExcelFile(string filePath)
       {
           var fileName = Path.GetFileName(filePath);
           if (File.Exists(filePath))
           {
               File.Delete(filePath);
           }
           Console.WriteLine($"{fileName} is deleted.");
       }
Posted
Updated 30-Oct-18 6:22am

1 solution

A problem I see right off the bat with people using the FileSystemWatcher is that people assume that a "Created" event fires when the file is written.

WRONG! Just like the event says, the file is CREATED. That is no way means the file is done being WRITTEN by the other app. If you try to open the file while is Excel is still writing it, you'll get the exception you describe.

They way around this is to create a loop to retry opening the file after a short period of time, and keep retrying it until either the file finally opens, or you reach a set number of retries in your loop.
 
Share this answer
 
Comments
istudent 30-Oct-18 12:28pm    
could you please share the code how do I do it? thank you
Dave Kreskowiak 30-Oct-18 13:04pm    
It's a simple loop with a counter for the number of retries. What's so hard about that?
istudent 30-Oct-18 17:11pm    
thank you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900