0

I am not able to read the CSV file immediately after converting an Excel sheet to CSV. I am getting error: "The process cannot access the file .. because it is being used by another process."

My code is:

            csvFilePath = AppSettings.exportFileFolderPath + @"\Temp002.csv";
            ws.SaveAs(csvFilePath, Excel.XlFileFormat.xlCSV, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
            allLines = File.ReadAllLines(csvFilePath);

At this point, if I put a breakpoint and try to rename the file in Windows Explorer, I get the same error in Explorer too. But immediately after the debugger throws an exception and the program ends, I am able to rename the file in Explorer.

What is the issue and the solution?

EDIT 1: This seems to be working:

            string csvFilePath1 = AppSettings.exportFileFolderPath + @"\Temp001.001";
            ws.SaveAs(csvFilePath1, Excel.XlFileFormat.xlCSV, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);

            ws = wSheets.get_Item("Dummy") as Excel.Worksheet;
            string csvFilePath2 = AppSettings.exportFileFolderPath + @"\Temp001.002";
            ws.SaveAs(csvFilePath2, Excel.XlFileFormat.xlCSV, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);

            string[] allLines = File.ReadAllLines(csvFilePath1);

But just wondering if there is a still better way?

AllSolutions
  • 1,176
  • 5
  • 19
  • 40
  • 1
    The file is not getting closed before you try to rename it. – Craig Tullis Sep 25 '16 at 22:23
  • I have several sheets in the WorkBook, and I need to convert many of them to CSV (a temp file) and immediately read the contents and delete the temp CSV file. I can not close the workbook immediately after creating 1 CSV, as I need to process the contents of this CSV and then need to convert other sheets too in the same workbook. – AllSolutions Sep 25 '16 at 22:29
  • Is there a way I can close only the newly created CSV, and not the original workbook? – AllSolutions Sep 25 '16 at 22:29
  • I'm on my phone and short on time, but I'd recommend reading up on that ReadAllLines function to make sure it is closing the file immediately. Or check out some other stackoverflow posts for more options: http://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba – Craig Tullis Sep 25 '16 at 22:35
  • I guess the problem is not in ReadAllLines, but rather in ws.SaveAs, as it continues to keep the csv file open. If I close workbook, then ReadAllLines works fine. But closing wb each time after converting a sheet to csv is so inefficient – AllSolutions Sep 25 '16 at 22:38
  • I think that's what I meant. :) – Craig Tullis Sep 25 '16 at 22:44
  • By keeping the Excel app visible, I got some more detail: After ws.SaveAs, the existing sheet seems to be replaced in the same Excel workbook with the new CSV sheet ! – AllSolutions Sep 25 '16 at 22:47
  • That makes sense. So it's basically just running the same code as the File/Save As menu. – Craig Tullis Sep 25 '16 at 22:55
  • It does not make sense to me. Reason: By just closing the original workbook, the single sheet actually gets saved as a separate csv file. The only problem is that if the workbook is kept open, the sheet converted to csv file still shows as part of the original workbook! Moreover, then what is the whole point of having a SaveAs method in the Worksheet level, if it does not work without closing the WorkBook ! – AllSolutions Sep 25 '16 at 22:57
  • Whats more, the Worksheet SaveAs method replaces the original Sheet in the workbook. Example, if there is a Sheet called "MySheet1" and I call SaveAs method with file name "CSV1", then MySheet1 gets "replaced" in the workbook with CSV1, and you can no longer see MySheet1, but you can see all the other sheets just as they were. But in Windows Explorer, you will find that CSV1 is created as a seperate file! – AllSolutions Sep 25 '16 at 23:03
  • Excel's Save As converts the sheet, and makes it the active sheet. You aren't editing your xlsx file any more after Save As, you're editing the CSV. – Craig Tullis Sep 25 '16 at 23:05
  • And there does not seem to be any way to tell Excel to just save the sheet and not make it active or keep it open ! The only workaround as suggested in some articles is to just copy that 1 sheet to a new workbook, and then call SaveAs on the new WorkBook, and then close the new WorkBook in order to read the file ! – AllSolutions Sep 25 '16 at 23:08
  • Have a look at this Q&A, basically looks like you want to copy the current sheet to a new temporary sheet, save THAT sheet as CSV, then close it: http://stackoverflow.com/q/37037934/618649 – Craig Tullis Sep 25 '16 at 23:11
  • That answer is the same what I posted above, i.e. create a new WorkBook for each sheet that I want to save, and save that new WorkBook – AllSolutions Sep 25 '16 at 23:16
  • I found 1 more workaround: immediately after calling SaveAs on each worksheet, I can call SaveAs on a dummy worksheet, which will make Excel release the 1st worksheet and I can read it. – AllSolutions Sep 25 '16 at 23:19
  • Is there a way to read a csv file even if it is open in Excel? – AllSolutions Sep 25 '16 at 23:25
  • This is getting long. ;-) Excel has made an API call to the operating system (Windows) to lock the file. So when you try to open the file from your script, it's the operating system that won't let you, not Excel. When Excel closes the file (releases the lock), then the OS will let you open it. You might experiment with opening the file in multi-user mode (review/track changes), but I'm guessing. I think those scripts in the other Stack Overflow answer look like a decent solution. – Craig Tullis Sep 25 '16 at 23:28
  • At the time when I get error in reading file from my C# application, if I open the file in TextPad or Notepad, I am able to read the file. Can you suggest some method other than File.ReadAllLines which can read the file just like how TextPad or NotePad does? – AllSolutions Sep 26 '16 at 10:59
  • Well, I am now able to read the csv by writing my own ReadAllLines method. – AllSolutions Sep 26 '16 at 11:56
  • Just saw this, glad you found a solution, the built-in ReafAllLines must be asking for an exclusive lock, which of course it can't get. – Craig Tullis Sep 26 '16 at 14:49

0 Answers0