How to Read xml spread sheet

Question

Hi All, I have a 3rd Party tool that generates an xml spreadsheet (*.xls). I have another program that reads this spreasheet and processes it. The content of the generated xml spreadsheet is a table with 5 columns and my program runs select queries on them. I m facing an issue while opening a connection to the generated spreadsheet. It says "{"External table is not in the expected format."}". My Connectin string is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\""; Do i need to change anything to this string ? I tried using a dataset and calling dataSet.ReadXml(excelFileName); But the dataset doesnt contain my table. Any inputs on how to read an xml spreadsheet ?

Thanks for your time, CS

Answer 1

Looks like your connection string is set for older binary format Excel files. You want a different connection string for XML. If you are talking about the new Excel 2007 xml files, then you need this connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

If you're talking about the older xml format that came in Excel 2003, then I'm not sure there is a connection string. In which case, your best bet is to open in Excel and then save as an xls file, and use the connection string you were originally using.

By the way, ConnectionStrings.com is a great place to find any old connection string that you might need to access all different kinds of data.

Also note, as others have noted, that if it is an xml file, it shouldn't have an xls file extension, it should be either .xml or .xlsx.

Answer 2

I have the Same Problem please have u found the solution in excel 2003.

What is solution?

Any way to convert the xml spreadsheet to Microsoft oFFICE eXCEL?

many thanks.

Answer 3

               Excel.Workbook wb1;

            Excel.Application wb2 = new Excel.Application();

            wb2.DisplayAlerts = false;
                           wb1 = (Excel.Workbook)wb2.Workbooks._Open(filename);

            if (wb1.FileFormat == Excel.XlFileFormat.xlXMLSpreadsheet)
            {
                                    wb1.SaveAs(filename, Excel.XlFileFormat.xlExcel12, Type.Missing, Type.Missing,
                        false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                       Excel.XlSaveConflictResolution.xlOtherSessionChanges, false);
            }
            else
            {
                wb2.Workbooks.Close();

            }

You can convert the Excel SpreadSheet to 2007 Format and then use LinQ to query the sheets using any open source provider or OleDB.