Problem parsing an XLS file with C#

Question

Ok, let's see if I can make this make sense.

I have a program written that parses an Excel file and it works just fine. I use the following to get into the file:

string FileToConvert = Server.MapPath(".") + "\\App_Data\\CP-ARFJN-FLAG.XLS";
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileToConvert + ";Extended Properties=Excel 8.0;";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
//this next line assumes that the file is in default Excel format with Sheet1 as the first sheet name, adjust accordingly
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [CP-ARFJN-FLAG$]", connection);

and this works just fine. But when I try it on the actual file (it is supplied to me by another program) I get this error:

System.Data.OleDb.OleDbException: External table is not in the expected format. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at wetglobe.Page_Load(Object sender, EventArgs e)

BUT, this is where I think the problem lies. If I take that file, and save it with my local Excel, first I get this popup:

CP-ARFJN-FLAG.XLS may contain features that are not compatible with Text (Tab delimited). Do you want to keep the workbook in this format?

• To Keep this format, which leaves out any incompatible features, click Yes.
• To preserve the features, click No. Ten save a copy in the latest Excel format.
• To see what might be lost, click Help.

If I click No and then save it as the current Excel format, the program will then work fine.

So I am assuming this is saved in some crazy old Excel format?

I suppose my questions would be:

• How can I tell what Excel version saved this?
• How can I parse it in its current state?
• -or- Can I programatically save it as a newer version?

I hope that is clear... Thank you.

Answer 1

It sounds like the XLS file generated by your third-party app may not really be in Excel format - it might actually be a tab-delimited text file with an .xls extension.

Try opening it with a text editor and see.

If it is tab delimited, you can ditch the OleDB adapter and open/parse it as a standard text file.

Answer 2

If the format of the generated file is likely to change in future (perhaps when you upgrade the third-party app), you may prefer to use the Office Primary Interop Assemblies. These will load any version or format of file produced by Excel. The downside is that you'll need Office installed on the server.

Answer 3

i solved the problem. The Excel file should be generated by MS EXCEL 2003, not from MS EXCEL 2007 "save as 97-2003". so you have to download a file from any source. Then copy the data manually to the sheet. it worked with me.