Need suggestion on getting column data from excel without using ADO or Excel Interops

[C# Programming]

I am working with csv files.

I started out using ADO to grab column and data, however there is a flaw in doing this.

I have data that is F3, F5, F{something} and Excel automatically tries to put the dataType as a Float.

I moved to opening the file using StreamReader and im able to successfully import an excel file into my application - but now I don't have the columns DataType.

Another bad thing about ADO I can't have is the 255 column limit, we will have datasets that contain over 300 columns if not more.

So what I ended up doing (just an fyi), is first collecting the List of columns by using StreamReader reading the first line (headers) and placing them inside a List

Now I iterate through the List and use ADO to pull back that column name but empty -
(Select {columnName} from {XlsFile} Where 1=2)

I get back the column data and add it into a Dictionary {'column', 'datatype'}

I now have my column Data that I need.

Now I have to go back through the csv file again using StreamReader

This time I am creating a DataTable with the columns and data inside it

Before I add the column I set the datatype to whatever the dataType is in the Dictionary for that column Name.


Ok, this works good until I get data inside a column like 'F3'.

I know that Excel reads like 8-10 rows and tries to 'guess' what the dataType would be.
(any work arounds for this?)

Excel has already set this datatype to a 'float' or 'decimal' when it should of been 'string'

Ok, now my question(s)

1. Does this make sense?
2. Am I going to have to iterate through the data and if column's dataType is anything but String try to convert the data to the columns dataType and see if it's a match?

3. Any idea's on how I can get the correct dataTypes and columns without Interop or ADO?


Hope this makes sense, if not express it and I'll clarify.

Steve Welborn
Software Engineer
Eli Lilly
Indianapolis, Indiana.