How to read xml sheet
I have a third party tool that generates an xml table (* .xls). I have another program that reads this table and processes it. The content of the generated xml table is a table with 5 columns and my program runs selections on them. I am facing an issue when opening a connection to a generated spreadsheet. It says: "{" The 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 something on this line? I tried using dataset and called dataSet.ReadXml (excelFileName); But the dataset doesn't contain my table. Any inputs on how to read the xml table?
Thanks for your time, CS
a source to share
It looks like your connection string is configured for Excel files in binary format. You need a different connection string for XML. If you are talking about new Excel 2007 xml files you need this line:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
If you are talking about an earlier XML format that appeared in Excel 2003, I'm not sure if there is a connection string. In this case, your best bet is to open Excel and then save as xls file and use the connection string you originally used.
By the way, ConnectionStrings.com is a great place to look for any old connection string you might need to access all the different types of data.
Also note, as others have noted, that if it is an xml file, it should not have the xls file extension, it should be either .xml or .xlsx.
a source to share
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 format to 2007 and then use LinQ to query the sheets using any open source provider or OleDB.
a source to share