0

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.

JDCAce
  • 97
  • 9
  • Possible duplicate of [HRESULT: 0x800A03EC on Worksheet.range](https://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range) – aybe Mar 09 '18 at 18:00
  • 0x3EC = 1004, google "Excel error 1004" for hits. It has many possible causes, but one certainly stands out here. You can trigger it by slamming Excel with too many commands from a worker thread. So it is one step forward and one step back. If it is localized then you could catch it, sleep for a bit and try again. Do look at the various OpenXML wrappers as an alternative. – Hans Passant Mar 09 '18 at 18:59
  • https://support.microsoft.com/en-us/help/210684/copying-worksheet-programmatically-causes-run-time-error-1004-in-excel – Hans Passant Mar 09 '18 at 18:59
  • @Aybe Unfortunately, the issue in the link you posted isn't the same as my issue. The spreadsheet is being created in Excel 2016, and this error happens with 20 rows and 6 columns of data. – JDCAce Mar 09 '18 at 19:21
  • @HansPassant I've been Googling that error for a little bit, and I haven't had any more luck solving my issue. I've discovered I'm able to open the .txt in Excel and **manually** without an issue. I receive an error only when I try to do it in my C# program. – JDCAce Mar 09 '18 at 19:24

1 Answers1

0

The problem is the FieldInfo parameter. According to the API:

When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of theXlColumnDataType constants specifying how the column is parsed.

I rewrote the "Set up the arrays of XlColumnDataTypes" section in the original code to the following, and now it works as intended.

// Set up the arrays of XlColumnDataTypes
int[,] trimmedDataTypes = new int[trimmedTranlog.TagsPresent.Count, 2];
for(int i = 1; i <= trimmedDataTypes.Length / 2; i++)
{
    trimmedDataTypes[i - 1, 0] = i;
    trimmedDataTypes[i - 1, 1] = (int)Excel.XlColumnDataType.xlTextFormat;
}
int[,] fullDataTypes = new int[fullTranlog.TagsPresent.Count, 2];
for(int i = 1; i <= fullDataTypes.Length / 2; i++)
{
    fullDataTypes[i - 1, 0] = i;
    fullDataTypes[i - 1, 1] = (int)Excel.XlColumnDataType.xlTextFormat;
}
JDCAce
  • 97
  • 9