More about reading Excel files in C#

In a previous posting I talked about how to read Excel files in .NET. I talked about the IMX parameter and how important it is to set it to “1” in order to interpret all data as strings. If the parameter is something else .NET will try to make an assumption based on the first rows in the file, if these rows are integers then is the column treated as an integer, and if a row further down contains non-numeric characters then will that data be null.

After discovering this parameters I implemented a correct connection string (one of several information sources is this one) and it worked. But as I discovered today it does not work all the time, still not all columns are treated as strings. The solution (this time…) is to treat the first row in the file as a header row and then map the headers with the imported data if needed. When using this solution must the HDR-parameter in the connection string be set to No. Assuming the file has a header row with text, then will .NET treat all following data as strings. If you read from the file based on column names (using the HDR-parameter) then you must start the reading with a map-conversion from the .NET Excel column names (”F1”, ”F2”, ”F3”, etc.) and the data in the first row (i.e. the column names).