I receive this exception every time I try to open a CSV file (whether a .csv or .txt). Because the exception is so generic, I haven't been able to figure out what is wrong.
My program is a Winform application that FTPs data from an IBM mainframe, parses it, and generates custom objects which are ideally viewed in a spreadsheet, for easy navigation and filtering. First I tried writing my data directly to a spreadsheet, but that was taking a really long time for large data sets. For example, for 100 of my custom objects, my program took about 20 seconds to write them to a spreadsheet. It's not unusual for my end-user to need to create spreadsheets for thousands to tens of thousands of custom objects. Since I don't want the user to sit idly for an hour while the spreadsheet is created, I looked into the cause of the slowness. I read on StackExchange the Interop.Excel instructions are very slow, so in order to limit the number of them, I'm trying a different solution: First I write the data to a CSV, then I use the Interop.Excel to open the CSV, maybe do some light formatting like coloring the header and freezing the top row, then saving as a .xls or .xlsx. But I'm having trouble at the very first step: opening the CSV!
Here is the code:
using Excel = Microsoft.Office.Interop.Excel;
...
private void BuildSpreadsheetFromCsvFiles(string spreadsheetPath, string csvPathTrimmedTranlog, string csvPathFullTranlog)
{
Excel.Application xlApp = new Excel.Application();
try
{
// Set up the arrays of XlColumnDataTypes
Excel.XlColumnDataType[] trimmedDataTypes = new Excel.XlColumnDataType[trimmedTranlog.TagsPresent.Count];
Excel.XlColumnDataType[] fullDataTypes = new Excel.XlColumnDataType[fullTranlog.TagsPresent.Count];
for(int i = 0; i < trimmedDataTypes.Length; i++)
{
trimmedDataTypes[i] = Excel.XlColumnDataType.xlTextFormat;
}
for(int i = 0; i < fullDataTypes.Length; i++)
{
fullDataTypes[i] = Excel.XlColumnDataType.xlTextFormat;
}
xlApp.Workbooks.OpenText(Filename: csvPathTrimmedTranlog, // THROWS EXCEPTION
Origin: Excel.XlPlatform.xlWindows,
DataType: Excel.XlTextParsingType.xlDelimited,
TextQualifier: Excel.XlTextQualifier.xlTextQualifierNone,
Semicolon: true,
FieldInfo: trimmedDataTypes);
Excel.Workbook xlWorkbookTrimmed = xlApp.Workbooks[1];
xlApp.Workbooks.OpenText(Filename: csvPathFullTranlog, // ALSO THROWS EXCEPTION
DataType: Excel.XlTextParsingType.xlDelimited,
Origin: Excel.XlPlatform.xlWindows,
TextQualifier: Excel.XlTextQualifier.xlTextQualifierNone,
Semicolon: true,
FieldInfo: fullDataTypes);
Excel.Workbook xlWorkbookFull = xlApp.Workbooks[1];
Excel.Worksheet xlWorksheetTrimmed = xlWorkbookTrimmed.Worksheets[1];
Excel.Worksheet xlWorksheetFull = xlWorkbookFull.Worksheets[1];
xlWorksheetTrimmed.Copy(Before: xlWorksheetFull);
xlApp.Visible = true;
}
catch(Exception e)
{
xlApp.Quit();
}
}
I tried opening the files with Open() instead of OpenText(), and that does technically work. However, for my purposes, I cannot use Open() since in doing so all the columns will be read as General format. My data contains long strings of numbers (20 digits or so) which need to be displayed as text, and Open() will display those numbers with scientific notation.
More information:
- When the workbook is created, it is created in Excel 2016.
- I trimmed the .txt file to contain 20 rows and 6 columns, and the problem persists.
- The .txt file can be opened in Excel 2016 manually, but not programatically within my Winform application.
UPDATE I've narrowed the issue to the last parameter in OpenText(), the FieldInfo: parameter. With that parameter omitted, the file opens successfully. Unfortunately, as I said earlier, the data must be formatted as Text, instead of the default General.